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
相关产品推荐
相关产品推荐

