Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

CTE and inner join Expand / Collapse
Author
Message
Posted Tuesday, January 11, 2011 12:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 1:41 AM
Points: 123, Visits: 515

Yep. A CTE can be referenced multiple times inside of the statement immediately following it, but it can't be referenced by multiple statements within the query.

you mean it can be used only once inside an inner join and that's all the problem?
Post #1046086
Posted Tuesday, January 11, 2011 12:38 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 15,737, Visits: 28,143
No. Not really. This is psuedo-code to describe the situation:

WITH MyCTE(...)

INSERT INTO MyTable
SELECT * FROM MyCTE AS x
JOIN MyCTE as y
ON x.ID = Y.ID
and x.Type=1
JOIN MyCTE AS z
ON x.ID = z.ID
AND z.Type=2

Something like this will work fine. But if I did this:

WITH MyCTE(...)

INSERT INTO MyTable
SELECT * FROM MyCTE as X

INSERT INTO MyOtherTable
SELECT * FROM MyCTE aS y

That will fail because the CTE only lives as long as the first statement following it's definition, regardless of how many times it's referenced within the statement.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1046090
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse