How in the BOM according to find out the top of the bottom material components-CodePudding

BOM table

Demand results:

How to find the top 882, according to the bottom material in the BOM components (four components), four lines need to display

CodePudding user response:

CONNECT_BY_ROOT

CodePudding user response:

reference 1st floor yaiger response:

CONNECT_BY_ROOT

Can you point in detail

CodePudding user response:

Select *
The from (select level as mat_level, t. components, connect_by_root t. material
From the table t
Start with the material=882
Connect by material=the prior t. t. components)
Where mat_level=3;

Suggest their check grammar, is not difficult, learned can master more skills

CodePudding user response:

Written on the note, MAT_LEVEL=3 is not very precise, it is best to take MAT_LEVEL biggest, I lazy

CodePudding user response:

For table storage structure, 882 is the root node, the four is a leaf node,
"At the top of the component" if refers to all the leaf nodes,

 select * 
The from (select level as mat_level, 
T. components, 
Connect_by_root t. material, 
Connect_by_isleaf 
From the table t 
Start with the material=882 
Connect by material=the prior t. t. components) 
Where connect_by_isleaf=1

If refers to the depth of the highest node

 select * 
The from (select level as mat_level, 
T. components, 
Connect_by_root t. material, 
Connect_by_isleaf, 
Max (level) over (MLV) 
From the table t 
Start with the material=882 
Connect by material=the prior t. t. components) 
Where connect_by_isleaf=1 
And MLV=mat_level

CodePudding user response:

From the perspective of the data of display, the upstairs also can understand, so the problem is this is just a part of the data, from the point of business on the BOM structure, components, there will be other material so connect_by_isleaf inappropriate