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!
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
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
WITH RECURSIVE RecCTE (ParentNodeID, NodeID, NodeLevel,NodeText) AS
1 AS NodeLevel,
WHERE ParentNodeID IS NULL
SELECT p.NodeID AS ParentNodeID,
c.NodeID AS NodeID,
p.NodeLevel +1 AS NodeLevel,
FROM FirstCTE c
JOIN RecCTE p
ON c.ParentNodeID = p.NodeID
SELECT * FROM RecCTE
Thanks for your reply. Of course - in this case you're right. Below now why im looking for "CTE referes to another CTE".
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".
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).
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.
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?
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.
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?