Hierarchical Query Level

Question

The below query is returning data as expected but we have a requirement that where there is level 1, it should be the last level and last level should be represented as 1st.

I am running this in Oracle database.

Data for TABLE_1:-

FK_1 |FK_2 | REL_CD
-----+-----+-------
345  | 123 | ORG
567  | 345 | ORG
897  | 567 | ORG

Data for Org table:-

FK |ORG   |CD
---+------+-----
123|Global|A_001
345|Canada|A_002
567|AB    |A_003
897|VAN   |A_004

I am getting below result, which is logically correct:-

FK  | ORG   | LVL| CD
----+-------+----+-----------
123 | Global| 4  |A_001
345 | Canada| 3  |A_002
567 | AB    | 2  |A_003
897 | VAN   | 1  |A_004

But the expected output is:-

FK  | ORG   | LVL| CD
----+-------+----+-----------
123 | Global| 1  |A_001
345 | Canada| 2  |A_002
567 | AB    | 3  |A_003
897 | VAN   | 4  |A_004

---

Query used

SELECT FK, ORG, LVL, CD
FROM (SELECT LEVEL AS LVL, CONNECT_BY_ROOT FK_2 AS FK_1, CONNECT_BY_ISCYCLE
      FROM TABLE_1 T1
      WHERE FK_2='897'
      CONNECT BY NOCYCLE PRIOR T1=T2
     ) INNER JOIN
     ORG
     ON ORG.FK= FK_1;

Answer 1

You can use a recursive CTE to get the result you want.

For example:

with
n (id, lvl) as (
  select fk, 1 from org a where not exists (select fk_1 from table_1 b where b.fk_1 = a.fk)
 union all
  select t.fk_1, n.lvl + 1
  from n
  join table_1 t on t.fk_2 = n.id
)
select o.fk, o.org, n.lvl, o.cd
from org o
left join n on n.id = o.fk
order by o.fk -- optional ordering

Result:

 FK   ORG     LVL  CD    
 ---- ------- ---- ----- 
 123  Global  1    A_001 
 345  Canada  2    A_002 
 567  AB      3    A_003 
 897  VAN     4    A_004

See running example at db<>fiddle.

Answer 2

Without sample data it is difficult to tell exactly but you want something like:

SELECT FK,
       ORG,
       LVL,
       CD
FROM   (
         SELECT 1 AS LVL,                  -- Generate a row for the parent at the root.
                123 AS FK_1
         FROM   DUAL
         UNION ALL
         SELECT LEVEL+1,
                FK_1                       -- Leaf value of this rather than root.
         FROM   TABLE_1
         START WITH FK_2=123               -- Start with this rather than end with it.
         CONNECT BY NOCYCLE
                PRIOR FK_1 = FK_2          -- Swap these.
       )
       INNER JOIN ORG
       ON ORG.FK= FK_1;

Which, for your sample data:

CREATE TABLE TABLE_1 ( FK_1, FK_2, REL_CD ) AS
SELECT 345, 123, 'ORG' FROM DUAL UNION ALL
SELECT 567, 345, 'ORG' FROM DUAL UNION ALL
SELECT 897, 567, 'ORG' FROM DUAL;

CREATE TABLE org( FK, ORG, CD ) AS
SELECT 123, 'Global', 'A_001' FROM DUAL UNION ALL
SELECT 345, 'Canada', 'A_002' FROM DUAL UNION ALL
SELECT 567, 'AB',     'A_003' FROM DUAL UNION ALL
SELECT 897, 'VAN',    'A_004' FROM DUAL;

Outputs:

 FK | ORG    | LVL | CD   
--: | :----- | --: | :----
123 | Global |   1 | A_001
345 | Canada |   2 | A_002
567 | AB     |   3 | A_003
897 | VAN    |   4 | A_004

db<>fiddle here