Forum Stats

  • 3,782,431 Users
  • 2,254,644 Discussions
  • 7,880,078 Comments

Discussions

Tricky output based on Hierarchy

Arif2018
Arif2018 Member Posts: 214 Red Ribbon
edited Aug 1, 2021 2:53PM in SQL & PL/SQL

i have a situation where i will be passing object to get the parent object , the challenge is there are 3 parents to that object , for example if i have object 4 and want to get object 3 it should get object 3 , if want to get parent object 2 it should get object 2 for object 4. For example , i will pass the child object along with level of parent then it should brng the right parent in hierarchy according to level of parent ,

[code]

create table test_object (mch_code varchar2(25),sup_mch_code varchar2(25), mch_code_level varchar2(20))


insert into test_object (mch_code,sup_mch_code , mch_code_level) values ('AAA-AAA-AAA-AA','AAA-AAA-AAA','L:5');

insert into test_object (mch_code,sup_mch_code , mch_code_level) values ('AAA-AAA-AAA','AAA-AAA','L:4');

insert into test_object (mch_code,sup_mch_code , mch_code_level) values ('AAA-AAA','AAA','L:3');

insert into test_object (mch_code,sup_mch_code , mch_code_level) values ('AAA','A','L:2');

insert into test_object (mch_code,sup_mch_code , mch_code_level) values ('BBB-BBB-BBB-BB','BBB-BBB-BBB','L:5');

insert into test_object (mch_code,sup_mch_code , mch_code_level) values ('BBB-BBB-BBB','BBB-BBB','L:4');

insert into test_object (mch_code,sup_mch_code , mch_code_level) values ('BBB-BBB','BBB','L:3');

insert into test_object (mch_code,sup_mch_code , mch_code_level) values ('BBB','B','L:2');


--get ancestor object

select get_ancestor_object('AAA-AAA-AAA','L:2') from dual;

--first is the object , second parameter is the level of parent that i want.

should return 'AAA'

