Convert the below logic in oracle tree-CodePudding

Recursively the parent is inserted first and then the children. How can I migrate the logic in Oracle using parent-child.The logic for the SQL should be first the parent is evaluated and then inserted or added and then the child is checked and then it is prepared.

Thank you for the response , the table structure as follow Name

REF_ID        NOT NULL NUMBER        
REF_TYPE_ID   NOT NULL NUMBER        
PARENT_REF_ID          NUMBER        
REF_VALUE     NOT NULL VARCHAR2(255)

Now the expected result conditions: we need to start finding the query with ref_id, so if ref_id is supposed 14, then we need to check the corresponding should have parent_ref_id is null or not, if parent_ref_id is present suppose 4, then we need to again get the value for 4 as ref_id and check is parent_id is null or not If null then we need to check the value ref_type_id whether it is 1, 2,3,4,..; etc and based on the condition we need to append the result 4(parent_ref_id) with some text let it be 'article 4'. Now resuming back to the loop, the user had enter ref_id 14, then we need to check the value for ref_type_id for the corresponding and then we need to append with the result suppose 'par 14' and this result had to come after parent result: article 4 par 14 (article 4 - parent, par 14 children)

sample data

In the attached sample data , check ref_id has data 502 and and parent_ref_id is 501 , so parent_ref_id is not null , then again we check the table with ref_id 501 and in the above you see the parent_ref_id is null there the loop will stop. Now we will check the ref_id 501 ref_type_id value in its case it is 1 and then with 501 will append article - article 501 , now going back again in loop we had ref_id 501 whose ref_type_id is 2 then we append with "article 501" "par 501" - expected result.

CodePudding user response：

I believe that using a CONNECT BY statement with SYS_CONNECT_BY_PATH should be able to help you achieve your goal.

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;

   STARTING_REF_ID                   REF_LABEL
__________________ ___________________________
               501 article 501
               502 par 502,article 501
               503 (503,par 502,article 501

CodePudding user response：

I had attached the result from the screen but I am unable to understand why it is not accepting comma