BOMツリー-子ノードから親ノードへのクエリー

1808 ワード

前編に続き、サブノードから親ノードへのクエリー文を書き直します.
 
子ノードから親ノードへのクエリー:
select level bom_level, -- 
       bom_trees.parent_item_id, -- 
       bom_trees.parent_item_code, -- 
       bom_trees.parent_item_name, -- 
       bom_trees.sub_item_id, -- 
       bom_trees.sub_item_code, -- 
       bom_trees.sub_item_name -- 

  from (select msi_sub.inventory_item_id sub_item_id,
               msi_sub.segment1          sub_item_code,
               msi_sub.description       sub_item_name,
               msi.inventory_item_id     parent_item_id,
               msi.segment1              parent_item_code,
               msi.description           parent_item_name,
               msi.organization_id       organization_id
        
          from bom_inventory_components bic,
               bom_bill_of_materials    bom,
               mtl_system_items_b       msi,
               mtl_system_items_b       msi_sub
         where msi.inventory_item_id = bom.assembly_item_id
           and msi.organization_id = bom.organization_id
           and msi.organization_id = 103
              
           and bom.bill_sequence_id = bic.bill_sequence_id
           and bom.alternate_bom_designator is null
           and bom.organization_id = 103
              
           and bic.disable_date is null
           and bic.component_item_id = msi_sub.inventory_item_id
              
           and msi_sub.organization_id = 103
        
        ) bom_trees --BOM 
connect by prior bom_trees.parent_item_id = bom_trees.sub_item_id
 start with bom_trees.sub_item_code = 'YBC.DA020-04' -- 
 order by bom_level;