试试这样select a.itemcode itemcode,a.itemdesc itemdesc ,nvl(sum(b.quantity_in),0) inv01 ,nvl(sum(b.quantity_out),0) inv02 from mrptmp a,( select itemcode,quantity_in=quantity,quantity_out=0 from inv01 where inv01date between '2005-1-1' and '2005-1-31' union all select itemcode,quantity_in=0,quantity_out=quantity from inv02 where inv02date between '2005-1-1' and '2005-1-31' )b where a.itemcode=b.itemcode(+) group by a.itemcode,a.itemdesc
樓上的這位﹐還是不行﹐我估計是那個'='號不行 ERROR 在行 5: ORA-00923: 無法在應該出現 FROM 關鍵字的位置上找到它
1、sqlserver对参数的赋值可以在一条上设多个值如: select itemcode=a.itemcode,itemdesc=a.itemdesc ,inv01=isnull(sum(b.quantity_in),0) ,inv02=isnull(sum(b.quantity_out),0).... 但是oracle要一条一条的设置: (1) select a.itemcode into itemcode from .... (2) select a.itemdesc into itemdesc from .... .... 2、isnull替换成nvl 3、left join ...on是iso标准语句mssqlserver和oracle差不多一样,oracle还有一种写法为: where a.itemcode=b.itemcode(+) 其它没什么了吧?!
select a.itemcode into itemcode from mrptmp a left out join( select itemcode,quantity_in=quantity,quantity_out=0 from inv01 where inv01date between '2005-1-1' and '2005-1-31' union all select itemcode,quantity_in=0,quantity_out=quantity from inv02 where inv02date between '2005-1-1' and '2005-1-31' )b on a.itemcode=b.itemcode group by a.itemcode,a.itemdesc;select a.itemdesc into itemdesc from mrptmp a left out join( select itemcode,quantity_in=quantity,quantity_out=0 from inv01 where inv01date between '2005-1-1' and '2005-1-31' union all select itemcode,quantity_in=0,quantity_out=quantity from inv02 where inv02date between '2005-1-1' and '2005-1-31' )b on a.itemcode=b.itemcode group by a.itemcode,a.itemdesc;select nvl(sum(b.quantity_in),0) into inv01 from mrptmp a left out join( select itemcode,quantity_in=quantity,quantity_out=0 from inv01 where inv01date between '2005-1-1' and '2005-1-31' union all select itemcode,quantity_in=0,quantity_out=quantity from inv02 where inv02date between '2005-1-1' and '2005-1-31' )b on a.itemcode=b.itemcode group by a.itemcode,a.itemdesc;select nvl(sum(b.quantity_out),0) into inv02 from mrptmp a left out join( select itemcode,quantity_in=quantity,quantity_out=0 from inv01 where inv01date between '2005-1-1' and '2005-1-31' union all select itemcode,quantity_in=0,quantity_out=quantity from inv02 where inv02date between '2005-1-1' and '2005-1-31' )b on a.itemcode=b.itemcode group by a.itemcode,a.itemdesc;
select a.itemcode itemcode,a.itemdesc itemdesc ,nvl(sum(b.quantity_in),0) inv01 ,nvl(sum(b.quantity_out),0) inv02 from mrptmp a, ( select itemcode,quantity quantity_in,0 quantity_out from inv01 where inv01date between '2005-1-1' and '2005-1-31' union all select itemcode,0,quantity from inv02 where inv02date between '2005-1-1' and '2005-1-31' )b where a.itemcode=b.itemcode(+) group by a.itemcode,a.itemdesc如果inv01date,inv02date是日期型 select a.itemcode itemcode,a.itemdesc itemdesc ,nvl(sum(b.quantity_in),0) inv01 ,nvl(sum(b.quantity_out),0) inv02 from mrptmp a, ( select itemcode,quantity quantity_in,0 quantity_out from inv01 where inv01date between to_date('2005-01-01','yyyy-mm-dd') and to_date('2005-01-31','yyyy-mm-dd') union all select itemcode,0,quantity from inv02 where inv02date between to_date('2005-01-01','yyyy-mm-dd') and to_date('2005-01-31','yyyy-mm-dd') )b where a.itemcode=b.itemcode(+) group by a.itemcode,a.itemdesc
,nvl(sum(b.quantity_in),0) inv01
,nvl(sum(b.quantity_out),0) inv02
from mrptmp a,(
select itemcode,quantity_in=quantity,quantity_out=0
from inv01
where inv01date between '2005-1-1' and '2005-1-31'
union all
select itemcode,quantity_in=0,quantity_out=quantity
from inv02
where inv02date between '2005-1-1' and '2005-1-31'
)b
where a.itemcode=b.itemcode(+)
group by a.itemcode,a.itemdesc
ORA-00923: 無法在應該出現 FROM 關鍵字的位置上找到它
select itemcode=a.itemcode,itemdesc=a.itemdesc
,inv01=isnull(sum(b.quantity_in),0)
,inv02=isnull(sum(b.quantity_out),0)....
但是oracle要一条一条的设置:
(1) select a.itemcode into itemcode from ....
(2) select a.itemdesc into itemdesc from ....
....
2、isnull替换成nvl
3、left join ...on是iso标准语句mssqlserver和oracle差不多一样,oracle还有一种写法为:
where a.itemcode=b.itemcode(+)
其它没什么了吧?!
from mrptmp a
left out join(
select itemcode,quantity_in=quantity,quantity_out=0
from inv01
where inv01date between '2005-1-1' and '2005-1-31'
union all
select itemcode,quantity_in=0,quantity_out=quantity
from inv02
where inv02date between '2005-1-1' and '2005-1-31'
)b on a.itemcode=b.itemcode
group by a.itemcode,a.itemdesc;select a.itemdesc into itemdesc
from mrptmp a
left out join(
select itemcode,quantity_in=quantity,quantity_out=0
from inv01
where inv01date between '2005-1-1' and '2005-1-31'
union all
select itemcode,quantity_in=0,quantity_out=quantity
from inv02
where inv02date between '2005-1-1' and '2005-1-31'
)b on a.itemcode=b.itemcode
group by a.itemcode,a.itemdesc;select nvl(sum(b.quantity_in),0) into inv01
from mrptmp a
left out join(
select itemcode,quantity_in=quantity,quantity_out=0
from inv01
where inv01date between '2005-1-1' and '2005-1-31'
union all
select itemcode,quantity_in=0,quantity_out=quantity
from inv02
where inv02date between '2005-1-1' and '2005-1-31'
)b on a.itemcode=b.itemcode
group by a.itemcode,a.itemdesc;select nvl(sum(b.quantity_out),0) into inv02
from mrptmp a
left out join(
select itemcode,quantity_in=quantity,quantity_out=0
from inv01
where inv01date between '2005-1-1' and '2005-1-31'
union all
select itemcode,quantity_in=0,quantity_out=quantity
from inv02
where inv02date between '2005-1-1' and '2005-1-31'
)b on a.itemcode=b.itemcode
group by a.itemcode,a.itemdesc;
,nvl(sum(b.quantity_in),0) inv01
,nvl(sum(b.quantity_out),0) inv02
from mrptmp a,
(
select itemcode,quantity quantity_in,0 quantity_out
from inv01
where inv01date between '2005-1-1' and '2005-1-31'
union all
select itemcode,0,quantity
from inv02
where inv02date between '2005-1-1' and '2005-1-31'
)b
where a.itemcode=b.itemcode(+)
group by a.itemcode,a.itemdesc如果inv01date,inv02date是日期型
select a.itemcode itemcode,a.itemdesc itemdesc
,nvl(sum(b.quantity_in),0) inv01
,nvl(sum(b.quantity_out),0) inv02
from mrptmp a,
(
select itemcode,quantity quantity_in,0 quantity_out
from inv01
where inv01date between to_date('2005-01-01','yyyy-mm-dd') and
to_date('2005-01-31','yyyy-mm-dd')
union all
select itemcode,0,quantity
from inv02
where inv02date between to_date('2005-01-01','yyyy-mm-dd') and
to_date('2005-01-31','yyyy-mm-dd')
)b
where a.itemcode=b.itemcode(+)
group by a.itemcode,a.itemdesc