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: Sunday, December 14, 2014 6:31 AM
Points: 123, Visits: 526

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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:51 AM
Points: 14,201, Visits: 28,530
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 Query Performance Tuning
and
SQL Server Execution Plans

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

Add to briefcase ««12

Permissions Expand / Collapse