Sub Queries can be extremely useful. Let’s say you want to get a list of business partners (Customers) who have ordered 1000 or more cases of your products in total. Because your report would be getting data from the RDR1 table, without a sub query, the best you could do is sum quantities for all business partners. But here’s how you can list only those businesspartners who’s aggregate order quantity is greater than 1000?
select t2.cardcode, t2.cardname from ocrd t2 where t2.cardcode in (
select t1.cardcode
from RDR1 t0 inner join ORDR t1 on t0.docentry=t1.docentry
group by t1.cardcode
having sum(t0.quantity) > 1000)
