Oracle sql - hierarchical query

Question

I am struggling to do a hierarchical query on the following table.

CLASSSTRUCTUREID  PARENT    CLASSIFCATIONID
1688                             FLT
1689              1688           ASSET
1690              1688           PMFLT
1691              1688           CM
1692              1691           POSTFAILCM

for instance I would like to get the output as below for the above example

FLT/CM/POSTFAILCM as FLT is the parent of CM and CM is the parent of POSTFAILCM

I used the following query but doesn;t give me the desired output. I just wanted to know where I do wrong.

SELECT LPAD(' ', 2*level-1)|| SYS_CONNECT_BY_PATH(CLASSIFICATIONID , '/') "Path"
,LEVEL
   FROM CLASSSTRUCTURE
   START WITH CLASSIFICATIONID = 'PREFAILCM' --TRIED 'FLT' TOO
   CONNECT BY PRIOR  TO_CHAR(CLASSSTRUCTUREUID) = PARENT

score 1 · Accepted Answer · answered Oct 30 '17 at 13:38

Hope this helps:

SELECT    LPAD (' ', 2 * LEVEL - 1) || SYS_CONNECT_BY_PATH (CLASSIFICATIONID, '/') PATH,
           LEVEL
      FROM CLASSSTRUCTURE
START WITH parent IS NULL
CONNECT BY PRIOR TO_CHAR (CLASSSTRUCTUREID) = PARENT;

Used create table :

CREATE TABLE CLASSSTRUCTURE AS
(select 1688 CLASSSTRUCTUREID, NULL PARENT,'FLT' CLASSIFICATIONID FROM DUAL UNION ALL
select 1689 CLASSSTRUCTUREID, 1688 ,'ASSET' FROM DUAL UNION ALL
select 1690 CLASSSTRUCTUREID, 1688 ,'PMFLT' FROM DUAL UNION ALL
select 1691 CLASSSTRUCTUREID, 1688 ,'CM' FROM DUAL UNION ALL
select 1692 CLASSSTRUCTUREID, 1691 ,'POSTFAILCM' FROM DUAL );

Oracle sql - hierarchical query

1 Answers1