fasck — 2012-10-30T12:19:23-04:00 — #1
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'.
r937 — 2012-10-30T12:37:39-04:00 — #2
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
fasck — 2012-10-30T12:44:44-04:00 — #3
I read somewhere that outer apply makes it possible to reach references outside the subquery, is that false?
kylewolfe — 2012-10-30T14:24:01-04:00 — #4
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?
r937 — 2012-10-30T14:30:12-04:00 — #5
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
fasck — 2012-10-30T17:20:45-04:00 — #6
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.
gk53 — 2012-10-31T12:38:07-04:00 — #7
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...