递归查询T100的BOM

最新推荐文章于 2024-10-12 22:28:01 发布

python777_com

最新推荐文章于 2024-10-12 22:28:01 发布

阅读量388

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/weixin_43314137/article/details/126560621

版权

递归查询T100的BOM多料号快速呈现

 
 WITH t_bom0 AS (
 SELECT DISTINCT bmbaent AS bom_corp, bmbasite AS bom_site, bmba001 AS bom_no
 FROM bmba_t
 WHERE (1 = 1)
 AND bmbaent = 100 AND bmbasite = 'AAAAA' AND bmba001 = 'AAAAA'
 ), t_bom1 AS (
 SELECT LTRIM(TO_CHAR(C2.bmba009,'00000')) AS bom_level, C1.bom_corp, C1.bom_site, C1.bom_no, 
 C2.bmba001, C2.bmba003
 FROM t_bom0 C1
 INNER JOIN bmba_t C2 ON C2.bmbaent = C1.bom_corp AND C2.bmbasite = C1.bom_site AND C2.bmba001 = C1.bom_no
 INNER JOIN bmaa_t C3 ON C3.bmaaent = C1.bom_corp AND C3.bmaasite = C1.bom_site AND C3.bmaa001 = C1.bom_no AND C3.bmaastus = 'Y'
 WHERE (1 = 1)
 AND (C2.bmba006 IS NULL OR C2.bmba006 < TO_DATE(TO_CHAR(SYSDATE,'yyy-mm-dd'),'yyy-mm-dd'))
 ), t_bom2 AS (
 SELECT C1.bom_level||LTRIM(TO_CHAR(C2.bmba009,'00000')) AS bom_level, C1.bom_corp, C1.bom_site, C1.bom_no, 
 C2.bmba001, C2.bmba003
 FROM t_bom1 C1
 INNER JOIN bmba_t C2 ON C2.bmbaent = C1.bom_corp AND C2.bmbasite = C1.bom_site AND C2.bmba001 = C1.bmba003
 INNER JOIN bmaa_t C3 ON C3.bmaaent = C1.bom_corp AND C3.bmaasite = C1.bom_site AND C3.bmaa001 = C1.bmba003 AND C3.bmaastus = 'Y'
 WHERE (1 = 1)
 AND (C2.bmba006 IS NULL OR C2.bmba006 < TO_DATE(TO_CHAR(SYSDATE,'yyy-mm-dd'),'yyy-mm-dd'))
 ), t_bom3 AS (
 SELECT C1.bom_level||LTRIM(TO_CHAR(C2.bmba009,'00000')) AS bom_level, C1.bom_corp, C1.bom_site, C1.bom_no, 
 C2.bmba001, C2.bmba003
 FROM t_bom2 C1
 INNER JOIN bmba_t C2 ON C2.bmbaent = C1.bom_corp AND C2.bmbasite = C1.bom_site AND C2.bmba001 = C1.bmba003
 INNER JOIN bmaa_t C3 ON C3.bmaaent = C1.bom_corp AND C3.bmaasite = C1.bom_site AND C3.bmaa001 = C1.bmba003 AND C3.bmaastus = 'Y'
 WHERE (1 = 1)
 AND (C2.bmba006 IS NULL OR C2.bmba006 < TO_DATE(TO_CHAR(SYSDATE,'yyy-mm-dd'),'yyy-mm-dd'))
 ), t_bom4 AS (
 SELECT C1.bom_level||LTRIM(TO_CHAR(C2.bmba009,'00000')) AS bom_level, C1.bom_corp, C1.bom_site, C1.bom_no, 
 C2.bmba001, C2.bmba003
 FROM t_bom3 C1
 INNER JOIN bmba_t C2 ON C2.bmbaent = C1.bom_corp AND C2.bmbasite = C1.bom_site AND C2.bmba001 = C1.bmba003
 INNER JOIN bmaa_t C3 ON C3.bmaaent = C1.bom_corp AND C3.bmaasite = C1.bom_site AND C3.bmaa001 = C1.bmba003 AND C3.bmaastus = 'Y'
 WHERE (1 = 1)
 AND (C2.bmba006 IS NULL OR C2.bmba006 < TO_DATE(TO_CHAR(SYSDATE,'yyy-mm-dd'),'yyy-mm-dd'))
 ), t_bom5 AS (
 SELECT C1.bom_level||LTRIM(TO_CHAR(C2.bmba009,'00000')) AS bom_level, C1.bom_corp, C1.bom_site, C1.bom_no, 
 C2.bmba001, C2.bmba003
 FROM t_bom4 C1
 INNER JOIN bmba_t C2 ON C2.bmbaent = C1.bom_corp AND C2.bmbasite = C1.bom_site AND C2.bmba001 = C1.bmba003
 INNER JOIN bmaa_t C3 ON C3.bmaaent = C1.bom_corp AND C3.bmaasite = C1.bom_site AND C3.bmaa001 = C1.bmba003 AND C3.bmaastus = 'Y'
 WHERE (1 = 1)
 AND (C2.bmba006 IS NULL OR C2.bmba006 < TO_DATE(TO_CHAR(SYSDATE,'yyy-mm-dd'),'yyy-mm-dd'))
 ), t_bom6 AS (
 SELECT C1.bom_level||LTRIM(TO_CHAR(C2.bmba009,'00000')) AS bom_level, C1.bom_corp, C1.bom_site, C1.bom_no, 
 C2.bmba001, C2.bmba003
 FROM t_bom5 C1
 INNER JOIN bmba_t C2 ON C2.bmbaent = C1.bom_corp AND C2.bmbasite = C1.bom_site AND C2.bmba001 = C1.bmba003
 INNER JOIN bmaa_t C3 ON C3.bmaaent = C1.bom_corp AND C3.bmaasite = C1.bom_site AND C3.bmaa001 = C1.bmba003 AND C3.bmaastus = 'Y'
 WHERE (1 = 1)
 AND (C2.bmba006 IS NULL OR C2.bmba006 < TO_DATE(TO_CHAR(SYSDATE,'yyy-mm-dd'),'yyy-mm-dd'))
 ), t_bom7 AS (
 SELECT C1.bom_level||LTRIM(TO_CHAR(C2.bmba009,'00000')) AS bom_level, C1.bom_corp, C1.bom_site, C1.bom_no, 
 C2.bmba001, C2.bmba003
 FROM t_bom6 C1
 INNER JOIN bmba_t C2 ON C2.bmbaent = C1.bom_corp AND C2.bmbasite = C1.bom_site AND C2.bmba001 = C1.bmba003
 INNER JOIN bmaa_t C3 ON C3.bmaaent = C1.bom_corp AND C3.bmaasite = C1.bom_site AND C3.bmaa001 = C1.bmba003 AND C3.bmaastus = 'Y'
 WHERE (1 = 1)
 AND (C2.bmba006 IS NULL OR C2.bmba006 < TO_DATE(TO_CHAR(SYSDATE,'yyy-mm-dd'),'yyy-mm-dd'))
 ), t_bom8 AS (
 SELECT C1.bom_level||LTRIM(TO_CHAR(C2.bmba009,'00000')) AS bom_level, C1.bom_corp, C1.bom_site, C1.bom_no, 
 C2.bmba001, C2.bmba003
 FROM t_bom7 C1
 INNER JOIN bmba_t C2 ON C2.bmbaent = C1.bom_corp AND C2.bmbasite = C1.bom_site AND C2.bmba001 = C1.bmba003
 INNER JOIN bmaa_t C3 ON C3.bmaaent = C1.bom_corp AND C3.bmaasite = C1.bom_site AND C3.bmaa001 = C1.bmba003 AND C3.bmaastus = 'Y'
 WHERE (1 = 1)
 AND (C2.bmba006 IS NULL OR C2.bmba006 < TO_DATE(TO_CHAR(SYSDATE,'yyy-mm-dd'),'yyy-mm-dd'))
 ), t_bom9 AS (
 SELECT C1.bom_level||LTRIM(TO_CHAR(C2.bmba009,'00000')) AS bom_level, C1.bom_corp, C1.bom_site, C1.bom_no, 
 C2.bmba001, C2.bmba003
 FROM t_bom8 C1
 INNER JOIN bmba_t C2 ON C2.bmbaent = C1.bom_corp AND C2.bmbasite = C1.bom_site AND C2.bmba001 = C1.bmba003
 INNER JOIN bmaa_t C3 ON C3.bmaaent = C1.bom_corp AND C3.bmaasite = C1.bom_site AND C3.bmaa001 = C1.bmba003 AND C3.bmaastus = 'Y'
 WHERE (1 = 1)
 AND (C2.bmba006 IS NULL OR C2.bmba006 < TO_DATE(TO_CHAR(SYSDATE,'yyy-mm-dd'),'yyy-mm-dd'))
 ), t_bom90 AS (
 SELECT * FROM t_bom1 UNION ALL
 SELECT * FROM t_bom2 UNION ALL
 SELECT * FROM t_bom3 UNION ALL
 SELECT * FROM t_bom4 UNION ALL
 SELECT * FROM t_bom5 UNION ALL
 SELECT * FROM t_bom6 UNION ALL
 SELECT * FROM t_bom7 UNION ALL
 SELECT * FROM t_bom8 
 )
 SELECT *
 FROM t_bom90
 ORDER BY bom_level ASC