oracle 9i full join 问题

[ib915]

money_book 为视图
==============================================
select nvl(a.customer_no,b.customer_no),NVL(a.total,b.total) from
(select customer_no,sum(total_amount) as total from money_book where total_amount>0 group by customer_no) a
full join
(select customer_no,sum(total_amount) as total from money_book where total_amount<0 group by customer_no ) b
on a.customer_no=b.customer_no;

运行:

NVL(A.CUSTOMER_NO,B.CUSTOMER_NO) NVL(A.TOTAL,B.TOTAL)
-------------------------------- --------------------
7 200

已选择 1 行。
======================================



两表分开运行其中a
select customer_no,sum(total_amount) as total from money_book where total_amount>0 group by customer_no

CUSTOMER_NO TOTAL
----------- ----------
7 200

已选择 1 行。

其中 b
select customer_no,sum(total_amount) as total from money_book where total_amount<0 group by customer_no

CUSTOMER_NO TOTAL
----------- ----------
2 -480

已选择 1 行。


为什么full join 后只有一条纪录。我试过自己建两表a,b 数据一样，full join 后有两条纪录，
是否关视图问题？？压抑

[hippie1024]

你oracle什么版本的？
oracle的full join有几个bug的.

9.2.0.4.0后基本解决了。

升级吧

[ib915]

9.2.0.1.0

[hippie1024]

呵呵，9.2.0.1.0对FULL JOIN 存在BUG
升级到至少9.2.0.3.0以上就可以了

[ib915]

晕，在10g上没问题。

[hippie1024]

9.2.0.3.0都解了的问题，10g当然没问题了。

好像记得这个bug是在子查询中不能有group by

[ib915]

不知道是语句太复杂还是什么，
from money_book mb,customer cu
*
ERROR 位于第 12 行:
ORA-00600: internal error code, arguments: [qcscpqbc1], [2], [0], [], [], [], [], []

在9.2.0.1.0
竟然会出错


10g 中无问题

[ib915]

跟视图有关吧。现在我做了个测试，新建两表a,b
select * from a
NO VALUE
---------- ----------
7 200

已选择 1 行。

=================================

select * from b
NO VALUE
---------- ----------
2 -480

已选择 1 行。

======================================


select
NVL(a.no,b.no) no ,
NVL(a.value,b.value) value
from
(select a.no,sum(a.value) value from a group by a.no) a
full join
(select b.no,sum(b.value) value from b group by b.no) b
on a.no=b.no

NO VALUE
---------- ----------
7 200
2 -480

已选择2行。

正常