BOM层级查询嵌套子查询/外层查询时结果异常求助

架构老林

2026-6-13

问题描述

初始的Oracle CONNECT BY查询（用于BOM层级展开）运行正常，但尝试将其放入WITH子句（CTE）并添加外层过滤条件时，结果异常。初始查询如下：

SELECT 
    LEVEL BOM_LEVEL,
    CONNECT_BY_ISCYCLE IS_CYCLE,
    Q_BOM.COMPONENT_NUM ITEM,
    Q_BOM.COMPONENT_DESCR ITEM_DESCR,
    Q_BOM.QTY,
    Q_BOM.UOM,
    LEVEL - 1 PARENT_SELECTED,
    Q_BOM.ASSEMBLY_NUM PARENT_BOM,
    CONNECT_BY_ROOT Q_BOM.ASSEMBLY_NUM ROOT_ASSEMBLY,
    SUBSTR(SYS_CONNECT_BY_PATH(Q_BOM.ASSEMBLY_NUM, ' <-- '), 5) ASSEMBLY_PATH,
    Q_BOM.SEQUENCE it_seq,
    Q_BOM.ALTERNATE_BOM_DESIGNATOR
FROM (
    SELECT 
        MB1.ITEM_NUMBER ASSEMBLY_NUM,
        MB1.INVENTORY_ITEM_ID ASSEMBLY_ITEM_ID,
        MB2.ITEM_NUMBER COMPONENT_NUM,
        MB2.DESCRIPTION COMPONENT_DESCR,
        BC.COMPONENT_QUANTITY QTY,
        MB2.PRIMARY_UOM_CODE UOM,
        BC.item_num SEQUENCE,
        BS.ALTERNATE_BOM_DESIGNATOR
    FROM 
        EGP_STRUCTURES_B BS,
        EGP_SYSTEM_ITEMS_B MB1,
        EGP_COMPONENTS_B BC,
        EGP_SYSTEM_ITEMS_VL MB2,
        INV_ORG_PARAMETERS IOP
    WHERE 
        1 = 1
        AND TO_NUMBER(BS.PK1_VALUE) = MB1.INVENTORY_ITEM_ID
        AND TO_NUMBER(BS.PK2_VALUE) = MB1.ORGANIZATION_ID
        AND BC.BILL_SEQUENCE_ID = BS.COMMON_BILL_SEQUENCE_ID
        AND TO_NUMBER(BC.PK1_VALUE) = MB2.INVENTORY_ITEM_ID
        AND TO_NUMBER(BC.PK2_VALUE) = MB2.ORGANIZATION_ID
        AND MB1.ORGANIZATION_ID = IOP.ORGANIZATION_ID
        AND IOP.ORGANIZATION_CODE = :P_ORGANIZATION
        AND BS.EFFECTIVITY_CONTROL = 1
        AND BC.EFFECTIVITY_DATE <= SYSDATE
        AND NVL(BC.DISABLE_DATE, SYSDATE + 1) > SYSDATE
) Q_BOM
START WITH Q_BOM.ASSEMBLY_NUM >= :P_from_BOM_ITEM
    AND Q_BOM.ASSEMBLY_NUM <= :P_to_BOM_ITEM 
CONNECT BY NOCYCLE PRIOR Q_BOM.COMPONENT_NUM = Q_BOM.ASSEMBLY_NUM
ORDER SIBLINGS BY Q_BOM.ASSEMBLY_NUM, Q_BOM.SEQUENCE

尝试的错误写法（WITH子句）：

With levelbom (SELECT 
        LEVEL BOM_LEVEL,
        CONNECT_BY_ISCYCLE IS_CYCLE,
        Q_BOM.COMPONENT_NUM ITEM,
        Q_BOM.COMPONENT_DESCR ITEM_DESCR,
        Q_BOM.QTY,
        Q_BOM.UOM,
        LEVEL - 1 PARENT_SELECTED,
        Q_BOM.ASSEMBLY_NUM PARENT_BOM,
        CONNECT_BY_ROOT Q_BOM.ASSEMBLY_NUM ROOT_ASSEMBLY,
        SUBSTR(SYS_CONNECT_BY_PATH(Q_BOM.ASSEMBLY_NUM, ' <-- '), 5) ASSEMBLY_PATH,
        Q_BOM.SEQUENCE it_seq,
        Q_BOM.ALTERNATE_BOM_DESIGNATOR
    FROM (
        SELECT 
            MB1.ITEM_NUMBER ASSEMBLY_NUM,
            MB1.INVENTORY_ITEM_ID ASSEMBLY_ITEM_ID,
            MB2.ITEM_NUMBER COMPONENT_NUM,
            MB2.DESCRIPTION COMPONENT_DESCR,
            BC.COMPONENT_QUANTITY QTY,
            MB2.PRIMARY_UOM_CODE UOM,
            BC.item_num SEQUENCE,
            BS.ALTERNATE_BOM_DESIGNATOR
        FROM 
            EGP_STRUCTURES_B BS,
            EGP_SYSTEM_ITEMS_B MB1,
            EGP_COMPONENTS_B BC,
            EGP_SYSTEM_ITEMS_VL MB2,
            INV_ORG_PARAMETERS IOP
        WHERE 
            1 = 1
            AND TO_NUMBER(BS.PK1_VALUE) = MB1.INVENTORY_ITEM_ID
            AND TO_NUMBER(BS.PK2_VALUE) = MB1.ORGANIZATION_ID
            AND BC.BILL_SEQUENCE_ID = BS.COMMON_BILL_SEQUENCE_ID
            AND TO_NUMBER(BC.PK1_VALUE) = MB2.INVENTORY_ITEM_ID
            AND TO_NUMBER(BC.PK2_VALUE) = MB2.ORGANIZATION_ID
            AND MB1.ORGANIZATION_ID = IOP.ORGANIZATION_ID
            AND IOP.ORGANIZATION_CODE = :P_ORGANIZATION
            AND BS.EFFECTIVITY_CONTROL = 1
            AND BC.EFFECTIVITY_DATE <= SYSDATE
            AND NVL(BC.DISABLE_DATE, SYSDATE + 1) > SYSDATE
    ) Q_BOM
    START WITH Q_BOM.ASSEMBLY_NUM >= :P_from_BOM_ITEM
        AND Q_BOM.ASSEMBLY_NUM <= :P_to_BOM_ITEM 
    CONNECT BY NOCYCLE PRIOR Q_BOM.COMPONENT_NUM = Q_BOM.ASSEMBLY_NUM
    ORDER SIBLINGS BY Q_BOM.ASSEMBLY_NUM, Q_BOM.SEQUENCE
)

