举例鼎捷T100递归查询BOM
递归过程条件没有加完整,需要根据实际改一改用。
select DISTINCT level L,
bmba001 主件,bmba003 元件,bmba011 元件数量,bmba010 单位,bmba012 主件底数,Z.imaa004 元件属性 ,X.imaa004 主件属性,X.imaa003
from bmba_t
left join imaa_t Z on Z.imaa001=bmba003 and Z.imaaent=15
left join imaa_t X on X.imaa001=bmba001 and X.imaaent=15
LEFT JOIN bmaa_t ON BMAA001=BMBA001 AND BMAAENT=BMBAENT AND BMBASITE=BMBASITE
WHERE BMBAENT=15 --据点 :ENT
AND BMBASITE='ALL' --:SITE
AND (bmba006 is null or bmba006< to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd')) ---失效日
AND BMAASTUS='Y' ---
-- start with bmba003= '12354534AA' connect by bmba003=prior bmba001 ----上展
start with bmba001= '12354534AA' connect by bmba001=prior bmba003 ----下展
and bmbaent=15 and bmbasite='ALL' and bmaaent=15 and bmaasite='ALL'
order by L