SQL queries can help extract data from SAP Business One. This blog introduces two tools you may use to create queries and the eLearning materials which can help solve SQL problems. In the last part of the blog, we also give some examples.

I/ Tools for Creating Queries

Structured Query Language(SQL) is based on an idea where put all the data into a database and use a specific computer language to find and edit the data. To pulling data out of SAP Business one, we have two tools to create SQL statement: The Query Wizard and the Query Generator. Both of tools can be found under Tools > Queries.

Query Wizard: DOES NOT require SQL knowledge. There are 5 steps in the wizard which guide you step-by-step to create a query. Based on your choices, the system will generate the statement in the background and show the result directly.

Query Generator: Does require SQL knowledge. The interface is user-friendly to create an SQL statement. The system also displays the SQL commands so that you can edit them directly.

II/ eLearning Materials 

If you need free eLearning for these two tools, go to SAP Business One Academy, click Implementation and Support > Customization Tools, and see the material available for Queries (9.0).

Tips

1. Make sure the users responsible for writing queries have the correct authorizations.
2. In the SAP Business One, users are only able to run SELECT type statement. Some other statements, such as INSERT, UPDATE, DELETE and ALTER, cannot be edited in SAP Business one.
3. System Information and the SAP database reference can help us identify which tables to pull data from. Go to view > System Information from the menu bar and the table name and column name will show up at the bottom of the business one window. The SAP database reference is typically found on your server.

III/ Examples

Here are some sample queries used in SAP Business One. Some procedure details are also shown in some examples.

1/ Open Sales orders by customer

SELECT T0.”CardCode”, T0.”CardName”, T0.”DocNum”, T0.”DocDate”, T0.”DocTotal”

FROM ORDR T0

WHERE T0.”DocStatus” =’O’

ORDER BY T0.”CardCode”

Use [%0] to allow a user to select customers with specific characteristic

SELECT T0.”CardCode”, T0.”CardName”, T0.”DocNum”, T0.”DocDate”, T0.”DocTotal”

FROM ORDR T0

WHERE T0.”DocStatus” =’O’ AND T0.”CardCode” Like ‘%%[%0]%%’

ORDER BY T0.”CardCode”

2/ The amount of Open Sales Orders grouped by customer

SELECT T0.”CardCode”, T0.”CardName”, SUM(T0.”DocTotal”)

FROM ORDR T0

WHERE T0.”DocStatus” =’O’

ORDER BY T0.”CardCode”, T0.”CardName”

3/ Open Sales Orders by Customer Group

SELECT T0.”CardCode”, T0.”CardName”, T0.”DocNum”, T0.”DocDate”, T0.”DocTotal”, T2.”GroupName”

FROM ORDR T0 INNER JOIN OCRD T1 ON T0.”CardCode” = T1.”CardCode” INNER JOIN OCRG T2 ON T1.”GroupCode” = T2.”GroupCode”

WHERE T0.”DocStatus” =’O’

ORDER BY T2.”GroupName”

4/ Select customers with specific characteristic in specific period

SELECT T0.”CardCode”, T0.”CardName”, T0.”DocNum”, T0.”DocDate”, T1.”ItemCode”, T1.”Dscription”, T1.”Quantity”, T1.”OpenQty”, T1.”Price”, T1.”LineTotal”

FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.”DocEntry” = T1.”DocEntry”

WHERE T0.”DocStatus” =’O’ AND  T1.”LineStatus” =’O’

AND T0.”CardName” LIKE ‘%%[%0]%%’

AND T0.”DocDate”  >= [%1] AND T0.”DocDate” <=[%2]

5/ The amount of open quantity and order value grouped by item

SELECT T0.”ItemCode”, T0.”Dscription”, SUM(T0.”OpenQty”) AS “Total Open Qty”, SUM(T0.”LineTotal”) AS “Total Order Value”

FROM RDR1 T0

GROUP BY T0.”ItemCode”, T0.”Dscription”

6/ Closed Sales Order lines that short shipped or never delivered

SELECT T1.”ItemCode”, T1.”Dscription”, T1.”Quantity” AS “Order Qty”, T2.”Quantity” AS “Delivered Qty”, T1.”Price”, T1.”LineTotal”

FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.”DocEntry” = T1.”DocEntry” LEFT JOIN DLN1 T2 ON T1.”DocEntry”= T2.”BaseEntry” AND T1.”LineNum” = T2.”BaseLine”

WHERE T0.”DocStatus” =’C’ AND T1.”LineStatus” =’C’ AND ( T1.”Quantity” – T2.”Quantity” >0 OR  T2.”Quantity” IS NULL)

ORDER BY T1.”ItemCode”

7/ Open Purchase Orders by Vendor

SELECT T0.”CardCode”, T0.”CardName”, T0.”DocNum”, T0.”DocDate”, T0.”DocDueDate”, T0.”DocTotal”

FROM OPOR T0

WHERE T0.”DocStatus” = ‘O’

ORDER BY T0.”CardName”

8/ Items on Open Purchase Orders

SELECT T1.”ItemCode”, T1.”Dscription”, T1.”Quantity”, T1.”OpenQty”, T0.”DocDueDate”, T0.”CardCode”, T0.”CardName”

FROM OPOR T0 INNER JOIN POR1 T1 ON T0.”DocEntry” = T1.”DocEntry”

WHERE T1.”LineStatus” =’O’

ORDER BY T1.”ItemCode”

9/ Items in stock sorted by item group

SELECT T0.”ItemCode”, T0.”ItemName”, T0.”ItmsGrpCod”, T1.”ItmsGrpNam”, T0.”OnHand”, T0.”OnOrder”, T0.”CardCode”

FROM “SBODEMOUS”.”OITM” T0 INNER JOIN “SBODEMOUS”.”OITB” T1 ON T0.”ItmsGrpCod” = T1.”ItmsGrpCod” INNER JOIN OITW T2 ON T0.”ItemCode” = T2.”ItemCode”

ORDER BY T1.”ItmsGrpNam”

10/ Items in stock with specific warehouse

SELECT T0.”ItemCode”, T0.”ItemName”, T0.”ItmsGrpCod”, T1.”ItmsGrpNam”, T2.”WhsCode”, T2.”OnHand”, T0.”OnOrder”, T0.”CardCode”

FROM “SBODEMOUS”.”OITM” T0 INNER JOIN “SBODEMOUS”.”OITB”  T1 ON T0.”ItmsGrpCod” = T1.”ItmsGrpCod” INNER JOIN OITW T2 ON T0.”ItemCode” = T2.”ItemCode”

WHERE T2.”WhsCode” LIKE ‘%%[%0]%%’

ORDER BY T1.”ItmsGrpNam”

11/ Business Partner account balances and billing addresses

SELECT T0.”CardCode”, T0.”CardName”, T0.”GroupCode”, T0.”CntctPrsn”, T0.”Balance”, T1.”Street”, T1.”Block”, T1.”City”, T1.”State”, T1.”ZipCode”

FROM OCRD T0  INNER JOIN CRD1 T1 ON T0.”CardCode” = T1.”CardCode”

WHERE T1.”AdresType” =’B’ AND  T0.”Balance” >=1000

ORDER BY T0.”GroupCode”, T0.”CardCode”

12/ Balances by business partner group

SELECT T1.”GroupName”, SUM(T0.”Balance”)

FROM OCRD T0  INNER JOIN OCRG T1 ON T0.”GroupCode” = T1.”GroupCode”

GROUP BY T1.”GroupName”