Get path hierarhy using connect by prior vertical instead of horizontal in Oracle?

Question

Hi I have an SQL statement using connect by prior. I want to get the path by specific where criteria. Following SQL works fine, but I don't want to get just a single row matching the criteria, I want also to get each parent record (vertically up the hierarchy).

SELECT SUBSTR(SYS_CONNECT_BY_PATH(R.CALCULATION_PLAN_CODE, '.'),4) CODE_PATH,
       SUBSTR(SYS_CONNECT_BY_PATH(R.CALCULATION_PLAN_NAME, ' > '),29) NAME_PATH,           
       R.CALCULATION_PLAN_CODE,
       R.CALCULATION_PLAN_NAME        
  FROM PRM.MOVABLE_CALCULATION_PLAN R
  WHERE R.RELATED_YEAR = :relatedYear
  AND R.CALCULATION_PLAN_PARENT_OID != 0
  AND REGEXP_LIKE (R.CALCULATION_PLAN_NAME,'ROL')
  AND  CONNECT_BY_ISLEAF  = 1 
 START WITH R.OID =
          (SELECT R1.OID
             FROM PRM.MOVABLE_CALCULATION_PLAN R1
            WHERE (R1.CALCULATION_PLAN_PARENT_OID IS NULL OR R1.CALCULATION_PLAN_PARENT_OID = 0) AND R1.RELATED_YEAR=:relatedYear) CONNECT_BY_PRIOR  R.OID = R.CALCULATION_PLAN_PARENT_OID;

I don't want the output like this: I want it like this:

150       İLK MADDE VE MALZEMELER
150.1     İLK MADDE VE MALZEMELER > KIRTASİYE MALZEMELERİ GRUBU
150.1.1   İLK MADDE VE MALZEMELER > KIRTASİYE MALZEMELERİ GRUBU > ROL    <-----


150       İLK MADDE VE MALZEMELER
150.1     İLK MADDE VE MALZEMELER > KIRTASİYE MALZEMELERİ GRUBU
150.1.1   İLK MADDE VE MALZEMELER > KIRTASİYE MALZEMELERİ GRUBU > YAZI ARAÇLARI  
150.1.1.1 İLK MADDE VE MALZEMELER > KIRTASİYE MALZEMELERİ GRUBU > YAZI ARAÇLARI > ROLLER KALEM    <-----

How to achieve this ?

Thanks in advance

Answer 1

A solution will need to address your table more than once, since your desired output can have duplicates: a partial path (e.g. "150.1") may lead to two different leafs of interest, so it will occur twice (or more).

Note that you don't need to repeat the where conditions in the start with clause. They are applied anyway, also for the starting records.

Here is the query:

SELECT NODE.CODE_PATH, NODE.NAME_PATH FROM
      (
      SELECT     OID,
                 SYS_CONNECT_BY_PATH(R.OID, ',') OID_PATH
      FROM       MOVABLE_CALCULATION_PLAN R
      WHERE      REGEXP_LIKE (R.CALCULATION_PLAN_NAME,'ROL')
                 AND CONNECT_BY_ISLEAF = 1
                 AND R.RELATED_YEAR = :relatedYear
      START WITH NVL(CALCULATION_PLAN_PARENT_OID, 0) = 0
      CONNECT BY PRIOR  R.OID = R.CALCULATION_PLAN_PARENT_OID
      ) LEAF
INNER JOIN
      (
      SELECT     SUBSTR(SYS_CONNECT_BY_PATH(R.CALCULATION_PLAN_CODE, '.'),2) CODE_PATH,
                 SUBSTR(SYS_CONNECT_BY_PATH(R.CALCULATION_PLAN_NAME, ' > '),4) NAME_PATH,
                 OID,
                 SYS_CONNECT_BY_PATH(R.OID, ',') OID_PATH
      FROM       MOVABLE_CALCULATION_PLAN R
      START WITH NVL(CALCULATION_PLAN_PARENT_OID, 0) = 0
      CONNECT BY PRIOR  R.OID = R.CALCULATION_PLAN_PARENT_OID
      ) NODE
ON NODE.OID = LEAF.OID
OR LEAF.OID_PATH LIKE '%,' || NODE.OID || ',%'

Result:

CODE_PATH   NAME_PATH
----------  ------------------------------------------------------------
150         İLK MADDE VE MALZEMELER
150.1       İLK MADDE VE MALZEMELER > KIRTASİYE MALZEMELERİ GRUBU
150.1.1     İLK MADDE VE MALZEMELER > KIRTASİYE MALZEMELERİ GRUBU > ROL
150         İLK MADDE VE MALZEMELER
150.1       İLK MADDE VE MALZEMELER > KIRTASİYE MALZEMELERİ GRUBU
150.1.1     İLK MADDE VE MALZEMELER > KIRTASİYE MALZEMELERİ GRUBU > YAZI ARAÇLARI
150.1.1.1   İLK MADDE VE MALZEMELER > KIRTASİYE MALZEMELERİ GRUBU > YAZI ARAÇLARI > ROL

And a fiddle.

The idea is that in a first sub-select you select the leafs, with the where conditions as you provided. Then you do another sub-select that selects all nodes with their paths, without any filtering. Finally you match those nodes as being in the line of descent of the first filtered leaf nodes.