Recursive query firebird

0

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?

    
asked by anonymous 23.08.2018 / 20:42

0 answers