Tuesday, March 3, 2020

Oracle tree queries

SELECT parent_COST_CENTER_ID,COST_CENTER_ID,sum(parent_price) parent_price,lVL
FROM(
        SELECT parent_COST_CENTER_ID,COST_CENTER_ID,
            (TOTAL_ACTUAL_DEBIT) parent_price,lVL
        FROM COST_JOURNALTREES
        WHERE BRANCH_ID = 216
    ) t
GROUP BY parent_COST_CENTER_ID,COST_CENTER_ID,LVL
/

select DISTINCT
   connect_by_root parent_COST_CENTER_ID rootParent,  
   PARENT_SUM,
   COST_CENTER_ID,
   TOTAL_ACTUAL_DEBIT,
   prior TOTAL_ACTUAL_DEBIT prior_TOTAL_ACTUAL_DEBIT,
   level lvl
 FROM
   COST_JOURNALTREES
   WHERE BRANCH_ID = 216 --AND cost_center_id = 407--IN (427,409,407)
   START WITH PARENT_COST_CENTER_ID IS NOT NULL
   connect by prior COST_CENTER_ID = parent_COST_CENTER_ID
/

No comments:

Post a Comment