Menu
I want a first a normal CTE on table and followed by a recursive CTE
how can I combine this two?
I know for multiple pure normal CTE I can do
WITH CTE1 AS(
), CTE2 AS(),...
so I have tried
WITH CTE1 AS(
), RECURSIVE CTE2()
but that gives me a syntax error
573
A straightforward question deserves a straightforward answer: yes, you can. Now that you know how to use multiple CTEs, writing a CTE that references another CTE is just a variation of what you've learned.
The first CTE is separated from the second one by a comma. This also goes if you write more than two CTEs: all the CTEs are separated by a comma. However, no matter how many CTEs you have, there's no comma between the last CTE and the main query.
You can create recursive CTE or use a reference of a CTE to another CTE. However there is a slight differences compared with other databases - The referenced CTE must be present after the referencing CTE.
Unlike a derived table, a CTE behaves more like an in-line view and can be referenced multiple times in the same query. Using a CTE makes complex queries easier to read and maintain. Because a CTE can be referred to multiple times in a query, syntax can be simpler.
Just put the recursive at the start, even if the recursive one comes later:
with recursive cte1 as (
...
), cte2 as (
-- here comes the recursive cte
...
)
select *
from ...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
