Blog

SAP Business One – Query Generator

By Ryan Howe

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)

Ryan Howe

Ryan Howe

Partner, Clients First Business Solutions New Jersey

Ryan Howe is a Certified Public Accountant turned SAP Business One consultant. Leading the SAP Business One practice at Clients First, Ryan has over 23 years’ experience in the financial management and ERP industry, specializing in business analysis, relationship building, and business process optimization. When he’s not helping customers leverage SAP Business One to meet their short and long-term needs, you can find him at any Michigan State sporting event with his family – go Spartans!