Wie die B1-Datenbank aufgebaut ist.

Fast jedes Objekt in B1 ist eine Kopftabelle mit einer oder mehreren Zeilentabellen. Kopf- und Stammdatentabellen beginnen mit O; ihre Zeilentabellen haben dieselben Buchstaben mit einer Ziffer.

  • Stammdaten: OCRD Geschäftspartner (Schlüssel CardCode), OITM Artikel (Schlüssel ItemCode), OITW Bestand pro Artikel und Lager, mit OnHand, IsCommited und OnOrder.
  • Belege: ORDR und RDR1 für Kundenaufträge, ODLN und DLN1 für Lieferungen, OINV und INV1 für Ausgangsrechnungen. Der Einkauf folgt demselben Muster, zum Beispiel OPOR und POR1 für Bestellungen.
  • Journalbuchungen: OJDT für den Kopf und JDT1 für die Zeilen, eine Zeile pro Konto mit Debit und Credit.

Zwei Nummern kennzeichnen einen Beleg. DocEntry ist der interne Schlüssel: Die Zeilen tragen ihn, und jede Verknüpfung zwischen Belegen nutzt ihn. DocNum ist die Nummer, die der Benutzer sieht, aus der Nummernserie. Verknüpfen Sie über DocEntry, zeigen Sie DocNum. ObjType sagt, welche Art Objekt eine Zeile ist: 17 ist ein Kundenauftrag, 15 eine Lieferung, 13 eine Ausgangsrechnung, 16 eine Kundenretoure, 20 ein Wareneingang (Goods Receipt PO), 21 eine Warenrücksendung. Diese Nummern begegnen Ihnen wieder in den Verknüpfungen zwischen Belegen.

Tabelle und Feld finden.

Sie müssen die Namen nicht auswendig kennen. Wählen Sie im Client View > System Information. Bewegen Sie danach den Cursor über ein Feld, zeigt die Statusleiste die Tabelle und das Feld dahinter, zum Beispiel ORDR und CardCode im Kundenfeld eines Kundenauftrags. Die Datenbanktabellenreferenz im SAP Business One SDK listet jede Tabelle mit ihren Feldern, Typen und gültigen Werten, etwa O und C für offen und geschlossen.

Auch der Query Generator hilft: Sie wählen links die Tabellen, und er zeigt ihre Felder; Felder, die auf eine andere Tabelle verweisen, sind fett, und wenn Sie eines in den Tabellenbereich ziehen, kommt diese Tabelle hinzu.

Joins: Köpfe, Zeilen und die Belegkette.

Zeilen verknüpfen Sie über DocEntry mit ihrem Kopf, Geschäftspartner über CardCode, Artikel über ItemCode. Offene Auftragszeilen mit dem Kundennamen: 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'

Ein aus einem anderen kopierter Beleg merkt sich pro Zeile, woher sie kam: BaseType ist der ObjType des Basisbelegs, BaseEntry sein DocEntry und BaseLine die LineNum der Basiszeile. Eine Rechnungszeile aus einer Lieferung verknüpfen Sie so zurück: FROM INV1 T0 JOIN DLN1 T1 ON T1.DocEntry = T0.BaseEntry AND T1.LineNum = T0.BaseLine WHERE T0.BaseType = 15. Der Leitfaden zum Belegfluss erklärt, wie Belege kopiert werden.

  • DocStatus und LineStatus: O offen, C geschlossen. Ein Beleg kann offen sein, während einige Zeilen geschlossen sind; filtern Sie auf der Ebene, die Sie meinen.
  • OpenQty: die noch offene Menge einer Zeile; Quantity ist die volle Menge.
  • CANCELED: N für einen normalen Beleg; auf Lieferungen und Rechnungen kennzeichnet Y einen stornierten Beleg und C den Stornobeleg. CANCELED = 'N' lässt beide weg.

Summieren mit GROUP BY.

GROUP BY macht aus Zeilen Summen, und HAVING filtert auf diese Summen. Kunden mit mehr als 10.000 berechnet: 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. Für den echten Umsatz ziehen Sie die Gutschriften (ORIN und RIN1) auf dieselbe Weise ab.

  • Summieren Sie keine Kopffelder nach einem Join mit den Zeilen. DocTotal steht im Kopf; mit fünf Zeilen verknüpft, zählt es fünfmal. Summieren Sie Zeilenfelder oder den Kopf ohne die Zeilen.
  • Pro Monat: YEAR(T0.DocDate) und MONTH(T0.DocDate) im SELECT und im GROUP BY funktionieren auf beiden Plattformen.
  • Bestand pro Lager: SELECT ItemCode, WhsCode, OnHand FROM OITW WHERE OnHand > 0.
  • Offene Posten: In JDT1 stehen in BalDueDeb und BalDueCred die noch offenen Beträge je Zeile und in ShortName der Geschäftspartner- oder Kontocode. Pro ShortName summiert ergibt das den offenen Saldo pro Partner.

SAP HANA neben SQL Server.

