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.
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.
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).
Here are some sample queries used in SAP Business One. Some procedure details are also shown in some examples.
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”
SELECT T0.”CardCode”, T0.”CardName”, SUM(T0.”DocTotal”)
FROM ORDR T0
WHERE T0.”DocStatus” =’O’
ORDER BY T0.”CardCode”, T0.”CardName”
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”
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]
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”
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”
SELECT T0.”CardCode”, T0.”CardName”, T0.”DocNum”, T0.”DocDate”, T0.”DocDueDate”, T0.”DocTotal”
FROM OPOR T0
WHERE T0.”DocStatus” = ‘O’
ORDER BY T0.”CardName”
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”
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”
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”
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”
SELECT T1.”GroupName”, SUM(T0.”Balance”)
FROM OCRD T0 INNER JOIN OCRG T1 ON T0.”GroupCode” = T1.”GroupCode”
GROUP BY T1.”GroupName”