select get_ancestor_object('AAA-AAA-AAA-AA,'L:3') from dual;

should return 'AAA-AAA'

[/CODE]

Tagged:

Best Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,014 Red Diamond
    Accepted Answer
    SELECT  *
      FROM  TEST_OBJECT
      WHERE MCH_CODE_LEVEL = 'L:2'
      START WITH MCH_CODE = 'AAA-AAA-AAA'
      CONNECT BY MCH_CODE = PRIOR SUP_MCH_CODE
             AND PRIOR MCH_CODE_LEVEL != 'L:2'
    /
    
    MCH_CODE                  SUP_MCH_CODE              MCH_CODE_LEVEL
    ------------------------- ------------------------- --------------------
    AAA                       A                         L:2
    
    
    SQL>
    

    SY.

    Arif2018
  • Hub Tijhuis
    Hub Tijhuis Member Posts: 135 Silver Badge
    Accepted Answer

    You did not insert the real top-level parents 'A' and 'B' . So you don't have any level 1 records.

    You did add the level yourself, but you can omit it and leave it to oracle.

    You could define the following function .


    create or replace function get_ancestor_object(p_mch_code varchar2, p_lvl number) return varchar2

    is

     v_mch_code test_object.mch_code%TYPE;

    begin

     begin

    with h as -- hierarchy from top parent to child to get the level of hierarchy

    (

    --select mch_code, sup_mch_code,mch_code_level, level lvl , sys_connect_by_path(mch_code,'~') pth , connect_by_root mch_code p from test_object connect by sup_mch_code =  prior mch_code

    select mch_code, level lvl ,   connect_by_root mch_code p from test_object connect by sup_mch_code =  prior mch_code

    start with sup_mch_code not in (select mch_code from test_object) -- to select the top parent records

    )

    select (select mch_code from h h2 where h2.p = h1.p and h2.lvl + 1 = p_lvl) into v_mch_code from h h1 where h1.mch_code = p_mch_code;

     exception when no_data_found then null;

     end;

    return v_mch_code;

    end;

    -- to run test

    select mch_code, mch_code_level

    , get_ancestor_object(mch_code,1) l1

    , get_ancestor_object(mch_code,2) l2

    , get_ancestor_object(mch_code,3) l3

    , get_ancestor_object(mch_code,4) l4

    , get_ancestor_object(mch_code,5) l5

    , get_ancestor_object(mch_code,6) l6

    from test_object order by 1

    -- if you want to avoid functions you can start with something like

    with h as

    (

    select mch_code, sup_mch_code,mch_code_level, level lvl , sys_connect_by_path(mch_code,'~') pth , connect_by_root mch_code p from test_object connect by sup_mch_code =  prior mch_code

    start with sup_mch_code not in (select mch_code from test_object)

    )

    select h1.* , (select mch_code from h h2 where h2.p = h1.p and h2.lvl = 2) from h h1

    Arif2018

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 822 Gold Trophy

    Hierarchical queries are described in the SQL Language Reference.

    Arif2018
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,014 Red Diamond
    Accepted Answer
    SELECT  *
      FROM  TEST_OBJECT
      WHERE MCH_CODE_LEVEL = 'L:2'
      START WITH MCH_CODE = 'AAA-AAA-AAA'
      CONNECT BY MCH_CODE = PRIOR SUP_MCH_CODE
             AND PRIOR MCH_CODE_LEVEL != 'L:2'
    /
    
    MCH_CODE                  SUP_MCH_CODE              MCH_CODE_LEVEL
    ------------------------- ------------------------- --------------------
    AAA                       A                         L:2
    
    
    SQL>
    

    SY.

    Arif2018
  • Hub Tijhuis
    Hub Tijhuis Member Posts: 135 Silver Badge
    Accepted Answer

    You did not insert the real top-level parents 'A' and 'B' . So you don't have any level 1 records.

    You did add the level yourself, but you can omit it and leave it to oracle.

    You could define the following function .


    create or replace function get_ancestor_object(p_mch_code varchar2, p_lvl number) return varchar2

    is

     v_mch_code test_object.mch_code%TYPE;

    begin

     begin

    with h as -- hierarchy from top parent to child to get the level of hierarchy

    (

    --select mch_code, sup_mch_code,mch_code_level, level lvl , sys_connect_by_path(mch_code,'~') pth , connect_by_root mch_code p from test_object connect by sup_mch_code =  prior mch_code

    select mch_code, level lvl ,   connect_by_root mch_code p from test_object connect by sup_mch_code =  prior mch_code

    start with sup_mch_code not in (select mch_code from test_object) -- to select the top parent records

    )

    select (select mch_code from h h2 where h2.p = h1.p and h2.lvl + 1 = p_lvl) into v_mch_code from h h1 where h1.mch_code = p_mch_code;

     exception when no_data_found then null;

     end;

    return v_mch_code;

    end;

    -- to run test

    select mch_code, mch_code_level

    , get_ancestor_object(mch_code,1) l1

    , get_ancestor_object(mch_code,2) l2

    , get_ancestor_object(mch_code,3) l3

    , get_ancestor_object(mch_code,4) l4

    , get_ancestor_object(mch_code,5) l5

    , get_ancestor_object(mch_code,6) l6

    from test_object order by 1

    -- if you want to avoid functions you can start with something like

    with h as

    (

    select mch_code, sup_mch_code,mch_code_level, level lvl , sys_connect_by_path(mch_code,'~') pth , connect_by_root mch_code p from test_object connect by sup_mch_code =  prior mch_code

    start with sup_mch_code not in (select mch_code from test_object)

    )

    select h1.* , (select mch_code from h h2 where h2.p = h1.p and h2.lvl = 2) from h h1

    Arif2018
  • Arif2018
    Arif2018 Member Posts: 214 Red Ribbon

    Thanks @Solomon Yakobson and @Hub Tijhuis

    Amazed at how things can be done in hierarchy .