SAP Business One SQL Queries - Tips and Resources | Seidor USA

Our Top Tips and Resources on SQL Queries in SAP Business One

Use SQL queries in SAP Business One to extract data from your system.  This summary provides information on the tools available in SAP Business One to generate queries, and where to find eLearning materials on the Query Wizard and Query Generator.  We've also included some sample queries to pull data on customers, orders and items.

 

Tools for Creating Queries in SAP Business One

For pulling data out of SAP Business One, you need to generate a query.  A query is a user-defined report that directly reads the required tables from the database and displays them formatted using the Structured Query Language (SQL).  There are two different tools in SAP Business One to create the necessary SQL statements -- the Query Wizard and the Query Generator. 

Query Wizard:  Does NOT require SQL knowledge.  Guides you step-by-step through the process of creating a query.  The system generates the SQL statements in the background and keeps them permanently hidden.

Query Generator:  DOES require SQL knowledge.  Has a user-friendly interface for creating an SQL statement.  The system displays the SQL commands so you can edit them directly.

 

Where to Find eLearning Materials on the Query Wizard and Query Generator

Free eLearning is available on the Query Wizard and the Query Generator through the SAP Business One Academy.

Go To Implementation and Support, Customization Tools, and see the material available for Queries (9.0).

 

Tips on Developing SQL Queries in SAP Business One

Here are our top 3 tips on developing SQL queries in SAP Business One.

  1. Make sure the users responsible for writing queries have the correct authorizations.
  2. The most important thing to know about SQL queries in SAP Business One is that you can only run SELECT type SQL statements in SAP.  You are not allowed to run any other kinds of SQL (i.e. INSERT, UPDATE, DELETE, CREATE, ALTER, etc).  Running any of these in SAP Business One will cause SAP not to provide support for your system.
  3. Know how to identify the tables to pull data from.  Two places can help us find that out, system information and the SAP database reference.  To view the system information, go to view > system information from the menu bar. This will display system information in the message bar at the bottom of the business one window. The table name and column name will be on the right of the system information.  The SAP database reference is typically found on your server.

 

Example Queries for SAP Business One

 

Here are some example queries used in SAP Business One to execute data on customers, orders and items.

-- Example 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]

--To allow a user to select customer or leave it blank use
SELECT T0.[CardCode], T0.[CardName], T0.[DocNum], T0.[DocDate], T0.[DocTotal]
FROM ORDR T0
WHERE T0.[DocStatus] ='O' AND  T0.[CardName] Like '%%[%0]%%'
ORDER BY T0.[CardCode], T0.[CardName]

--Example 2 --
--Open Sales Orders grouped by customer
SELECT T0.[CardCode], T0.[CardName], SUM(T0.[DocTotal])
FROM ORDR T0
WHERE T0.[DocStatus] ='O'
GROUP BY T0.[CardCode], T0.[CardName]

--Example 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
ORDER BY T2.[GroupName]

-- Example 4 --
-- Open Sales order lines by customer --
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]

--Example 5 --
-- Open Sales order lines 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]

--Example 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]

--Example 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]

--Example 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]

--Example 9--
-- Items OnHand sorted by item group--
SELECT T0.[ItemCode], T0.[ItemName], T0.[ItmsGrpCod], T1.[ItmsGrpNam],
T0.[OnHand], T0.[OnOrder], T0.[CardCode]
FROM OITM T0  INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod
INNER JOIN OITW T2 ON T0.ItemCode = T2.ItemCode
ORDER BY T1.[ItmsGrpNam]

--Example 10--
-- Items OnHand by item warehouse--
SELECT T0.[ItemCode], T0.[ItemName], T0.[ItmsGrpCod], T1.[ItmsGrpNam],
T2.[WhsCode], T2.[OnHand], T2.[OnOrder],  T0.[CardCode]
FROM OITM T0  INNER JOIN 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], T0.[ItemCode]

--Example 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]

--Example 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]

More SQL Query Examples

For more examples, read the blog article by Tim Guest, "My Top SQL Queries for SAP Business One" available from the SAP Community Blog.

More Tips and Tricks for SQL

Additional tips and tricks can be found through the SAP B1 SQL Tips and Tricks Community WIKI page.

Seidor is the global #1 SAP Platinum Partner of SAP Business One.  We sell, install, implement, train and support

our customers for this software solution from our offices in Houston, Detroit, New Jersey, Miami and California.

If we can help you get more value from your SAP Business One, please

X
contact us