我有一个查询,如:
select nvl(nvl(sum(a.quantity),0)-nvl(cc.quantityCor,0),0)
from RCV_TRANSACTIONS a
LEFT JOIN (select c.shipment_line_id,c.oe_order_line_id,nvl(sum(c.quantity),0) quantityCor
from RCV_TRANSACTIONS c
where c.TRANSACTION_TYPE='CORRECT'
group by c.shipment_line_id,c.oe_order_line_id) cc on (a.shipment_line_id=cc.shipment_line_id and a.shipment_line_id=7085740)
where a.transaction_type='DELIVER'
and a.shipment_line_id=7085740
group by nvl(cc.quantityCor,0);
该查询运行正常,但不返回任何值。如果没有数量,我希望它返回0。我哪里出问题了?
GROUP BY
如果将所有行过滤掉,则带有a的聚合查询将不返回任何行。
与聚合查询不 GROUP BY
总是返回一行,即使全部行被过滤掉。
因此,只需删除GROUP BY
。并将其更改SELECT
为:
select coalesce(sum(a.quantity), 0) - coalesce(max(cc.quantityCor), 0)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句