Oracle BOM query returns disabled assemblies if any of its components are not

Question

I have this more or less common query to get an exploded BOM with the path to the child:

select distinct
       LEVEL "level"
       ,sys_connect_by_path(msib.segment1, '|') as "PATH"
       ,msib2.segment1 as "CHILD ITEM"
       ,msib2.description
       ,bic.component_quantity
       ,TO_DATE(bic.IMPLEMENTATION_DATE) IMPLEMENTATION
       ,TO_DATE(bic.DISABLE_DATE)DISABLED
       ,(SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS WHERE ORGANIZATION_ID = BIC.PK2_VALUE) ORGANIZATION
       ,bom.organization_id 
       ,BIC.ITEM_NUM SECUENCE
       ,bic.component_item_id CHILD_INVENTORY_ID
       , msib2.primary_uom_code UOM
from   bom.bom_components_b bic
       ,bom.bom_structures_b bom
       ,inv.mtl_system_items_b msib
       ,inv.mtl_system_items_b msib2
where  1=1
       and bic.bill_sequence_id = bom.bill_sequence_id
       and bom.assembly_item_id = msib.inventory_item_id
       and bom.organization_id = msib.organization_id
       and bic.component_item_id = msib2.inventory_item_id
       and bom.organization_id = msib2.organization_id
       and bom.organization_id IN (1269)
       and bom.alternate_bom_designator is NULL
       AND bic.DISABLE_DATE IS NULL <---------------- !!!LOOK HERE!!!
       connect by nocycle prior bic.codmponent_item_id = msib.inventory_item_id

D = the item has disabled date

--- EXAMPLE 0
FATHER 
   CHILD0
   CHILD1
   CHILD2
Should return:
 FATHER|CHILD0
 FATHER|CHILD1
 FATHER|CHILD2

--- EXAMPLE 1
FATHER 
   CHILD0 
   CHILD1 (D)
   CHILD2
Should return:
 FATHER|CHILD0
 FATHER|CHILD2

--- EXAMPLE 2
FATHER 
   CHILD0 (D)
   CHILD1 (D)
   CHILD2 (D)
Should return:
 FATHER (does not matter the assy is empty, this'd be corrected in the BOM)

--- EXAMPLE 3
FATHER (D)
   CHILD0 
   CHILD1
   CHILD2
Should return:
 NULL

With the current code if an assembly has at least one item active even if the father is not, it's loaded in the query:

--- EXAMPLE 4
FATHER (D)
   CHILD0 (D)
   CHILD1 (D)
   CHILD2
Returns:
 FATHER|CHILD0
 FATHER|CHILD1
 FATHER|CHILD2
Should return:
 NULL

--- EXAMPLE 5
FATHER (D)
   CHILD0
   CHILD1
   CHILD2
Returns:
 FATHER|CHILD0
 FATHER|CHILD1
 FATHER|CHILD2
Should return:
 NULL

How can I prevent this? This is, I need to retrieve items only if themselves or the father do NOT have disabled date. My BOMs have at most 9 levels. Thanks!

Edit: Made @mathguy modifications:

    select distinct
           LEVEL "level"
           ,sys_connect_by_path(msib.segment1, '|') as "PATH"
           ,msib2.segment1 as "CHILD ITEM"
           <SNIP>
    from   bom.bom_components_b bic
           ,bom.bom_structures_b bom
           ,inv.mtl_system_items_b msib
           ,inv.mtl_system_items_b msib2
    where  1=1
           and bic.bill_sequence_id = bom.bill_sequence_id
           and bom.assembly_item_id = msib.inventory_item_id
           and bom.organization_id = msib.organization_id
           and bic.component_item_id = msib2.inventory_item_id
           and bom.organization_id = msib2.organization_id
           and bom.organization_id IN (1269)
           and bom.alternate_bom_designator is NULL
    START WITH msib.segment1 = 'GRANDPA' AND bic.DISABLE_DATE IS NULL 
    CONNECT BY nocycle prior bic.component_item_id = msib.inventory_item_id AND bic.DISABLE_DATE IS NULL 
           ORDER BY LEVEL,PATH ASC

And the query returned a lot less rows (good!) but this test case is still failing:

--- EXAMPLE 4
GRANDPA
  FATHER (D)
    CHILD0
    CHILD1
    CHILD2
Should return:
  GRANDPA
Returns:
  GRANDPA|FATHER|CHILD0
  GRANDPA|FATHER|CHILD1
  GRANDPA|FATHER|CHILD2

Perhaps this is related at how deep the hierarchy is going?

Note - I changed one of your tags. Queries using the `CONNECT BY` mechanism are known as *hierarchical* queries. You had *recursive*-query instead; technically, that name is used for recursive `WITH` clause (also known as recursive subquery factoring or recursive CTE). — , Aug 20 '19 at 21:09

score 0 · Answer 1 · 2019-08-20T21:16:22.430

This condition:

bic.DISABLE_DATE IS NULL

should not be in the WHERE clause, but in the CONNECT BY clause. What's more, since the CONNECT BY conditions only apply to levels 2 and above, not to the initial rows, you also need a START WITH clause, and add the same condition there.

EDIT - and to explain why that is: Your hierarchical query has a join as its starting point. When using old, Oracle-style, comma syntax for joins, some of the conditions in the WHERE clause are seen by the parser as join conditions (and they are evaluated before the hierarchical step), while all the other conditions in the WHERE clause are evaluated only after the hierarchical step.

If instead the hierarchy was based on a single table (or on a subquery / inline view), then all the WHERE clause conditions would be evaluated only after the hierarchical step. Also, if you had used ANSI (SQL Standard) join syntax, all the WHERE clause conditions would be evaluated only after the hierarchical step; the conditions in the ON clause of the join, of course, would be evaluated before the hierarchical step.

thanks a lot! Implemented your suggestion and we are almost there. Can you take a look at my edit? Thanks — Gabriel, Aug 21 '19 at 14:33
@GabrielA.Zorrilla - I can't tell without some small sample of data for testing. I suspect this may have something to do with your joins (note that in your original question, in the common language description, there were NO joins). But I can't say for sure without seeing what the data looks like. — , Aug 21 '19 at 14:50

Oracle BOM query returns disabled assemblies if any of its components are not

1 Answers1