sqloracle-databaseoracle11goracle10g

Logic on Oracle Tree


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,

Solution

  • 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
    

    Result: