需要 Oracle 查询 BOM IMPLOSION 或 BOM“WHERE-USED”在 Oracle 10g 上

Question

使用 Oracle DB 10g。

我的任务是为 BOM IMPLOSION（通常称为 BOM“WHERE-USED”）编写 Oracle 查询。本质上，给定一个项目或零件，我需要提供包含该项目或零件（如果有）的父项目列表。

我最近使用以下 SQL 编写了 BOM EXPLOSION，该 SQL 使用 START WITH 和 CONNECT BY 语法从父项向下创建层次结构。我在 http://www.confluenceminds.com/ 找到了 BOM EXPLOSION 查询的灵感Trainings/SCM/Topic1.1_Ch1_Part5.html

当前 BOM 爆炸代码：

/* BOM EXPLOSION */
select distinct
       level,
       sys_connect_by_path(msib.segment1, ' / ') as "PATH",
       msib2.segment1 as "CHILD ITEM AT LEVEL/PATH"
       /*bic.component_item_id,*/
       /*msib.inventory_item_id,*/
       /*msib2.inventory_item_id*/
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 bic.disable_date is null
       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 = #### /* organization id here */
       and bic.effectivity_date < sysdate
       and bom.alternate_bom_designator is null
start with msib.segment1 = '$$$$$$$$$$' /* top parent item here */
connect by nocycle prior bic.component_item_id = msib.inventory_item_id
order by level

现在，我需要从任何子项开始并列出所有父项包含该子项目的项目。

我搜索过“oracle bom 内爆”和“oracle bom 使用位置”，但对我来说没有什么是显而易见的。 BOM IMPLOSION 似乎远不如 BOM EXPLOSION 简单。

非常感谢任何帮助或建议。

Clint Van Zee

编辑 2011 年 11 月 2 日：

是的，我想遍历 BOM 层次结构并列出清单中使用指定组件的项目或组件。

根据您的回答，我研究了“连接”关系和“开始”。我想我有办法做到这一点。我可能在不知不觉中已经接近答案了。

克雷格，这是您的模型，经过修改以证明这一点。我还修改了“连接方式”和“开始方式”。它{应该！}从子组件开始，然后“向上”列出“使用”指定起始组件的模型或组件。为此，我还删除了“prior”关键字。

with data
as
(
    select 'topmodel1' id, 'component1' child_id from dual union all
    select 'topmodel1' id, 'component3' child_id from dual union all
    select 'component2' id, 'component5' child_id from dual union all
    select 'component3' id, 'component4' child_id from dual union all
    select 'component4' id, 'component5' child_id from dual union all
    select 'component5' id, null child_id from dual union all
    select 'topmodel2' id, 'component1' child_id from dual union all
    select 'topmodel2' id, 'component5' child_id from dual union all
    select 'component5' id, null child_id from dual

)
select distinct
       sys_connect_by_path(id, '/') path, child_id, level
from data
start with child_id = 'component5'
connect by id = child_id
order by level

这会产生以下结果：

PATH        CHILD_ID    LEVEL
----------- ----------  -----
/component2 component5  1
/component4 component5  1
/topmodel2  component5  1

查看模拟数据，组件 5 被组件 2、组件 4 和 topmodel2“使用”。所以，这个改变似乎达到了我的目的。我不得不添加可怕的“不同”，因为它不止一次穿过相同的路径。

我认为这让我更加接近。

以下是对我的 BOM EXPLOSION 代码进行的最小更改，以反向执行此操作：

START WITH msib.segment1 = '$$$$$$$$$$' /* child item here */
CONNECT BY nocycle msib.inventory_item_id = bic.component_item_id
ORDER BY level

当我将这一更改应用于 SQL 脚本并针对实际的 Oracle 数据进行尝试时，CBO 报告仅搜索一个组件的成本已计入平流层。所以，这个方法需要调整。

Answer 1

我不是 100% 确定你在寻找什么，但听起来你只是想爬上树而不是下来。使用分层查询仍然可以实现这一点，您只需更改起始条件并按条件连接即可。

举一个简单的例子：

with data
as
(
    select 1 id, 2 child_id from dual union all
    select 1 id, 3 child_id from dual union all
    select 2 id, 5 child_id from dual union all
    select 3 id, 4 child_id from dual union all
    select 4 id, 5 child_id from dual union all
    select 5 id, null child_id from dual
)
select sys_connect_by_path(id, '/') path, level
from data
start with id = 1
connect by prior child_id = id;

这将为您提供从树顶部开始的子路径。要将其更改为从树中的某个点开始并向上移动，您只需将开始替换为/连接为类似以下内容：

start with id = 4 -- or wherever in the tree you want to start and move upwards
connect by prior id = child_id;

希望这会有所帮助。如果没有，如果您能给出一个示例来说明您希望输出的内容，那将非常有益。