SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Hybrid View

  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,247
    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
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    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"

  5. #5
    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.

  6. #6
    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...

  7. #7
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,181
    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?


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
  •