急﹐這個語句在oracle下怎么寫? 在線等

[NICOJOHN]

select itemcode=a.itemcode,itemdesc=a.itemdesc
,inv01=isnull(sum(b.quantity_in),0)
,inv02=isnull(sum(b.quantity_out),0)
from mrptmp a
left 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


以上是在SQL中的語句﹐在oracle下提示﹕ oracle-00923 無法在應該出現FROM關鍵字的位置上找到它

[yxxx]

不懂sqlserver:(

[yxxx]

这样行不行？
...
from mrptmp a,(...) b
where a.itemcode=b.itemcode(+)
...
还有isnull是不是该换成nvl？

[yxxx]

试试这样

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

[NICOJOHN]

樓上的這位﹐還是不行﹐我估計是那個'='號不行

ERROR 在行 5:
ORA-00923: 無法在應該出現 FROM 關鍵字的位置上找到它

[ashzs]

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(+)
其它没什么了吧？！

[yxxx]

嗯，＝不能这样用的

[NICOJOHN]

能不能帮忙写出来？？

[ashzs]

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;

[ORARichard]

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

[bisliu]

sql中的select 中的=表示别名的意思？

[NICOJOHN]

樓上ORARichard(没钱的日子......) 正解﹐結帖給分!!!

謝謝大家