Outer apply query

Databases
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?

Error message

Invalid object name ‘Z’.

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

3

I read somewhere that outer apply makes it possible to reach references outside the subquery, is that false?

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?

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 :slight_smile:

6

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

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…