January 12, 2016 at 8:30 pm
Comments posted to this topic are about the item CROSS APPLY on named sets
----------------------------------------------------
January 12, 2016 at 8:33 pm
I dunno about the "correct" answer on this one. The statements compile for me but produce an error at run-time. Seems to be a bit of a mixup here.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 12, 2016 at 9:36 pm
SQLRNNR (1/12/2016)
I dunno about the "correct" answer on this one. The statements compile for me but produce an error at run-time. Seems to be a bit of a mixup here.
What version of SQL Server? It fails the compilation on 2008,2008R2,2012 and 2014.
January 13, 2016 at 2:00 am
Phew.., I got the two points for the answer - but can't say I got the point of the question, even reading the outline. - Lucky though, to keep my streak alive: Tomorrow it is either one full month since mid-december, or devastation.
January 13, 2016 at 2:22 am
The T-SQL parses OK but does not compile. A lesson re-learnt this morning for me; the difference between parse and compile.
January 13, 2016 at 3:01 am
I think it's the difference between "parse" and "compile" that is causing the confusion here.
January 13, 2016 at 3:16 am
I disagree with explanation, the error generated
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "o.object_id" could not be bound.
it's not caused by CROSS APPLY, but a reference to a column not yet linked.
These examples generate the same error:
SELECT s1.c1, o.object_id
FROM (values (o.object_id+2)) s1(c1)
CROSS JOIN sys.objects o
or
SELECT s1.c1, o.object_id
FROM (values (o.object_id+2)) s1(c1)
JOIN sys.objects o on 1=1
January 13, 2016 at 4:49 am
This is what I got
c1 c1
77
78
79
January 13, 2016 at 6:18 am
Eirikur Eiriksson (1/12/2016)
SQLRNNR (1/12/2016)
I dunno about the "correct" answer on this one. The statements compile for me but produce an error at run-time. Seems to be a bit of a mixup here.What version of SQL Server? It fails the compilation on 2008,2008R2,2012 and 2014.
I can't find any option to compile a query. Parse, yes (the query parses). Execute, yes (the query fails - "The multi-part identifier "s2.c1" could not be bound.") But compile?
January 13, 2016 at 7:16 am
edwardwill (1/13/2016)
Eirikur Eiriksson (1/12/2016)
SQLRNNR (1/12/2016)
I dunno about the "correct" answer on this one. The statements compile for me but produce an error at run-time. Seems to be a bit of a mixup here.What version of SQL Server? It fails the compilation on 2008,2008R2,2012 and 2014.
I can't find any option to compile a query. Parse, yes (the query parses). Execute, yes (the query fails - "The multi-part identifier "s2.c1" could not be bound.") But compile?
Generate the estimated execution plan
January 13, 2016 at 7:20 am
Jacob Wilkins (1/13/2016)
edwardwill (1/13/2016)
Eirikur Eiriksson (1/12/2016)
SQLRNNR (1/12/2016)
I dunno about the "correct" answer on this one. The statements compile for me but produce an error at run-time. Seems to be a bit of a mixup here.What version of SQL Server? It fails the compilation on 2008,2008R2,2012 and 2014.
I can't find any option to compile a query. Parse, yes (the query parses). Execute, yes (the query fails - "The multi-part identifier "s2.c1" could not be bound.") But compile?
Generate the estimated execution plan
I did that. No compilation errors reported.
January 13, 2016 at 7:25 am
edwardwill (1/13/2016)
Jacob Wilkins (1/13/2016)
edwardwill (1/13/2016)
Eirikur Eiriksson (1/12/2016)
SQLRNNR (1/12/2016)
I dunno about the "correct" answer on this one. The statements compile for me but produce an error at run-time. Seems to be a bit of a mixup here.What version of SQL Server? It fails the compilation on 2008,2008R2,2012 and 2014.
I can't find any option to compile a query. Parse, yes (the query parses). Execute, yes (the query fails - "The multi-part identifier "s2.c1" could not be bound.") But compile?
Generate the estimated execution plan
I did that. No compilation errors reported.
Hmmm...generates an error for me.
January 13, 2016 at 7:32 am
SQLRNNR (1/12/2016)
I dunno about the "correct" answer on this one. The statements compile for me but produce an error at run-time. Seems to be a bit of a mixup here.
+1
January 13, 2016 at 7:32 am
See attachment.
Edward
January 13, 2016 at 7:34 am
edwardwill (1/13/2016)
See attachment.Edward
Yes, that works. That's the version he gives in the answer that is supposed to work, because the order of the table valued constructors has changed. Try the one actually mentioned in the question
EDIT: This is also why the example with APPLY is different than the similar examples given earlier with JOINs. The order in which the table valued constructor is used in the JOIN case doesn't matter. If it references the other table aliased in the join it will fail. With APPLY, the order determines whether it fails or not.
From https://msdn.microsoft.com/en-us/library/ms177634.aspx:
Using APPLY
Both the left and right operands of the APPLY operator are table expressions. The main difference between these operands is that the right_table_source can use a table-valued function that takes a column from the left_table_source as one of the arguments of the function. The left_table_source can include table-valued functions, but it cannot contain arguments that are columns from the right_table_source.[/i]
Cheers!
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy