1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to implement hierarchical query to get below outpu

Discussion in 'SQL PL/SQL' started by Shanmugapriya, Apr 11, 2017.

  1. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    48
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    Hi,
    I know there are a lot of website to know about hierarchical query but not getting the correct output as desired.

    I am in need to use hierarchical query:

    I have table with values

    sub_node_pk low_term_no high_term_no

    123 1 3

    342 5 7



    I need query with level being used to get as below



    level sub_node_pk

    1 123

    2 123

    3 123

    5 342

    6 342

    7 342



    Kindly help
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,466
    Likes Received:
    355
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You cannot get a heirarchical query with that data, there's nothing to connect rows by:

    Code (SQL):
    SQL> SELECT level+1, low_term_no, sub_node_pk
      2  FROM yoopeaster
      3  CONNECT BY nocycle sub_node_pk = prior sub_node_pk;

       LEVEL+1 LOW_TERM_NO SUB_NODE_PK
    ---------- ----------- -----------
             2           1         123
             2           5         342

    SQL>
    You'll need data like this to use CONNECT BY successfully:

    Code (SQL):
    SQL> CREATE TABLE yoopeaster(
      2  sub_node_pk        NUMBER,
      3  term_no NUMBER);

    TABLE created.

    SQL>
    SQL> INSERT INTO yoopeaster
      2  VALUES(123,1);

    1 ROW created.

    SQL> INSERT INTO yoopeaster
      2  VALUES(123,2);

    1 ROW created.

    SQL> INSERT INTO yoopeaster
      2  VALUES(123,3);

    1 ROW created.

    SQL> INSERT INTO yoopeaster
      2  VALUES(342, 5);

    1 ROW created.

    SQL> INSERT INTO yoopeaster
      2  VALUES(342, 6);

    1 ROW created.

    SQL> INSERT INTO yoopeaster
      2  VALUES(342, 7);

    1 ROW created.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> SELECT * FROM yoopeaster;

    SUB_NODE_PK    TERM_NO
    ----------- ----------
            123          1
            123          2
            123          3
            342          5
            342          6
            342          7

    6 ROWS selected.

    SQL>
    SQL> SELECT term_no, sub_node_pk
      2  FROM yoopeaster
      3  CONNECT BY nocycle sub_node_pk = prior sub_node_pk;

       TERM_NO SUB_NODE_PK
    ---------- -----------
             1         123
             2         123
             3         123
             5         342
             6         342
             7         342

    6 ROWS selected.

    SQL>
    But the CONNECT BY does nothing. If you MUST use your data the only way to generate the missing numbers and generate the output you want is with PL/SQL:

    Code (SQL):
    SQL> CREATE TABLE yoopeaster(
      2  sub_node_pk        NUMBER,
      3  low_term_no        NUMBER,
      4  high_term_no       NUMBER);

    TABLE created.

    SQL>
    SQL> INSERT INTO yoopeaster
      2  VALUES(123,1,3);

    1 ROW created.

    SQL>
    SQL> INSERT INTO yoopeaster
      2  VALUES(342, 5,7);

    1 ROW created.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> DECLARE
      2          cursor get_data IS
      3          SELECT sub_node_pk,low_term_no,high_term_no
      4          FROM yoopeaster;
      5
      6          v_node  NUMBER;
      7          v_low   NUMBER;
      8          v_high  NUMBER;
      9
    10  BEGIN
    11          OPEN get_data;
    12          loop
    13                  fetch get_data INTO v_node, v_low, v_high;
    14                  exit WHEN get_data%notfound;
    15                          FOR i IN v_low..v_high loop
    16                                  dbms_output.put_line(i||'       '||v_node);
    17                          END loop;
    18          END loop;
    19  END;
    20  /
    1       123
    2       123
    3       123
    5       342
    6       342
    7       342

    PL/SQL PROCEDURE successfully completed.

    SQL>
    Again, you do not have the proper data structure to use CONNECT BY.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    681
    Likes Received:
    137
    Trophy Points:
    830
    Location:
    Russian Federation
    1) hierarchical query
    Code (Text):

    WITH yourtable(sub_node_pk ,low_term_no, high_term_no) AS
    (SELECT 123, 1 ,3 FROM dual UNION ALL
    SELECT 342 ,5 ,7 FROM dual
    )
    SELECT yt.* ,yt.low_term_no +LEVEL-1 your_val
    FROM yourtable yt
    CONNECT BY LEVEL <= (yt.high_term_no - yt.low_term_no +1)
    AND PRIOR sub_node_pk =sub_node_pk
    AND PRIOR sys_guid() IS NOT NULL;





     
    2)
    Solution is naming "the transfer of the parameters in the side"

    Code (SQL):
    WITH yourtable(sub_node_pk ,low_term_no, high_term_no) AS
    (SELECT 123, 1 ,3 FROM dual UNION ALL
    SELECT 342 ,5 ,7 FROM dual
    )
    SELECT yt.* ,yt.low_term_no+ gv.column_value - 1 your_val
    FROM yourtable yt,TABLE(CAST(MULTISET(SELECT  LEVEL FROM dual CONNECT BY LEVEL <=yt.high_term_no - yt.low_term_no+1) AS sys.odcinumberlist)) gv;
     
     
    Last edited: Apr 12, 2017