FB-3,09 Recursive CTE

[Henry Olive 5]

I wish everyone a healthy day

I'm testing to use Recursive CTE instead of my Recursive S.Proc.

 

WITH RECURSIVE CTE AS (SELECT B.ITEMCODE, B.SUBITEMCODE, IT.ITEMTYPE, B.QTY,
V.CUSTNO, V.PRICE, IT.STOCK
FROM BOM B 
JOIN ITEMS IT ON IT.ITEMCODE=B.SUBITEMCODE
JOIN VENDOR V ON V.ITEMCODE=B.SUBITEMCODE )

SELECT ITEMCODE, SUBITEMCODE, ITEMTYPE, QTY,  CUSTNO, PRICE, STOCK
FROM CTE
WHERE ITEMCODE='ABC'

 

Above Query works but it doesnt explode the BOM, just shows BOM table's datas.

Thank You

 

 

 

[Ondrej Kelle 56]

Your CTE is in fact not recursive as it doesn't reference itself or use UNION ALL.

Generalized syntax for a recursive CTE looks like this:

WITH RECURSIVE <cte_alias> AS (
SELECT <parent data> -- root node’s data
UNION ALL
SELECT <child data> -- children’s data
JOIN <cte_alias> ON <parent_link>
) -- DO // for the Delphians
SELECT * FROM <cte_alias>

Source:
https://www.firebirdsql.org/file/community/ppts/fbcon11/FBTrees2011.pdf

[Henry Olive 5]

Thank you so much Ondrej,

to use UNION ALL is a must in a CTE ?

without using UNION ALL cant we use CTE ?
 

[Ondrej Kelle 56]

Without UNION ALL referencing itself, it's just a non-recursive CTE.