B1 läuft auf Microsoft SQL Server oder auf SAP HANA. Tabellen und Felder sind gleich, der SQL-Dialekt nicht. Die Unterschiede, denen Sie zuerst begegnen:

  • Namen: HANA wandelt Namen ohne doppelte Anführungszeichen in Großbuchstaben um, und die Feldnamen von B1 haben gemischte Groß- und Kleinschreibung. Auf HANA schreiben Sie SELECT "CardCode" FROM "OCRD"; SQL Server nimmt CardCode so, wie es ist.
  • Leere Werte: ISNULL(x, 0) auf SQL Server, IFNULL(x, 0) auf HANA. COALESCE funktioniert auf beiden.
  • Erste Zeilen: SELECT TOP 10 funktioniert auf beiden; HANA kennt zusätzlich LIMIT 10 am Ende der Abfrage.
  • Datum: GETDATE(), DATEADD und DATEDIFF auf SQL Server; CURRENT_DATE, ADD_DAYS und DAYS_BETWEEN auf HANA.
  • Text verbinden: + auf SQL Server, || auf HANA. CONCAT gibt es auf beiden, auf HANA aber mit genau zwei Argumenten.
  • Umwandeln: CAST funktioniert auf beiden; SQL Server hat zusätzlich CONVERT, HANA TO_VARCHAR und TO_DATE.

Eine für eine Plattform geschriebene Abfrage scheitert auf der anderen meist mit einem Syntaxfehler. Kann Ihr Unternehmen eines Tages auf HANA wechseln oder schreibt ein Partner Abfragen für mehrere Kunden, halten Sie sich wo möglich an das, was beide verstehen.

Abfragen in B1: Query Generator, Query Manager und Variablen.

Tools > Queries > Query Generator baut ein SELECT aus den Tabellen und Feldern, die Sie wählen; Tools > Queries > Query Wizard führt Sie durch dieselben Schritte. Nach Execute wählen Sie im Ergebnisfenster Save und geben der Abfrage einen Namen und eine Kategorie. Tools > Queries > Query Manager zeigt die gespeicherten Abfragen nach Kategorie; dort öffnen Sie die Abfragestruktur, um das SQL selbst zu ändern. Kategorien lassen sich mit Berechtigungsgruppen verknüpfen, sodass nicht jeder Benutzer jede Abfrage sieht.

Eine Variable lässt den Benutzer beim Ausführen einen Wert eingeben. Schreiben Sie [%0] dorthin, wo der Wert stehen soll, und [%1], [%2] für weitere: SELECT T0.DocNum, T0.CardCode, T0.DocTotal FROM OINV T0 WHERE T0.DocDate >= [%0].

Diese Werkzeuge sind nur zum Lesen da. Die Hilfe sagt es deutlich: Der Query Generator dient dem Abrufen von Daten, nicht dem Ändern, und der Query Wizard ruft nur Daten ab. Halten Sie es überall so, auch in einem Datenbankwerkzeug außerhalb von B1: Schreiben Sie nie mit UPDATE, INSERT oder DELETE in die B1-Tabellen. Das umgeht jede Prüfung von B1, und Belege, Bestand und Journalbuchungen können auseinanderlaufen. Änderungen gehen über den Client oder eine Schnittstelle, die die Regeln von B1 anwendet, etwa den Service Layer oder die DI API.

Checkliste, bevor Sie eine Abfrage ausführen.

  • Nur SELECT. Nichts in einer gespeicherten Abfrage ändert Daten.
  • Kennen Sie Ihre Plattform, HANA oder SQL Server, und schreiben Sie dafür.
  • Verknüpfen Sie über DocEntry, nie über DocNum.
  • Filtern Sie auf CANCELED und auf den gemeinten Status: Beleg oder Zeile.
  • Setzen Sie ein Datum oder einen Status in das WHERE: Tabellen wie JDT1 und INV1 wachsen jeden Tag.
  • Prüfen Sie eine Summe gegen einen Standardbericht von B1, bevor sich jemand darauf verlässt.
  • Testen Sie schwere Abfragen zuerst in einer Testfirma, nicht produktiv während der Arbeitszeit.
  • Nutzen Sie Kategorien und Berechtigungen: Abfragen zeigen auch personenbezogene Daten wie Adressen und Mitarbeiter.

Wie KoonLearn es angeht.

KoonLearn, unsere Trainingssimulation für SAP Business One, hat eine Vertiefung SQL and the Business One database. Sie schreiben selbst Abfragen in einem nachgebauten Query Manager, auf den Tabellen des Unternehmens aus der Geschichte, und jedes Ergebnis wird mit den erwarteten Zeilen verglichen. Die Abschnitte folgen den Themen dieses Leitfadens: Tabellenstruktur, Joins und Belegfluss, Aggregation, HANA neben SQL Server, Abfragen in Business One und das Reparieren defekter Abfragen. In Ihrem Profil wählen Sie SAP HANA oder SQL Server. Die Vertiefung endet mit einem Test und einer Modulprüfung, mit einem Ergebnis pro Abschnitt.

Preise und Optionen finden Sie im Kundenportal. Die Vertiefung geht über die Prüfung hinaus: Sie ist keine Vorbereitung auf C_TB120 und gibt kein Zertifikat. Alles läuft im Browser, ohne SAP-Lizenz oder Datenbank. Kapitel 03 Einkauf der Hauptgeschichte ist ohne Konto kostenlos: starten Sie es auf learn.koonsystems.com. KoonLearn ist nicht mit der SAP SE verbunden.

Quellen: die Hilfedokumentation von SAP Business One 10.0 (Query Generator, Query Generator Window, Query Wizard, Defining Conditions and Relations, Saving a Query, Changing a Saved Query); die Datenbanktabellenreferenz von SAP Business One 10.0 (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-Referenz; die Kapitelübersicht von KoonLearn. SAP, SAP HANA und SAP Business One sind Marken der SAP SE; SQL Server ist eine Marke von Microsoft.