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

Need Oracle query for a BOM IMPLOSION or BOM "WHERE-USED" on Oracle 10g

提问by Clint V.

Working with Oracle DB 10g.

使用 Oracle DB 10g。

I am tasked with writing an Oracle query for a BOM IMPLOSION commonly known as BOM "WHERE-USED." Essentially, given an item or part, I need to provide a list of parent items that contain that item or part, if any.

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

I have recently coded a BOM EXPLOSION using the following SQL which utilizes the START WITH and CONNECT BY syntax to create a heirarchy downward from a parent item. I found inspiration for the BOM EXPLOSION query at http://www.confluentminds.com/Trainings/SCM/Topic1.1_Ch1_Part5.html

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

Current BOM EXPLOSION code:

当前 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

Now, I need go from any child item and list all parent items that contain that child item.

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

I have searched for "oracle bom implosion" and "oracle bom where used" but nothing is obvious to me. The BOM IMPLOSION seems far less straightforward than the BOM EXPLOSION.

我搜索了“oracle bom 内爆”和“oracle bom where used”，但对我来说没有什么是显而易见的。BOM IMPLOSION 似乎远没有 BOM EXPLOSION 简单。

Any help or advice is greatly appreciated.

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

Clint Van Zee

克林特·范泽

EDIT 02-NOV-2011:

编辑 2011 年 11 月 2 日：

Yes, I want to traverse up a BOM heirarchy and list those items or components where a specified component is used in the bill.

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

Based on your answer, I looked into the "connect by" relationship and "start with." I think I have a way to do this. I may have been close to the answer without knowing it.

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

Craig, here is your mock-up with modifications added to prove this out. I also modified the "connect by" and "start with." It {should!} start with the child component and go "upwards" to list those models or components that "use" the specified starting component. To do this, I also removed the "prior" keyword.

克雷格，这是你的模型，添加了修改来证明这一点。我还修改了“连接方式”和“开始于”。它{应该！}从子组件开始并“向上”列出那些“使用”指定起始组件的模型或组件。为此，我还删除了“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

This produces the following result:

这会产生以下结果：

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

Looking at the mock data, component 5 is "used by" component2, component4, and topmodel2. So, this change seems to do what I intend. I had to add the dreaded "distinct" as it was traversing the same paths more than once.

查看模拟数据，组件 5 被 component2、component4 和 topmodel2“使用”。所以，这种变化似乎符合我的意图。我不得不添加可怕的“不同”，因为它不止一次穿过相同的路径。

I think this is getting me closer.

我认为这让我更接近了。

Here are the minimal changes to my BOM EXPLOSION code to do this in reverse:

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

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

When I apply this change to my SQL script and try it against the actual Oracle data, the CBO is reporting that the cost of searching just one component is out into the stratosphere. So, this method needs tuning.

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

采纳答案by Craig

I am not 100% sure what you are looking for, but it sounds like you are just wanting to climb up the tree instead of down. This is still possible using the hierarchical query, you just need to change your start with and connect by criteria.

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

So take a simple example:

所以举个简单的例子：

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;

That will give you the child paths starting from the top of the tree. To change this to start at a point in the tree and move upwards, you would simply replace the start with / connect by with something like:

这将为您提供从树顶开始的子路径。要将其更改为从树中的某个点开始并向上移动，您只需将开始替换为 / connect by ，如下所示：

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

Hopefully that helps. If not, if you could give an example of what you are wanting your output to look like it would be very beneficial.

希望这有帮助。如果没有，如果您可以举例说明您希望输出看起来像什么，那将非常有益。