select distinct
xbdlv.D1
,decode(xbdlv.q2,null,0,xbdlv.q2)*decode(sum(xwl.qty),null,0,sum(xwl.qty)) "qty_wef600"
from
x_bom_detail_level2_v xbdlv
,(select distinct item,qty from wip.xwp_wef600_l ) xwl
where qty_wef600 is not null系統提示
"qty_wef600":invalid identifier為什麼別名不能用在where 中???????這個where應該怎麼寫呢
select tt from(select sysdate tt from dual)
select *
from (select distinct
xbdlv.D1
,decode(xbdlv.q2,null,0,xbdlv.q2)*decode(sum(xwl.qty),null,0,sum(xwl.qty)) "qty_wef600"
from
x_bom_detail_level2_v xbdlv
,(select distinct item,qty from wip.xwp_wef600_l ) xwl
)
where qty_wef600 is not null
oracle解析器是先选择数据,然后拼接数据结果。你使用后面的的东西在前面使用肯定不行。。
放在外層也外這個錯呀,select
distinct *
from
(
select
distinct
xal.itemid --"領料id"
,xal.department_code
,xal.item --"領料料號"
,xal.name
,sum(qty ) "qty_ll"
from
wip.xwp_accessory_lyh xal where
--TO_CHAR(accessory_date , 'YYYYMMDD') between '20110201' and '20110213'
TO_CHAR(accessory_date , 'YYYYMMDD') BETWEEN '&f_date' AND '&s_date'
group by
xal.itemid
,xal.department_code
,xal.item
,xal.name
)accessory_lyh,(
select distinct
xbdlv.D1
,decode(xbdlv.q2,null,0,xbdlv.q2)*decode(sum(xwl.qty),null,0,sum(xwl.qty)) "qty_wef600"
from
x_bom_detail_level2_v xbdlv
,(select distinct item,qty from wip.xwp_wef600_l ) xwl
where xbdlv.SEGMENT1_MASTER=xwl.item(+)
AND xwl.qty IS NOT NULL group by
xbdlv.D1
,xbdlv.q2
)wef600_l where accessory_lyh.itemid=wef600_l.D1(+)
and qty_wef600 is not null
这个要去质问Oracle公司了