今天,wayne将Oracle 展BOM方式的一些问题分享出来,

其实,在1个月前做SKD时,我就发现这个问题,但未作Share,

难道最近我的Share意识淡薄了 ?!

 

PS.

 

Wayne’s Mail:

 

根據MDM CR PES中去執行這條SQL語句有問題,原因在於START WITH放的位置,他的位置先後執行出來展BOM的結果會不一致(錯誤會比正確的多展一些無關的BOM),所以以後在寫這樣的SQL語句中請小心,大家可以比較一下以下正確與錯誤執行的結果,錯誤的會莫名多出55.G0001.001這一階,他在此BOM中沒有任何母階;
 

Develop BOM

SELECT LEVEL, bprod, bchld, bqreq

  FROM mbm where bdeff<= 20040101 and bddis>=20040101 

CONNECT BY PRIOR bchld = bprod

START WITH bprod = ‘99.F0027.001′ and bdeff<= 20040101 and bddis>=20040101

 ORDER BY 1, 2, 3, 4
 
 
Error SQL Statement:
 SELECT distinct LEVEL, bprod, bchld, bqreq
  FROM mbm
  WHERE bid = ‘BM’
  AND bmwhs = ‘TG’
  AND bdeff <= 20040820
  AND bddis >= 20040820
  CONNECT BY PRIOR bchld = bprod
  START WITH bprod = ‘99.G0021.003′
  AND bid = ‘BM’
  AND bmwhs = ‘TG’
  AND bdeff <= 20040820
  AND bddis >= 20040820
  ORDER BY LEVEL, bprod, bchld
 
Right SQL Statement:
 SELECT distinct LEVEL, bprod, bchld, bqreq
  FROM mbm
  WHERE bid = ‘BM’
  AND bmwhs = ‘TG’
  AND bdeff <= 20040820
  AND bddis >= 20040820
  CONNECT BY PRIOR bchld = bprod
  AND bid = ‘BM’
  AND bmwhs = ‘TG’
  AND bdeff <= 20040820
  AND bddis >= 20040820
  START WITH bprod = ‘99.G0021.003′
  ORDER BY LEVEL, bprod, bchld
 
my liu:
Oracle的标准写法如下,Start With在Connect By前
      Start  WITH  condition
—->————————- > CONNECT  BY  condition


评论

评论已关闭.