CROSS APPLY on named sets

Question

CROSS APPLY on named sets

MMartin1

One Orange Chip

Points: 27840
More actions
January 12, 2016 at 8:30 pm

#399557

Comments posted to this topic are about the item CROSS APPLY on named sets
----------------------------------------------------

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281344 More actions · Answer 1

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

Eirikur Eiriksson SSC Guru Points: 182947 More actions · Answer 2

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.

morlindk SSCrazy Points: 2294 More actions · Answer 3

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.

tripleAxe SSCertifiable Points: 5707 More actions · Answer 4

The T-SQL parses OK but does not compile. A lesson re-learnt this morning for me; the difference between parse and compile.

david.dilworth Right there with Babe Points: 780 More actions · Answer 5

I think it's the difference between "parse" and "compile" that is causing the confusion here.

Carlo Romagnano SSC-Insane Points: 22758 More actions · Answer 6

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

anilkpadule Valued Member Points: 63 More actions · Answer 7

anilkpadule

Valued Member

Points: 63

January 13, 2016 at 4:49 am

#1851530

This is what I got

c1 c1

77

78

79

edwardwill SSCertifiable Points: 5936 More actions · Answer 8

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?

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 9

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

edwardwill SSCertifiable Points: 5936 More actions · Answer 10

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.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 11

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.

Rich Mechaber SSChampion Points: 11130 More actions · Answer 12

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

edwardwill SSCertifiable Points: 5936 More actions · Answer 13

edwardwill

SSCertifiable

Points: 5936

January 13, 2016 at 7:32 am

#1851576

See attachment.

Edward

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 14

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!

CROSS APPLY on named sets

Cookies on SQLServerCentral