Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Nov 2009
    Posts
    12

    Unanswered: Subselect Vs Join issues

    Hi guys, I have a sql query something like below:
    SELECT
    freq.jobid,
    rpd.perDate,
    FROM dbo.tmpFreq freq
    INNER JOIN dbo.refPeriodDays rpd
    ON rpd.perDate >= freq.startDate
    left join (select jobidsuped, max(datedue) as resynchdate
    from dbo.tmpResynch
    where tmpResynch.jobidsuped = freq.jobid
    and tmpResynch.datedue < rpd.perDate) resynch
    ON resynch.jobidsuped = freq.jobid
    WHERE freq.FrequencyUom IN ('DAY', 'WEEK')
    and (DATEDIFF(dd, rpd.perDate, case when resynch.resynchdate is null then freq.startDate else resynch.resynchdate end) % CONVERT(integer, ((case when freq.Interval <= 0 then 1 else freq.Interval end) * tf.factor))) = 0
    and (isnull(resynch.resynchdate,'1 jan 1900') <> rpd.perDate)

    when I run this script the error messages are:
    "The column prefix 'freq' does not match with a table name or alias name used in the query"
    "The column prefix 'rpd' does not match with a table name or alias name used in the query"
    And i figured out that outer table freq and rpd referenced inside the subselect are not allowed!!!
    But I do need to implement such functionality, so is there any way to work around that?? Any comments are welcomed!!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You could use CROSS APPLY instead of JOIN.
    If the relationship between these two tables is 1:1 then you could use a subselect in your SELECT clause too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2009
    Posts
    12

    Thanks so much for your help!!

    Thanks for replying.
    The relationship of table freq and rpd is 1:many
    Can you explain that a bit further?
    You mean move
    select jobidsuped, max(datedue) as resynchdate
    from dbo.tmpResynch
    where tmpResynch.jobidsuped = freq.jobid
    and tmpResynch.datedue < rpd.perDate
    into the top select section? But I need to reference the result of this subset
    in the following where clause.
    Thanks again.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Option 1 then. Try using OUTER APPLY (I forgot this was a left join - OUTER APPLY is to OUTER JOINS what CROSS APPLY is to INNER JOINS). It should take a few mins to look it up, a few seconds to alter the query and you should know almost immediately if it is working for you.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2009
    Posts
    12
    I changed to the following but still reported error, it seems OUTER APPLY NOT supported by Sql Sever 2005 env.

    SELECT
    freq.jobid,
    rpd.perDate,
    FROM dbo.tmpFreq freq
    INNER JOIN dbo.refPeriodDays rpd
    ON rpd.perDate >= freq.startDate
    OUTER APPLY (select jobidsuped, max(datedue) as resynchdate
    from dbo.tmpResynch
    where tmpResynch.jobidsuped = freq.jobid
    and tmpResynch.datedue < rpd.perDate) resynch
    WHERE freq.FrequencyUom IN ('DAY', 'WEEK')
    and (DATEDIFF(dd, rpd.perDate, case when resynch.resynchdate is null then freq.startDate else resynch.resynchdate end) &#37; CONVERT(integer, ((case when freq.Interval <= 0 then 1 else freq.Interval end) * tf.factor))) = 0
    and (isnull(resynch.resynchdate,'1 jan 1900') <> rpd.perDate)

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by brettdai View Post
    it seems OUTER APPLY NOT supported by Sql Sever 2005 env.
    That's not correct - look it up in BoL.

    Please can you copy and paste the message?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2009
    Posts
    12
    Msg 156, Level 15, State 1, Line 397
    Incorrect syntax near the keyword 'OUTER'.
    Msg 170, Level 15, State 1, Line 400
    Line 400: Incorrect syntax near 'resynch'.
    Msg 156, Level 15, State 1, Line 461
    Incorrect syntax near the keyword 'END'.

    Thanks very much for your help again, i am a bit new to T-Sql And this is so important for me

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No probs. One thing that is helpful is to use the [ code] [/ code] tages (without spaces). These retain the formatting of your SQL and put the code in fixed width font. This helps reading it.

    I'll check the code now.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There is a comma before FROM - that should not be there.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2009
    Posts
    12
    Sorry, that comma wasn't existing in my real code. Just for simplification purpose i removed some columns from that select section and left the comma there. But that is not the actual problem in my code.

  11. #11
    Join Date
    Nov 2009
    Posts
    12
    And also the word APPLY does not turn grey but OUTER does in my code. you know normally when you type INNER JOIN both INNER and JOIN will be greyed out as the recoginised identifies by MS Sql. Hope this helps for your checking

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - please can you copy and paste your code exactly.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Nov 2009
    Posts
    12
    The subselect every time only returns at most one row( the closest duedate to the perdate) given by the jobid and Perdate passed from outer query. That's what i want to get.

  14. #14
    Join Date
    Nov 2009
    Posts
    12

    real code

    HTML Code:
    INSERT into @lt_FreqDate
    		(
    			jobid, 
    			DueDate, 
    			Frequency
    		)
    		SELECT 
    			freq.jobid, 
    			rpd.perDate, 
    			1
    		FROM #tmpFreq freq
    		  INNER JOIN dbo.refPeriodDays rpd
    			  ON rpd.perDate >= freq.startDate 
    			      AND  rpd.perDate > freq.Actuals_Limit_Date
    			      AND  rpd.perDate <= @lv_endDate
    		  LEFT JOIN  @lt_timeFactor tf
    			  ON freq.FrequencyUom = tf.FrequencyUom
              OUTER APPLY (select jobidsuped, max(datedue) as resynchdate
                           from #tmpResynch                     
                           where jobidsuped = freq.jobid
                              and datedue < rpd.perDate) resynch
    		WHERE freq.SuppressingCount = @lv_Pass  
              AND freq.FrequencyUom IN ('DAY', 'WEEK')
    		  AND freq.Interval >= 1  -- Added to include the trivial case - per day in order to get the correct job schedule dates
    		  AND (DATEDIFF(dd, rpd.perDate, case when resynch.resynchdate is null then freq.startDate else resynch.resynchdate end) % CONVERT(integer, ((case when freq.Interval <= 0 then 1 else freq.Interval end) * tf.factor))) = 0
              and (isnull(resynch.resynchdate,'1 jan 1900') <> rpd.perDate)

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sorry - I can't debug code I can't see.

    Have a look at some examples of OUTER APPLY and CROSS APPLY and slowly build up your query from the beginning. E.g.
    Vadivel's blog: Cross Apply and Outer Apply in SQL Server 2005
    Prove the basic principle and then your can build it up
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •