Below is the structure for the table :
REF_ID NOT NULL NUMBER
REF_TYPE_ID NOT NULL NUMBER
PARENT_REF_ID NUMBER
REF_VALUE NOT NULL VARCHAR2(255)
Following Query had achieved the following result, but I might need the result :
WITH
refs (ref_id,
ref_type_id,
parent_ref_id,
ref_value)
AS
(SELECT 501, 1, NULL, 207 FROM DUAL
UNION ALL
SELECT 502, 2, 501, 4 FROM DUAL
UNION ALL
SELECT 503, 3, 502, 1 FROM DUAL)
SELECT CONNECT_BY_ROOT r.ref_id as starting_ref_id,
TRIM (
',' FROM
SYS_CONNECT_BY_PATH (
CASE r.ref_type_id
WHEN 1 THEN 'article '
WHEN 2 THEN 'par '
WHEN 3 THEN '('
WHEN 4 THEN 'point '
WHEN 5 THEN 'sous '
WHEN 6 THEN NULL
WHEN 8 THEN NULL
ELSE '/'
END
|| r.ref_id,
',')) AS ref_label
FROM refs r
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR r.parent_ref_id = r.ref_id;
Output from the query :
STARTING_REF_ID REF_LABEL
501 article 501
502 par 502,article 501
503 (503,par 502,article 501
Expected result:
STARTING_REF_ID REF_LABEL
501 article 501
502 article 501,par 502
503 article 501,par 502,(503,
Added reverse to the select
WITH
refs (ref_id,
ref_type_id,
parent_ref_id,
ref_value)
AS
(SELECT 501, 1, NULL, 207 FROM DUAL
UNION ALL
SELECT 502, 2, 501, 4 FROM DUAL
UNION ALL
SELECT 503, 3, 502, 1 FROM DUAL)
SELECT CONNECT_BY_ROOT r.ref_id as starting_ref_id,
TRIM (
',' FROM
reverse( --reverse path order (also reverts letters in words)
SYS_CONNECT_BY_PATH (
reverse( --put letters in words and numbers in ids back in the right order
CASE r.ref_type_id
WHEN 1 THEN 'article '
WHEN 2 THEN 'par '
WHEN 3 THEN '('
WHEN 4 THEN 'point '
WHEN 5 THEN 'sous '
WHEN 6 THEN NULL
WHEN 8 THEN NULL
ELSE '/'
END
|| r.ref_id),
',')
) ) AS ref_label
FROM refs r
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR r.parent_ref_id = r.ref_id