Common Table Expressions (CTE)

Database

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!

11 REPLIES
Enthusiast

Re: Common Table Expressions (CTE)

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

Re: Common Table Expressions (CTE)

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

Re: Common Table Expressions (CTE)

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

Senior Apprentice

Re: Common Table Expressions (CTE)

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".

Senior Apprentice

Re: Common Table Expressions (CTE)

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).

Re: Common Table Expressions (CTE)

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,

Enthusiast

Re: Common Table Expressions (CTE)

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?

Teradata Employee

Re: Common Table Expressions (CTE)

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. 

Enthusiast

Re: Common Table Expressions (CTE)

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?