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 );
my goal is to find for particular item the
modulitem , 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
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