How the B1 database is built.

Almost every object in B1 is a header table with one or more line tables. Header and master data tables start with O; their line tables use the same letters with a number.

  • Master data: OCRD business partners (key CardCode), OITM items (key ItemCode), OITW stock per item and warehouse, with OnHand, IsCommited and OnOrder.
  • Documents: ORDR and RDR1 for sales orders, ODLN and DLN1 for deliveries, OINV and INV1 for A/R invoices. Purchasing follows the same pattern, for example OPOR and POR1 for purchase orders.
  • Journal entries: OJDT for the header and JDT1 for the lines, one line per account with Debit and Credit.

Two numbers identify a document. DocEntry is the internal key: the lines carry it, and every link between documents uses it. DocNum is the number the user sees, taken from the numbering series. Join on DocEntry, show DocNum. ObjType says what kind of object a row is: 17 is a sales order, 15 a delivery, 13 an A/R invoice, 16 an A/R return, 20 a goods receipt PO, 21 a goods return. You meet these numbers again in the links between documents.

Finding the table and the field.

You don't need to know the names by heart. In the client, choose View > System Information. When you then move the cursor over a field, the status bar shows the table and field behind it, for example ORDR and CardCode on the customer field of a sales order. The database tables reference in the SAP Business One SDK lists every table with its fields, types and valid values, such as O and C for open and closed.

The Query Generator helps too: you pick tables on the left and it shows their fields; fields that point to another table are shown in bold, and dragging one into the table area adds that table.

Joins: headers, lines and the document chain.

Lines join to their header on DocEntry, business partners on CardCode, items on ItemCode. Open sales order lines with the customer name: SELECT T0.DocNum, T0.CardCode, T2.CardName, T1.ItemCode, T1.OpenQty FROM ORDR T0 JOIN RDR1 T1 ON T1.DocEntry = T0.DocEntry JOIN OCRD T2 ON T2.CardCode = T0.CardCode WHERE T1.LineStatus = 'O'

A document copied from another one remembers where each line came from: BaseType is the ObjType of the base document, BaseEntry its DocEntry and BaseLine the LineNum of the base line. An invoice line that came from a delivery joins back like this: FROM INV1 T0 JOIN DLN1 T1 ON T1.DocEntry = T0.BaseEntry AND T1.LineNum = T0.BaseLine WHERE T0.BaseType = 15. The guide to document flow explains how documents are copied.

  • DocStatus and LineStatus: O open, C closed. A document can be open while some of its lines are closed; filter on the level you mean.
  • OpenQty: the quantity still open on a line; Quantity is the full quantity.
  • CANCELED: N for a normal document; on deliveries and invoices Y marks a cancelled document and C the cancellation document. CANCELED = 'N' leaves both out.

Adding up with GROUP BY.

GROUP BY turns rows into totals, and HAVING filters on those totals. Customers invoiced for more than 10,000: SELECT T0.CardCode, SUM(T1.LineTotal) FROM OINV T0 JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry WHERE T0.CANCELED = 'N' GROUP BY T0.CardCode HAVING SUM(T1.LineTotal) > 10000. For real revenue, subtract A/R credit memos (ORIN and RIN1) in the same way.

  • Don't sum header fields after joining the lines. DocTotal is on the header; joined to five lines, it is counted five times. Sum line fields, or sum the header without the lines.
  • Per month: YEAR(T0.DocDate) and MONTH(T0.DocDate) in the SELECT and the GROUP BY work on both platforms.
  • Stock per warehouse: SELECT ItemCode, WhsCode, OnHand FROM OITW WHERE OnHand > 0.
  • Open items: in JDT1, BalDueDeb and BalDueCred hold what is still open on each line, and ShortName the business partner or account code. Summed per ShortName, that gives the open balance per partner.

SAP HANA next to SQL Server.

