SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Oct 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Outer apply query

    Hey guys, I'm trying to reach some table variables with the help of outer apply. The query i got so far is this.

    Code SQL:
    SELECT T.Firstcol, T.Secondcol, Z.Timmar, B.Timmar, C.Timmar, D.Timmar FROM @tble AS Z, @tble3 AS B, @tble4 AS C, @tble5 AS D 
    OUTER APPLY (SELECT TOP 1 L.Timmar FROM @tble2 AS L 
    INNER JOIN Z (NOLOCK)
    ON A.Personnummer = T.PNRCol OR B.Personnummer = T.PNRCol) AS tempres

    Is there any way to make this query work?

    Error message

    Invalid object name 'Z'.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    can't help you with APPLY...

    ... but you've got @tble defined as Z, and then apparently another table called Z inside your subquery, where you're joining L to Z on some column in table A but you have no table A, and some column in table T but you have no table T

    so, in short, in order to make your query work, you have to fix all those problems
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Oct 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I read somewhere that outer apply makes it possible to reach references outside the subquery, is that false?

  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    I've had no experience with APPLY either... Do you mind presenting some sample table data and your desired result and perhaps we can attack it another way?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by fasck View Post
    I read somewhere that outer apply makes it possible to reach references outside the subquery, is that false?
    references outside a subquery are possible in ordinary non-APPLY subqueries as well

    but references to tables that aren't mentioned anywhere in the query? that won't work
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Oct 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT u.id, mbg.marker_value
    FROM dps_user u
    OUTER APPLY
    (SELECT TOP 1 m.marker_value, um.profile_id
    FROM dps_usr_markers um (NOLOCK)
    INNER JOIN dps_markers m (NOLOCK)
    ON m.marker_id= um.marker_id AND
    m.marker_key = 'moneyBackGuaranteeLength'
    WHERE um.profile_id=u.id
    ORDER BY m.creation_date
    ) AS MBG
    WHERE u.id = 'u162231993';

    Example from Stackoverflow, he can access the u from inside that subquery.

  7. #7
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As I remember cross apply is equivalent to inner join and outer apply to left join and it make a bit difference on query performance apply is mach faster.
    fasck publish your table structure. Your SQL looks not right, but it is hard to fix without knowing your tables...


Bookmarks

Posting Permissions

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