Hey guys, I'm trying to reach some table variables with the help of outer apply. The query i got so far is this.
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?
Invalid object name 'Z'.
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
I read somewhere that outer apply makes it possible to reach references outside the subquery, is that false?
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?
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
SELECT u.id, mbg.marker_value
FROM dps_user u
(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'
ORDER BY m.creation_date
) AS MBG
WHERE u.id = 'u162231993';
Example from Stackoverflow, he can access the u from inside that subquery.
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...
This topic is now closed. New replies are no longer allowed.