Your Location is: Home > Sql

hierarchic SQL in Oracle

From: Sweden View: 1730 Patrik Melichercik 

Question

I have huge table with lot of columns so for simplicity I pick up just the relevant ones.

There are components and moduls. Component can be used as final product but can be built in another component = modul. the same goes for moduls. One modul can be as final product or can be built in another component = modul.

in the real table there just numbers but for better understanding I use the relation component-modul so for example componentA + componentB = modulAB but componentA can be used as final items as well. Therfore in the table there 2 columns which identify if the component is using let's say as final item in a plant assembly_id - means the item is a modul , so must have some components (childs) in_assembly_id - means this item will be built in another item(s)/modul(s)

as mentioned above item can be final in that case both columns has -1 but at the same time this item can be built in another item(modul) so there is another row with column in_assembly_id <> -1 .in my example componentA

The same goes for items which are already moduls with some components and can be final item or will be built in another item(modul) indicating in the column in_assembly_id.

my goal is to find for particular item the modul item , doesn't matter that the item can be as final product as well

I can use join but that find just one level up.

select distinct c.item, c.in_assembly_id, modul, modul_id from items c ,lateral (select a.item modul,a.assembly_id modul_id from items a where a.assembly_id=c.in_assembly_id)
where c.item = 'componentA' and c.in_assembly_id <> -1;

ITEM        IN_ASSEMBLY_ID  MODUL   MODUL_ID
componentA  100             modulAC 100
componentA  50              modulAB 50

so to see the whole structure because modulAC is beeing built in modulACDE as well hierarchic query is needed to see the output like this 1.

ITEM        IN_ASSEMBLY_ID assembly_id type/level
componentA  -1             50          comp
modulAB     50             -1          modul
componentA  -1             100         comp
modulAC     100            -1          modul
modulAC     100            500         modul
modulACDE   500            -1          modul

the highest modul

    ITEM        IN_ASSEMBLY_ID assembly_id type/level
    componentA  -1             50          comp
    modulAB     50             -1          modul
    componentA  -1             100         comp
    modulACDE   500            -1          modul

I started with connect by clause but it doesn't work for me, I dont know why I get the modulDE

select distinct * from items
connect by nocycle in_assembly_id = prior assembly_id
start with item = 'componentA' and in_assembly_id <> '-1';

here is my data

create table items (
item_id number,
item varchar2(12),
assembly_id number,
in_assembly_id number
);

insert into items values ( 1 , 'componentA' , -1 , -1 );
insert into items values ( 2 , 'componentA' , -1 , 50 );
insert into items values ( 3 , 'componentB' , -1 , 50 );
insert into items values ( 4 , 'modulAB' , 50 , -1 );
insert into items values ( 5 , 'componentA' , -1 , 100 );
insert into items values ( 6 , 'componentC' , -1 , 100);
insert into items values ( 7 , 'modulAC' , 100 , -1 );
insert into items values ( 7 , 'modulAC' , 100 , 500 );
insert into items values ( 8 , 'componentD' , -1 , 200 );
insert into items values ( 9 , 'componentE' , -1 , 200 );
insert into items values ( 10 , 'modulDE' , 200 , 500 );
insert into items values ( 11 , 'modulACDE' , 500 , -1 );
insert into items values ( 12 , 'componentF' , -1 , -1 );
insert into items values ( 13 , 'componentG' , -1 , -1 );

Best answer

my goal is to find for particular item the modul item , doesn't matter that the item can be as final product as well

This will get all the modul for a componentA:

SELECT CONNECT_BY_ROOT( item_id ) AS root_item_id,
       CONNECT_BY_ROOT( item ) AS root_item,
       item_id,
       item,
       assembly_id,
       SYS_CONNECT_BY_PATH( item_id, ',' ) AS path_item_id,
       SYS_CONNECT_BY_PATH( item, ',' ) AS path_item
FROM   items
WHERE  assembly_id <> -1
START WITH
       item = 'componentA'
AND    ( assembly_id, in_assembly_id ) NOT IN ((-1,-1))
CONNECT BY NOCYCLE
       PRIOR in_assembly_id = assembly_id
ORDER SIBLINGS BY item, item_id;

Which outputs:

ROOT_ITEM_ID | ROOT_ITEM  | ITEM_ID | ITEM      | ASSEMBLY_ID | PATH_ITEM_ID | PATH_ITEM                             
-----------: | :--------- | ------: | :-------- | ----------: | :----------- | :-------------------------------------
           2 | componentA |       4 | modulAB   |          50 | ,2,4         | ,componentA,modulAB                   
           2 | componentA |       7 | modulAC   |         100 | ,2,4,5,7     | ,componentA,modulAB,componentA,modulAC
           2 | componentA |       7 | modulAC   |         100 | ,2,4,6,7     | ,componentA,modulAB,componentC,modulAC
           2 | componentA |      10 | modulDE   |         200 | ,2,4,8,10    | ,componentA,modulAB,componentD,modulDE
           2 | componentA |      10 | modulDE   |         200 | ,2,4,9,10    | ,componentA,modulAB,componentE,modulDE
           5 | componentA |       7 | modulAC   |         100 | ,5,7         | ,componentA,modulAC                   
           5 | componentA |      10 | modulDE   |         200 | ,5,7,8,10    | ,componentA,modulAC,componentD,modulDE
           5 | componentA |      10 | modulDE   |         200 | ,5,7,9,10    | ,componentA,modulAC,componentE,modulDE
           5 | componentA |       7 | modulAC   |         100 | ,5,7         | ,componentA,modulAC                   
           5 | componentA |      11 | modulACDE |         500 | ,5,7,11      | ,componentA,modulAC,modulACDE         

db<>fiddle here


Update

SELECT CONNECT_BY_ROOT( item_id ) AS root_item_id,
       CONNECT_BY_ROOT( item ) AS root_item,
       item_id,
       item,
       assembly_id,
       SYS_CONNECT_BY_PATH( item_id, ',' ) AS path_item_id,
       SYS_CONNECT_BY_PATH( item, ',' ) AS path_item
FROM   items
WHERE  assembly_id > -1
START WITH
       item = 'componentA'
AND    ( assembly_id, in_assembly_id ) NOT IN ((-1,-1))
CONNECT BY NOCYCLE
       PRIOR in_assembly_id = assembly_id
AND    assembly_id > -1
ORDER SIBLINGS BY item, item_id;

Outputs:

ROOT_ITEM_ID | ROOT_ITEM  | ITEM_ID | ITEM      | ASSEMBLY_ID | PATH_ITEM_ID | PATH_ITEM                    
-----------: | :--------- | ------: | :-------- | ----------: | :----------- | :----------------------------
           2 | componentA |       4 | modulAB   |          50 | ,2,4         | ,componentA,modulAB          
           5 | componentA |       7 | modulAC   |         100 | ,5,7         | ,componentA,modulAC          
           5 | componentA |       7 | modulAC   |         100 | ,5,7         | ,componentA,modulAC          
           5 | componentA |      11 | modulACDE |         500 | ,5,7,11      | ,componentA,modulAC,modulACDE

db<>fiddle here