select * from
levelbom 
where levelbom.ALTERNATE_BOM_DESIGNATOR = 'V0'

解决方案与技巧

1. 修正WITH子句语法错误

Oracle的CTE（WITH子句）定义必须使用AS关键字，原写法遗漏了AS，这是导致异常的直接原因。另外，ORDER SIBLINGS BY应放到外层查询中，因为CTE内部的排序无法保证外层结果的层级顺序，还可能干扰层级展开逻辑。

正确的WITH子句写法：

WITH levelbom AS (
    SELECT 
        LEVEL BOM_LEVEL,
        CONNECT_BY_ISCYCLE IS_CYCLE,
        Q_BOM.COMPONENT_NUM ITEM,
        Q_BOM.COMPONENT_DESCR ITEM_DESCR,
        Q_BOM.QTY,
        Q_BOM.UOM,
        LEVEL - 1 PARENT_SELECTED,
        Q_BOM.ASSEMBLY_NUM PARENT_BOM,
        CONNECT_BY_ROOT Q_BOM.ASSEMBLY_NUM ROOT_ASSEMBLY,
        SUBSTR(SYS_CONNECT_BY_PATH(Q_BOM.ASSEMBLY_NUM, ' <-- '), 5) ASSEMBLY_PATH,
        Q_BOM.SEQUENCE it_seq,
        Q_BOM.ALTERNATE_BOM_DESIGNATOR
    FROM (
        SELECT 
            MB1.ITEM_NUMBER ASSEMBLY_NUM,
            MB1.INVENTORY_ITEM_ID ASSEMBLY_ITEM_ID,
            MB2.ITEM_NUMBER COMPONENT_NUM,
            MB2.DESCRIPTION COMPONENT_DESCR,
            BC.COMPONENT_QUANTITY QTY,
            MB2.PRIMARY_UOM_CODE UOM,
            BC.item_num SEQUENCE,
            BS.ALTERNATE_BOM_DESIGNATOR
        FROM 
            EGP_STRUCTURES_B BS,
            EGP_SYSTEM_ITEMS_B MB1,
            EGP_COMPONENTS_B BC,
            EGP_SYSTEM_ITEMS_VL MB2,
            INV_ORG_PARAMETERS IOP
        WHERE 
            1 = 1
            AND TO_NUMBER(BS.PK1_VALUE) = MB1.INVENTORY_ITEM_ID
            AND TO_NUMBER(BS.PK2_VALUE) = MB1.ORGANIZATION_ID
            AND BC.BILL_SEQUENCE_ID = BS.COMMON_BILL_SEQUENCE_ID
            AND TO_NUMBER(BC.PK1_VALUE) = MB2.INVENTORY_ITEM_ID
            AND TO_NUMBER(BC.PK2_VALUE) = MB2.ORGANIZATION_ID
            AND MB1.ORGANIZATION_ID = IOP.ORGANIZATION_ID
            AND IOP.ORGANIZATION_CODE = :P_ORGANIZATION
            AND BS.EFFECTIVITY_CONTROL = 1
            AND BC.EFFECTIVITY_DATE <= SYSDATE
            AND NVL(BC.DISABLE_DATE, SYSDATE + 1) > SYSDATE
    ) Q_BOM
    START WITH Q_BOM.ASSEMBLY_NUM >= :P_from_BOM_ITEM
        AND Q_BOM.ASSEMBLY_NUM <= :P_to_BOM_ITEM 
    CONNECT BY NOCYCLE PRIOR Q_BOM.COMPONENT_NUM = Q_BOM.ASSEMBLY_NUM
)
SELECT *
FROM levelbom 
WHERE levelbom.ALTERNATE_BOM_DESIGNATOR = 'V0'
ORDER SIBLINGS BY PARENT_BOM, it_seq;

2. 普通子查询写法

如果不需要CTE，可直接将原查询作为子查询嵌套在外层过滤中：

SELECT *
FROM (
    SELECT 
        LEVEL BOM_LEVEL,
        CONNECT_BY_ISCYCLE IS_CYCLE,
        Q_BOM.COMPONENT_NUM ITEM,
        Q_BOM.COMPONENT_DESCR ITEM_DESCR,
        Q_BOM.QTY,
        Q_BOM.UOM,
        LEVEL - 1 PARENT_SELECTED,
        Q_BOM.ASSEMBLY_NUM PARENT_BOM,
        CONNECT_BY_ROOT Q_BOM.ASSEMBLY_NUM ROOT_ASSEMBLY,
        SUBSTR(SYS_CONNECT_BY_PATH(Q_BOM.ASSEMBLY_NUM, ' <-- '), 5) ASSEMBLY_PATH,
        Q_BOM.SEQUENCE it_seq,
        Q_BOM.ALTERNATE_BOM_DESIGNATOR
    FROM (
        SELECT 
            MB1.ITEM_NUMBER ASSEMBLY_NUM,
            MB1.INVENTORY_ITEM_ID ASSEMBLY_ITEM_ID,
            MB2.ITEM_NUMBER COMPONENT_NUM,
            MB2.DESCRIPTION COMPONENT_DESCR,
            BC.COMPONENT_QUANTITY QTY,
            MB2.PRIMARY_UOM_CODE UOM,
            BC.item_num SEQUENCE,
            BS.ALTERNATE_BOM_DESIGNATOR
        FROM 
            EGP_STRUCTURES_B BS,
            EGP_SYSTEM_ITEMS_B MB1,
            EGP_COMPONENTS_B BC,
            EGP_SYSTEM_ITEMS_VL MB2,
            INV_ORG_PARAMETERS IOP
        WHERE 
            1 = 1
            AND TO_NUMBER(BS.PK1_VALUE) = MB1.INVENTORY_ITEM_ID
            AND TO_NUMBER(BS.PK2_VALUE) = MB1.ORGANIZATION_ID
            AND BC.BILL_SEQUENCE_ID = BS.COMMON_BILL_SEQUENCE_ID
            AND TO_NUMBER(BC.PK1_VALUE) = MB2.INVENTORY_ITEM_ID
            AND TO_NUMBER(BC.PK2_VALUE) = MB2.ORGANIZATION_ID
            AND MB1.ORGANIZATION_ID = IOP.ORGANIZATION_ID
            AND IOP.ORGANIZATION_CODE = :P_ORGANIZATION
            AND BS.EFFECTIVITY_CONTROL = 1
            AND BC.EFFECTIVITY_DATE <= SYSDATE
            AND NVL(BC.DISABLE_DATE, SYSDATE + 1) > SYSDATE
    ) Q_BOM
    START WITH Q_BOM.ASSEMBLY_NUM >= :P_from_BOM_ITEM
        AND Q_BOM.ASSEMBLY_NUM <= :P_to_BOM_ITEM 
    CONNECT BY NOCYCLE PRIOR Q_BOM.COMPONENT_NUM = Q_BOM.ASSEMBLY_NUM
) t
WHERE t.ALTERNATE_BOM_DESIGNATOR = 'V0'
ORDER SIBLINGS BY t.PARENT_BOM, t.it_seq;

3. 关键技巧

CTE语法规范：Oracle中WITH子句必须遵循WITH alias AS (subquery)格式，遗漏AS会触发语法错误。
排序位置正确：ORDER SIBLINGS BY是针对CONNECT BY层级结果的专用排序，需放到最终外层查询中，避免在CTE或内层子查询中使用，否则会破坏层级顺序。

过滤逻辑优化：如果过滤条件（如ALTERNATE_BOM_DESIGNATOR = 'V0'）针对原始BOM数据，可直接移到最内层子查询，减少层级展开的数据量，提升查询性能：

SELECT 
    -- 字段列表
FROM (
    SELECT 
        -- 内层字段
        BS.ALTERNATE_BOM_DESIGNATOR
    FROM 
        -- 表列表
    WHERE 
        -- 原有条件
        AND BS.ALTERNATE_BOM_DESIGNATOR = 'V0' -- 提前过滤
) Q_BOM
-- START WITH 和 CONNECT BY 逻辑
ORDER SIBLINGS BY ...;

避免层级干扰：在外层过滤层级计算后的字段（如BOM_LEVEL = 2）时，需明确业务需求，确保过滤不会破坏完整的层级结构。

内容的提问来源于stack exchange，提问作者CanaConstance

BOM层级查询嵌套子查询/外层查询时结果异常求助

1. 修正WITH子句语法错误 #

2. 普通子查询写法 #

3. 关键技巧 #

1. 修正WITH子句语法错误

2. 普通子查询写法

3. 关键技巧