同事测试的时候,执行一个SELECT语句的时候出错了, 如图所示:
感觉上像是个Oracle的bug。
数据库版本:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
将 FULL JOIN改成LEFT JOIN没有出错, SQL语句
解决方法:
修改隐含参数:_optimizer_cost_based_transformation
alter system set "_optimizer_cost_based_transformation"=OFF;
参考:http://blog.csdn.net/zzvnzz/article/details/7728329
http://www.itpub.net/thread-1742242-2-1.html
感觉上像是个Oracle的bug。
数据库版本:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
将 FULL JOIN改成LEFT JOIN没有出错, SQL语句
点击(此处)折叠或打开
- SELECT ac.checkno,
- ac.compid,
- ac.busno,
- ac.wareid,
- ac.makeno,
- ac.stallno,
- ac.batid,
- (ac.invalidate) AS invalidate,
- ac.accountqty,
- CASE
- WHEN tr.checkqty IS NULL THEN
- 0
- ELSE
- tr.checkqty
- END AS checkqty,
- ((CASE
- WHEN tr.checkqty IS NULL THEN
- 0
- ELSE
- tr.checkqty
- END) - ac.accountqty) AS plqty,
- ac.purtax,
- ac.avgpurprice AS accavgpurprice,
- ac.avgpurprice,
- (ac.purprice) AS accpurprice,
- CASE
- WHEN (tr.purprice) IS NULL THEN
- 0
- ELSE
- tr.purprice
- END AS chepurprice,
- (ac.saleprice) AS accsaleprice,
- CASE
- WHEN (tr.saleprice) IS NULL THEN
- 0
- ELSE
- tr.saleprice
- END AS chesaleprice
- FROM t_check_account ac
- FULL JOIN (SELECT (h.checkno) AS checkno,
- wareid,
- MAX(makeno) makeno,
- batid,
- stallno,
- MAX(nvl(invalidate, '')) invalidate,
- SUM(nvl(checkqty, 0)) checkqty,
- MAX(purtax) purtax,
- MAX(purprice) purprice,
- MAX(saleprice) saleprice
- FROM t_check_track_h h, t_check_track_d d
- WHERE h.checkno = '150811001'
- AND h.checkno = d.checkno
- AND h.status = 1
- AND h.pageno = d.pageno
- GROUP BY h.checkno, wareid, batid, stallno) tr
- ON (ac.checkno = tr.checkno AND ac.wareid = tr.wareid AND
- ac.batid = tr.batid AND ac.stallno = tr.stallno)
- WHERE ac.checkno = '150811001'
解决方法:
修改隐含参数:_optimizer_cost_based_transformation
alter system set "_optimizer_cost_based_transformation"=OFF;
参考:http://blog.csdn.net/zzvnzz/article/details/7728329
http://www.itpub.net/thread-1742242-2-1.html
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8520577/viewspace-1812519/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8520577/viewspace-1812519/