Intégrer un arbre dans oracle

Question

J'ai une requête comme ci-dessous :

 select * from (
select * from (
select distinct * from TBL_IDPS_TREE 
START WITH LEDGER_CODE in  (

 10912520000
,10825060000
,10912380000
,11311110201
)
    CONNECT BY PRIOR  parent_CODE = LEDGER_CODE
  )a  left join (
select /*+ PARALLEL(AUTO) */  balance as "y300" , ledger_code as "id",'' as "x300" , round(abs(balance)/30835,2) as "z300",name as "name"   from tbl_ledger_archive where ledger_code in ( 
10912520000
,10825060000
,10912380000
,11311110201) and eff_date ='29-MAY-19'
) b
on a.LEDGER_CODE = b."id")
START WITH PARENT_CODE is null
connect by PRIOR LEDGER_CODE = Parent_CODE

 ;

et le résultat est :

x300,y300,z300 sont les valeurs de l'arbre.

Je souhaite modifier une requête qui intègre la valeur de l'arbre pour x300,y300,z300 Je veux dire que la requête doit intégrer la valeur de l'arbre depuis les feuilles jusqu'à la racine.

Answer 1

Utilice connect_by_root dans la sous-requête sur l'arbre des comptes et la jointure avec le grand livre.

Démonstration :

with TBL_IDPS_TREE as (
  select 10912520000 LEDGER_CODE, 1091252 parent_CODE from dual union all
  select 10825060000, 1091252 from dual union all
  select 1091252, 1091 from dual union all
  select 1091, null from dual
), tbl_ledger_archive as (
   select  500000 as "y300" , 10912520000 as "id", '' as "x300" , round(500000/30835, 2) as "z300", 'abc' as "name" from dual union all
   select  600000 as "y300" , 10825060000 as "id", '' as "x300" , round(600000/30835, 2) as "z300", 'abc' as "name" from dual 
)
select a.LEDGER_CODE, a.parent_CODE, l."x300", sum(l."y300") "y300", sum(l."z300") "z300"
from (
  select distinct t.*, connect_by_root LEDGER_CODE as accRoot
  from TBL_IDPS_TREE t
     START WITH LEDGER_CODE in  (
                 10912520000 
        ,10825060000
        ,10912380000
        ,11311110201
        )
      CONNECT BY PRIOR parent_CODE = LEDGER_CODE
) a
left join tbl_ledger_archive l
  on l."id" = a.accRoot 
group by a.LEDGER_CODE, a.parent_CODE, l."x300" ;