I created a table that it gets its own id, to create something like a tree, cascade, etc. I'm having trouble making a query and returning the record "parent" and its "children", "grandchildren", etc.
I made a query, but it gives " has cyclic dependencies " error. The query is as follows:
WITH CTE_CENTRO
AS (
SELECT
A.CEC_ID,
A.CEC_NOME,
A.CEC_CENTROCUSTO_ID_PAI ,
0 AS NIVEL
FROM CENTRO_CUSTO A WHERE A.CEC_CENTROCUSTO_ID_PAI IS NULL
UNION ALL
SELECT
D.CEC_ID,
D.CEC_NOME,
D.CEC_CENTROCUSTO_ID_PAI,
C.NIVEL + 1
FROM CTE_CENTRO C
INNER JOIN CENTRO_CUSTO D ON D.CEC_CENTROCUSTO_ID_PAI = C.CEC_ID
)
SELECT * FROM CTE_CENTRO
The structure of the CENTRO_CUSTO table is as follows:
CEC_ID
CEC_CODIGO
CEC_NOME
CEC_CENTROCUSTO_ID_PAI
Can anyone help me?