I've got this query that has been in our system for a long time. I feel like it could be simplified, I'm just not sure. Anyone care to lend a hand? It's taking quantities from various tables to get total quantities for an inventory system.
it also uses that less verbose join in the where clause, as apposed to "JOIN X ON" - not sure if that is less efficient.
select i.account,i.uic,i.site,i.item_no,i.cage,i.min_allo w,sum(i.qty+nvl(k.qty,0)) qty
(select account,uic,site,item_no,cage,min_allow,sum(qty) qty
group by account,uic,site,item_no,cage,min_allow) i, process_bal k
WHERE i.item_no=k.item_no(+) and i.cage=k.cage(+) and i.uic=k.uic(+)
GROUP BY i.account,i.uic,i.site,i.item_no,i.cage,i.min_allo w
select u.account,k.uic,u.site,k.item_no,k.cage,0 ,sum(k.qty)
FROM uic u, process_kit k
AND not exists (select 1 from inventory where uic=k.uic and item_no=k.item_no and cage=k.cage)
GROUP BY u.account,k.uic,u.site,k.item_no,k.cage, 0