select a.bmb02 Seq,a.bmb03 PartsNo
,a.bmb06 MStdQpa
,nvl((select t.ccs03a from ccs_file t where t.ccs01=a.bmb03 and t.ccs02='1112'),0) MStdCost
,0 StdSum
,nvl((select sum(sfe16) from sfe_file s,sfb_file t where s.sfe01=t.sfb01 and t.sfb05=a.bmb01 and s.sfe07=a.bmb03),0) MFactQpa
,decode(nvl(b.bmb03,''),''
,
nvl((select t.ccs03a from ccs_file t where t.ccs01=a.bmb03 and t.ccs02='1112'),0)
,
nvl((select t.ccs03a from ccs_file t where t.ccs01=b.bmb03 and t.ccs02='1112'),0)*nvl((select sum(sfe16) from sfe_file s,sfb_file t where s.sfe01=t.sfb01 and t.sfb05=b.bmb01 and s.sfe07=b.bmb03),0)
) MFactCost
,0 FactSum
,b.bmb03 PartNo,b.bmb06 PStdQpa
,nvl((select sum(sfe16) from sfe_file s,sfb_file t where s.sfe01=t.sfb01 and t.sfb05=b.bmb01 and s.sfe07=b.bmb03),0) PFactQpa
,nvl((select t.ccs03a from ccs_file t where t.ccs01=b.bmb03 and t.ccs02='1112'),0) PCost
from bmb_file a,outer (bmb_file b)
where a.bmb03=b.bmb01
and a.bmb01='B11I12600A-ZT01'
and a.bmb03 NOT IN('B11I12600A-ZT02','B11I12600A-ZT01')
--含本階層五金件
select a.ccn03 Seq,a.ccn04 PartsNo
,a.ccn05 MStdQpa
,nvl((select t.ccs03a from ccs_file t where t.ccs01=a.ccn04 and t.ccs02='1112'),0) MStdCost
,0 StdSum
,nvl((select sum(sfe16) from sfe_file s,sfb_file t where s.sfe01=t.sfb01 and t.sfb05=a.ccn01 and s.sfe07=a.ccn04),0) MFactQpa
,decode(nvl(b.ccn04,''),''
,
nvl((select t.ccs03a from ccs_file t where t.ccs01=a.ccn04 and t.ccs02='1112'),0)
,
nvl((select t.ccs03a from ccs_file t where t.ccs01=b.ccn04 and t.ccs02='1112'),0)*nvl((select sum(sfe16) from sfe_file s,sfb_file t where s.sfe01=t.sfb01 and t.sfb05=b.ccn01 and s.sfe07=b.ccn04),0)
) MFactCost
,0 FactSum
,b.ccn04 PartNo,b.ccn05 PStdQpa
,nvl((select sum(sfe16) from sfe_file s,sfb_file t where s.sfe01=t.sfb01 and t.sfb05=b.ccn01 and s.sfe07=b.ccn04),0) PFactQpa
,nvl((select t.ccs03a from ccs_file t where t.ccs01=b.ccn04 and t.ccs02='1112'),0) PCost
from ccn_file a,outer (ccn_file b)
where a.ccn04=b.ccn01 and a.ccn02='1112' and b.ccn02='1112'
and a.ccn01='B11I12600A-ZT01'
and a.ccn04 NOT IN('B11I12600A-ZT02','B11I12600A-ZT01')
order by a.ccn04
--不含本階層五金件
select a.ccn03 Seq,a.ccn04 PartsNo
,a.ccn05 MStdQpa
,nvl((select t.ccs03a from ccs_file t where t.ccs01=a.ccn04 and t.ccs02='1112'),0) MStdCost
,0 StdSum
,nvl((select sum(sfe16) from sfe_file s,sfb_file t where s.sfe01=t.sfb01 and t.sfb05=a.ccn01 and s.sfe07=a.ccn04),0) MFactQpa
,decode(nvl(b.ccn04,''),''
,
0
,
nvl((select t.ccs03a from ccs_file t where t.ccs01=b.ccn04 and t.ccs02='1112'),0)*nvl((select sum(sfe16) from sfe_file s,sfb_file t where s.sfe01=t.sfb01 and t.sfb05=b.ccn01 and s.sfe07=b.ccn04),0)
) MFactCost
,0 FactSum
,b.ccn04 PartNo,b.ccn05 PStdQpa
,nvl((select sum(sfe16) from sfe_file s,sfb_file t where s.sfe01=t.sfb01 and t.sfb05=b.ccn01 and s.sfe07=b.ccn04),0) PFactQpa
,nvl((select t.ccs03a from ccs_file t where t.ccs01=b.ccn04 and t.ccs02='1112'),0) PCost
from ccn_file a,outer (ccn_file b)
where a.ccn04=b.ccn01 and a.ccn02='1112' and b.ccn02='1112'
and a.ccn01='B11I12600A-ZT01'
and a.ccn04 NOT IN('B11I12600A-ZT02','B11I12600A-ZT01')
--成功SQL
select a.ccn03 Seq,a.ccn04 PartsNo
,a.ccn05 MStdQpa
,nvl((select t.ccs03a from ccs_file t where t.ccs01=a.ccn04 and t.ccs02='1112'),0) MStdCost
,0 StdSum
,nvl((select sum(sfe16) from sfe_file s,sfb_file t where s.sfe01=t.sfb01 and t.sfb05=a.ccn01 and s.sfe07=a.ccn04),0) MFactQpa
,decode(nvl(b.ccn04,''),''
,
nvl((select t.ccs03a from ccs_file t where t.ccs01=a.ccn04 and t.ccs02='1112'),0)
,
nvl((select t.ccs03a from ccs_file t where t.ccs01=b.ccn04 and t.ccs02='1112'),0)*nvl((select sum(sfe16) from sfe_file s,sfb_file t where s.sfe01=t.sfb01 and t.sfb05=b.ccn01 and s.sfe07=b.ccn04),0)
) MFactCost
,0 FactSum
,b.ccn04 PartNo,b.ccn05 PStdQpa
,nvl((select sum(sfe16) from sfe_file s,sfb_file t where s.sfe01=t.sfb01 and t.sfb05=b.ccn01 and s.sfe07=b.ccn04),0) PFactQpa
,nvl((select t.ccs03a from ccs_file t where t.ccs01=b.ccn04 and t.ccs02='1112'),0) PCost
from ccn_file a,outer (ccn_file b)
where a.ccn04=b.ccn01 and a.ccn02='1112' and b.ccn02='1112'
and a.ccn01='B11I12600A-ZT03'
and a.ccn04 NOT IN('B11I12600A-ZT01','B11I12600A-ZT02')
order by a.ccn04