April 15, 2013 at 8:54 am
I have a question about when a CROSS APPLY is processed in a query
Using the following code as an example:
DECLARE @oc TABLE
(
BatchVARCHAR(5),
DateDataDATETIME
)
INSERT INTO @oc
SELECT 'ABCDH', '20130101' UNION ALL
SELECT 'DFGHY', '20130102' UNION ALL
SELECT 'DFGJU', '20130103' UNION ALL
SELECT 'DFGHP', '20130104'
DECLARE @PO TABLE
(
BatchVARCHAR(5),
DateDataDATETIME
)
INSERT INTO @PO
SELECT 'ABCDH', '20130111' UNION ALL
SELECT 'DFGHY', '20130112' UNION ALL
SELECT 'DFGJU', '20130113'
SELECTO.Batch,
CA.LBat,
CA1.POLBat,
O.DateData,
P.DateData
FROM@oc AS O
CROSS
APPLY(SELECT LEFT(O.Batch,2) AS LBat) AS CA
CROSS
APPLY(SELECT LEFT(P.Batch,2) AS POLBat) AS CA1
LEFT
JOIN@PO AS P
ON O.Batch = P.Batch
This returns:
Msg 4104, Level 16, State 1, Line 37
The multi-part identifier "P.Batch" could not be bound.
However, if you switch the second CROSS APPLY to after the JOIN it appears to work:
SELECTO.Batch,
CA.LBat,
CA1.POLBat,
O.DateData,
P.DateData
FROM@oc AS O
CROSS
APPLY(SELECT LEFT(O.Batch,2) AS LBat) AS CA
LEFT
JOIN@PO AS P
ON O.Batch = P.Batch
CROSS
APPLY(SELECT LEFT(P.Batch,2) AS POLBat) AS CA1
Would any joins not be processed before the CROSS APPLY?
April 15, 2013 at 9:08 am
It works just like the way your joins do. You can't reference a table that has not yet been joined. CROSS APPLY is really nothing more that more complex subselect.
select *
from tablea a
join tableb b on b.ID = c.ID
join tablec c on c.ID = a.ID
The original query you posted is much like the one above. It is pretty obvious this won't work right? The same is true when you use APPLY.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply