You need to enable JavaScript to run this app. AS400服务器上DB2递归查询提取层级BOM的正确排序方法
优惠活动
大模型
产品
解决方案
定价
更多

AS400服务器上DB2递归查询提取层级BOM的正确排序方法

问题根因
  • 现有查询未显式定义全局排序规则,DB2 for i 返回的结果集默认按底层扫描顺序输出,完全不符合深度优先遍历的要求
  • 你已经通过SYS_CONNECT_BY_PATH生成的BOM_Path字段,本身就是从根节点到当前组件的完整链路,是实现深度优先排序的天然依据,无需改造原有递归查询逻辑,仅需增加正确的全局排序即可达到效果。
实现调整方案

需要将原本UNION ALL拼接的两个结果集包裹为内联视图,在外层增加全局排序规则——按BOM_Path字段升序排序,核心逻辑是:字符串逐位比对时,路径更短、更靠近根节点的记录会排在前面,同一父节点下的所有子分支会连续排列,直到该分支遍历完成后,才会回溯到下一个同级父节点,完全匹配深度优先的遍历规则。

调整后的完整可执行代码如下:

@set item = '10984'

WITH BOM (origin, PMPRNO, PMMTNO, BOM_Level, BOM_Path, IsCycle, IsLeaf) AS
    (SELECT CONNECT_BY_ROOT PMPRNO AS origin, PMPRNO, PMMTNO,
    LEVEL AS BOM_Level,
    SYS_CONNECT_BY_PATH(TRIM(PMMTNO), ' : ') BOM_Path,
    CONNECT_BY_ISCYCLE IsCycle,
    CONNECT_BY_ISLEAF IsLeaf
    FROM MPDMAT
    WHERE PMCONO = 405 AND PMFACI = 'M01' AND PMSTRT = 'STD'    
    START WITH PMPRNO = :item   
    CONNECT BY NOCYCLE PRIOR PMMTNO = PMPRNO)

SELECT * FROM (
    SELECT 0 AS BOM_Level, '' AS BOM_Path, MMITNO AS Part_Number, MMITDS AS Part_Name, 
        IFSUNO AS Supplier_Number, IDSUNM AS Supplier_Name, IFSITE AS Supplier_Part_Number
    FROM MITMAS
    LEFT OUTER JOIN MITVEN ON MMCONO = IFCONO AND MMITNO = IFITNO AND IFSUNO <> 'ZGA'
    LEFT OUTER JOIN CIDMAS ON MMCONO = IDCONO AND IDSUNO = IFSUNO
    WHERE MMCONO = 405
    AND MMITNO = :item

    UNION ALL

    SELECT BOM.BOM_Level, BOM_Path, BOM.PMMTNO AS Part_Number, MMITDS AS Part_Name, 
        IFSUNO AS Supplier_Number, IDSUNM AS Supplier_Name, IFSITE AS Supplier_Part_Number
    FROM BOM
    LEFT OUTER JOIN MITMAS ON MMCONO = 405 AND MMITNO = BOM.PMMTNO
    LEFT OUTER JOIN MITVEN ON IFCONO = MMCONO AND IFITNO = MMITNO AND IFSUNO <> 'ZGA' AND MMMABU = '2'
    LEFT OUTER JOIN CIDMAS ON MMCONO = IDCONO AND IDSUNO = IFSUNO
) AS FULL_BOM
ORDER BY BOM_Path ASC;
效果验证与兼容注意点
  • 排序后的结果顺序完全匹配预期:空BOM_Path的根节点会排在第一行,随后连续排列第一个一级组件的所有层级子节点(从该组件本身,到二级、三级……直到分支最末端),该分支遍历完成后,再输出下一个一级组件及其全部子节点
  • 禁止在UNION ALL的两个分支内部单独写ORDER BY,DB2会忽略分支内的排序逻辑,必须将全量结果集作为整体再加外层排序
  • 如果遇到服务器语言环境导致的字符串排序异常(比如中文物料编码排序不符合预期),可以将排序规则修改为ORDER BY CAST(BOM_Path AS VARBINARY(1000)) ASC,强制按路径的逐字节顺序排序,不受本地字符集规则影响
  • 由于所有路径都是通过同一个SYS_CONNECT_BY_PATH函数、使用统一的分隔符生成的,不存在分隔符长度不一致导致的路径错配问题,最高支持99层级的BOM路径长度完全可以覆盖

内容的提问来源于stack exchange,提问作者Andrewki

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.24 19:54:04