All Forums Database
Dominic77 4 posts Joined 07/14
23 Jul 2014
Common Table Expressions (CTE)
WITH FirstCTE (Attr1,Attr2) AS
(
 SELECT A.Attr1, A.Attr2 FROM DB.TBL_A A
 ),
SeccondCTE (FK_Attr1,Attr2,Attr3)  AS
(
 SELECT B.Attr1, B.Attr2, A.Attr1  
 FROM DB.TBL_B B
 JOIN FirstCTE A
  ON A.Attr1 = B.FK_Attr1
 )
SELECT * FROM SeccondCTE

Does TD support this kind if CTE? One CTE is referring to the prior CTE.
Thanks a lot!
 
 
 

Glass 225 posts Joined 04/10
23 Jul 2014

Dominic,
If I see what you're after correctly,
No need to join to second cte,
just join first to tbl_b

WITH CTE (Attr1,Attr2) AS
(
SELECT Attr1, Attr2 FROM DsB.TBL_A
)

SELECT B.Attr1, B.Attr2, a.Attr1
FROM DsB.TBL_B B
JOIN CTE A
ON A.Attr1 = B.FK_Attr1

Dominic77 4 posts Joined 07/14
23 Jul 2014
WITH FirstCTE (ParentNodeID,NodeID) AS
(
  -- let's assume a huge query...that's why I like to write the SQL only once and refer it twice in the recursive CTE
  SELECT ParentNodeID,
                    NodeID,
                    NodeText
  FROM DEV_DWH.Test
 ),
 WITH RECURSIVE RecCTE  (ParentNodeID, NodeID, NodeLevel,NodeText)  AS
(
  SELECT ParentNodeID,
                    NodeID,
                    1 AS NodeLevel,
                    NodeText
  FROM FirstCTE
  WHERE ParentNodeID IS NULL
  UNION ALL
  SELECT  p.NodeID AS ParentNodeID,
                    c.NodeID AS NodeID,
                    p.NodeLevel +1 AS NodeLevel,
                    c.NodeText
  FROM FirstCTE c
  JOIN  RecCTE p
  ON c.ParentNodeID = p.NodeID
)
SELECT * FROM RecCTE

Hi Robert
Thanks for your reply. Of course - in this case you're right. Below now why im looking for "CTE referes to another CTE".
 
Best regards,
Dominic

Dominic77 4 posts Joined 07/14
23 Jul 2014

....and please forget the second "WITH" :-)

dnoeth 4628 posts Joined 11/04
23 Jul 2014

Hi Dominic,
according to the manuals you should be able to do that.
But when I asked why a similar example didn't work, the answer was "it's known, currently the workaround is to define the 2nd CTE before the 1st CTE".

Dieter

dnoeth 4628 posts Joined 11/04
23 Jul 2014

Regarding the huge query int CTE #1 as source for the the recursive CTE #2:
Check if the optimizer resuses the result of #1 in a spool instead of re-calculating it for every recursive step. If not, you should materialize  #1 in a Volatile Table (plus you can set the ParentNodeId).

Dieter

Dominic77 4 posts Joined 07/14
25 Jul 2014

Hi Dieter
Thanks a lot for your answer. I'm using now the workaround with the changed order of the CTEs. It works....
Even if the optimizer would re-use a prior spool result...the SQL code needs to be duplicated and therefore being maintained twice.
Best regards,
 

b.saipavankumar 12 posts Joined 03/12
30 Apr 2016

Hi All,
Could someone help me understand what a CTE means? By the looks of it, Can i assume that it's something similar to a recursive query?

Thanks,
Sai Pavan Kumar Bhamidipati

ToddAWalter 316 posts Joined 10/11
30 Apr 2016

There are two forms of CTE. The first is an alternate syntax for specifying SQL select statements prior to the query which will use them. Many people and many SQL generation tools find this an easier way build the SQL and make it more readable and maintainable.
 
this form of CTE behave in effect as if that select expression in a view, then reference the view in the from clause of the query. Specifying it this way also lets the optimizer see the query and optimizer across the whole query the best way possible   
 
The second form of CTE is the enabler for recursive queries. This form is identified by WITH RECURSIVE keywords. 
 
 

b.saipavankumar 12 posts Joined 03/12
03 May 2016

Thanks Todd. If I understood it correctly, first form of CTE is more similar to a view and second form is similar to a volatile/derived table. For the first form, do we need to compile or create it, like we do for a view, before we use it? 
 

Thanks,
Sai Pavan Kumar Bhamidipati

b.saipavankumar 12 posts Joined 03/12
03 May 2016

Also, could you please share any link or pointer where I can find complete details about the CTE

Thanks,
Sai Pavan Kumar Bhamidipati

ToddAWalter 316 posts Joined 10/11
03 May 2016

No compilation necessary in either case. It is just part of the SQL SELECT statement and is all parsed/optimized together. The second form in not like a virtual table, it is very specific to defining a recursive select statement.
 
In the SQL Data Manipulation manual under the SELECT statement there is a subsection on the WITH modifier that tells all about it.

You must sign in to leave a comment.