Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH recursive HIERARQUIA
- AS (SELECT TC.ID
- , TC.id_pai
- , TC.id_filho
- ,C.nome
- ,C.ativo
- , CAST(lpad(TC.ID,9,0)AS VARCHAR(2000)) AS Ordem
- FROM tree_control TC
- INNER JOIN Control C ON
- C.id = TC.id_filho
- WHERE TC.id_pai IS NULL
- AND C.ativo = 'S'
- UNION ALL
- SELECT TC.ID
- , TC.id_pai
- , TC.id_filho
- ,C.nome
- ,C.ativo
- , CAST(H.Ordem || lpad(TC.id,9,0)AS VARCHAR(2000))
- FROM HIERARQUIA H
- JOIN tree_control TC ON
- H.ID_Filho = TC.id_Pai
- INNER JOIN Control C ON
- C.id = TC.id_filho
- WHERE H.ativo = 'S'
- )
- SELECT
- H.ID
- , H.id_pai
- , H.id_filho
- ,H.nome
- ,H.ativo
- ,H.Ordem
- FROM HIERARQUIA H
- WHERE ((H.id_pai = :ID) OR (:P_ID = 'S'))
- ORDER BY H.Ordem
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement