sqloracle-databaseconnect-by

Display immediate parent after CONNECT_BY_ROOT in Oracle PL SQL

I am using oracle 12 DB and client. Need help on this one. I want to populate a column which displays the root, child, and immediate parent after the root? lets say "CONNECT BY 2nd ROOT"?

Here is my code so far.

SELECT
          PARENT,
          CONNECT_BY_ROOT PARENT_ID AS ROOT_ID,
      ????2ND_ROOT_ID,
          CHILD_ID AS CHILD_ID 
     FROM TABLE
     START WITH 
        PARENT_ID = 1 
     CONNECT BY
         PRIOR CHILD_ID =  PARENT_ID

INPUT

PARENT_ID   CHILD_ID
1           1.1
1           1.2
1.1         1.1.1
1.1         1.1.2
1.1         1.1.3
1.1         1.1.4
1.1         1.1.5
1.2         1.2.1
1.2         1.2.2
1.1.1       1.1.1.1
1.1.1       1.1.1.2
1.1.3       1.1.3.1
1.1.3       1.1.3.2
1.1.3       1.1.3.3

OUTPUT:

ROOT_ID  2ND_ROOT_ID    CHILD_ID
    1        1.1        1.1
    1        1.2        1.2
    1        1.1        1.1.1
    1        1.1        1.1.2
    1        1.1        1.1.3
    1        1.1        1.1.4
    1        1.1        1.1.5
    1        1.2        1.2.1
    1        1.2        1.2.2
    1        1.1        1.1.1.1
    1        1.1        1.1.1.2
    1        1.1        1.1.3.1
    1        1.1        1.1.3.2
    1        1.1        1.1.3.3

Also tried using substring from connect by path

regexp_substr(SYS_CONNECT_BY_PATH, ....

To get the parent after the root but it gives me a ORA-01489 error.

Kindly advice and many thanks in advance

Solution

  • You can use conditional sys_connect_by_path:

    rtrim(sys_connect_by_path(case when level<=2 then id else null end, '.'),'.')