Mastering SQL Queries for SAP Business One

Commonly used statements

SQL queries comprise a statement, keyword, function, expression, and parameters. To see how commonly used query statements work, you can have a look at the following query example that includes most of the statements and some of the functions. The query contents and meaning of these query results will be discussed in the next chapter:

SELECT TOP 5 T0.ShortName 'Customer', Max(T2.CardName) 'Customer Name', SUM(ISNULL(T0.Debit,0) - ISNULL(T0.Credit,0)) as "Amount(LC)" FROM dbo.JDT1 T0 INNER JOIN dbo.OJDT T1 ON T1.TransID = T0.TransID and T0.TransType IN (13,14) INNER JOIN dbo.OCRD T2 ON T2.CardCode = T0.ShortName WHERE t1.RefDate >= [%0] and t1.RefDate <= [%1] GROUP BY T0.ShortName HAVING SUM(ISNULL(T0.Debit,0) - ISNULL(T0.Credit,0)) ...