B1 runs on Microsoft SQL Server or on SAP HANA. The tables and fields are the same; the SQL dialect is not. The differences you meet first:

  • Names: HANA converts names without double quotes to upper case, and B1's field names are mixed case. On HANA you write SELECT "CardCode" FROM "OCRD"; SQL Server accepts CardCode as it is.
  • Empty values: ISNULL(x, 0) on SQL Server, IFNULL(x, 0) on HANA. COALESCE works on both.
  • First rows: SELECT TOP 10 works on both; HANA also has LIMIT 10 at the end of the query.
  • Dates: GETDATE(), DATEADD and DATEDIFF on SQL Server; CURRENT_DATE, ADD_DAYS and DAYS_BETWEEN on HANA.
  • Joining text: + on SQL Server, || on HANA. CONCAT exists on both, but on HANA it takes exactly two arguments.
  • Converting: CAST works on both; SQL Server adds CONVERT, HANA TO_VARCHAR and TO_DATE.

A query written for one platform usually fails on the other with a syntax error. If your company may move to HANA one day, or a partner writes queries for several customers, keep to what both understand where you can.

Queries in B1: Query Generator, Query Manager and variables.

Tools > Queries > Query Generator builds a SELECT from the tables and fields you pick; Tools > Queries > Query Wizard walks you through the same steps. After Execute, choose Save in the result window and give the query a name and a category. Tools > Queries > Query Manager lists the saved queries by category; there you open the query structure to edit the SQL itself. Categories can be tied to authorization groups, so not every user sees every query.

A variable makes the user enter a value when the query runs. Write [%0] where the value goes, and [%1], [%2] for more: SELECT T0.DocNum, T0.CardCode, T0.DocTotal FROM OINV T0 WHERE T0.DocDate >= [%0].

These tools are for reading only. The help says it plainly: the Query Generator is designed for data retrieval, not for updates, and the Query Wizard only retrieves data. Keep it that way everywhere, also in a database tool outside B1: never write to the B1 tables with UPDATE, INSERT or DELETE. That skips every check B1 makes, and documents, stock and journal entries can stop matching. Changes go through the client or an interface that applies B1's rules, such as the Service Layer or the DI API.

Checklist before you run a query.

  • Only SELECT. Nothing in a saved query changes data.
  • Know your platform, HANA or SQL Server, and write for it.
  • Join on DocEntry, never on DocNum.
  • Filter on CANCELED and on the status you mean: document or line.
  • Put a date or status in the WHERE: tables such as JDT1 and INV1 grow every day.
  • Check a total against a standard B1 report before anyone relies on it.
  • Try heavy queries in a test company first, not in production during working hours.
  • Use categories and authorizations: queries also show personal data, such as addresses and employees.

How KoonLearn approaches it.

KoonLearn, our training simulation for SAP Business One, has a deep dive called SQL and the Business One database. You write queries yourself in a rebuilt Query Manager, on the tables of the company in the story, and each result is compared with the expected rows. Its sections follow the topics of this guide: table structure, joins and document flow, aggregation, HANA next to SQL Server, queries in Business One, and repairing broken queries. You pick SAP HANA or SQL Server in your profile. The deep dive ends with a quiz and a module exam, with a result per section.

Prices and options are in the customer portal. The deep dive goes beyond the exam: it is not preparation for C_TB120 and gives no certificate. Everything runs in the browser, without an SAP licence or database. Chapter 03 Purchasing of the main story is free without an account: start it on learn.koonsystems.com. KoonLearn is not affiliated with SAP SE.

Sources: SAP Business One 10.0 help documentation (Query Generator, Query Generator Window, Query Wizard, Defining Conditions and Relations, Saving a Query, Changing a Saved Query); the SAP Business One 10.0 database tables reference (Displaying System Information in the Application; OCRD, OITM, OITW, ORDR/RDR1, ODLN/DLN1, OINV/INV1, OJDT/JDT1); SAP HANA SQL Reference Guide; Microsoft Transact-SQL reference; the KoonLearn chapter overview. SAP, SAP HANA and SAP Business One are trademarks of SAP SE; SQL Server is a trademark of Microsoft.