Query - Aufbau von Requests

Sie stellen sich jetzt vielleicht die Frage, wieso man mit Query nicht ganz normale SQL-Select-Abfragen ausführen kann.

Das liegt daran, dass SQL eben nicht gleich SQL ist. So ist beispielsweise die Trim-Funktion für Strings gemäss SQL-92 genormt, wird aber von Microsoft anders implementiert. Würden wir ganz normale SQL-Select-Abfragen unterstützen, könnten in der Folge Scripts und Programme entstehen, welche u.U. zu einem späteren Zeitpunkt nicht mehr korrekt funktionieren oder fehlerhafte Daten zurückliefern. Deshalb verwendet der Service "Query" eine eigene Syntax. Diese Syntax wird dann in SQL-Datenbank -spezifische Kommandos übersetzt. So können wir sicherstellen, dass eine bestimmte Query-Abfrage immer korrekt funktioniert. Sie als Anwender müssen sich nicht um SQL-spezifische Eigenheiten kümmern und können sich jederzeit auf die korrekte Funktionalität der Query-Abfrage verlassen.

Query-Requests sind, im Gegensatz zu normalen Service-Requests, nicht fix aufgebaut (Struktur). Die Reihenfolge der Argumente ist daher nicht zwingend vorgegeben. Darüber hinaus können einige Argumente (z.B. Column) mehrmals vorkommen.

Wir empfehlen aufgrund der besseren Lesbarkeit, immer denselben Aufbau bzw. dieselbe Reihenfolge einzuhalten.

Minimal notwendige Attribute

Ein Query-Request bzw. eine Query-Abfrage benötigt minimal drei Argumente:

Main (BO)
Column (mindestens eine Column)
MaxRows (Anzahl Resultat-Zeilen)

Beispiel:

Query
Main=Addr
Columns=Addr.FullName
MaxRows=10

Wichtig

BO-Attributnamen müssen immer "qualified" (d.h. Inkl. BO-Name als Präfix) angegeben werden.

Richtig: Addr.FullName

Falsch: FullName

Basis-Argumente für einfache Query-Abfragen

Die wichtigsten (Basis-) Argumente in der Übersicht:

Argument

Beschreibung / Beispiel

Zwingend

Main=[Alias, ] Source

Zu verwendendes Main-BO.

Main=Firma,Addr

Notiz

Wird beim Main-BO mit einem Alias gearbeitet, dann muss dieser Alias auch bei den anderen Argumenten (Column/Columns, Filter, etc.) verwendet werden. Der Alias ersetzt in diesem Fall den BO-Namen als Präfix. Beispiel: Anstelle von Addr.LastName wird Firma.LastName verwendet.

MaxRows=nn/All

Anzahl Datensätze bzw. Resultat-Zeilen.

MaxRows=15

Filter=FilterExpression

Filter auf das Main-BO.

Filter=Firma.HighParentCompany <> 0

Sie haben die Möglichkeit, Strings in Filtern spezifisch als "ohne Escaping" zu deklarieren. Die Deklaration erfolgt dabei mittels Backtick-Character.

// Pfadangabe in einem Filter ohne Escaping der Backslash-Character
Filter=PrintJob.PrinterDevice = `\\ServerName\Share`

Nein

Columns=Attribut,Attribut,...

Für die Rückgabe gewünschte Attribute, wenn kein Alias für den Spaltennamen benötigt wird.

Columns ist nur für Attribute, nicht für Expressions erlaubt.

Columns=Firma.Number,Firma.LastName,Firma.FirstName

Ja, wenn Column fehlt.

Column=Alias,Attribut/Expression

Das für die Rückgabe gewünschte Attribut. Die Angabe eines Alias ist zwar nicht zwingend, wird aber dringend empfohlen. Das Komma vor Attribut bzw. Expression muss in jedem Fall angegeben werden.

Column=Firmenname,Firma.FullName

Ja, wenn Columns fehlt.

OrderBy=[+/-]Column

OrderByAsDate=[+|-]Column

OrderByAsNmb=[+|-]Column

Gewünschte Sortier-Reihenfolge. Es ist möglich, mehrere Columns unterschiedlich zu sortieren.

Die Angabe von + bzw. keine Angabe, bedeutet: Aufsteigend sortieren.

Die Angabe von - bedeutet: Absteigend sortieren.

OrderBy=Firma.LastName
OrderBy=-Firma.Zip

Nein

Distinct=0/1

Distinct=FALSE/TRUE

Rows mit identischem Inhalt werden unterdrückt.

Default bzw. keine Angabe = 0/FALSE (Nicht unterdrücken)

Distinct=1
Distinct=TRUE

Nein

SuppressSqlParameterUsage

Sind die parametrierten Statements global aktiviert, was ab v17.10.00 per Default der Fall ist, kann diese Funktionalität zur Laufzeit einer Query-Abfrage übersteuert, in diesem Falle also ausgeschaltet, werden.

SuppressSqlParameterUsage=TRUE

Nein

ForceSqlParameterUsage

Sind die parametrierten Statements global deaktiviert, kann diese Funktionalität zur Laufzeit einer Query-Abfrage übersteuert, in diesem Falle also eingeschaltet, werden.

ForceSqlParameterUsage=TRUE

Nein

Einfache Abfrage

Beispiel: Es soll der FullName von 10 Adressen ausgegeben werden.

Query

Query
Main=Addr
Columns=Addr.FullName
MaxRows=10

Resultat

Addr.FullName
-
-
-
-
-
-
-
-
-
-

Hier sind zwar zehn Rows (Adressen) vorhanden, anscheinend ist bei diesen Adressen aber kein Vor- und Nachname erfasst.

Filter

Es wird nun ein Filter auf FullName angewendet: FullName darf nicht leer sein.

Achtung

Das Verwenden von BoId-Attributen (z.B. Addr.BoId=...) ist aus Performancegründen unbedingt zu vermeiden.

Query

Query
Main=Addr
Columns=Addr.FullName
MaxRows=10
Filter=Addr.FullName<>""

Resultat

Addr.FullName
4B Fenster
A + E Leasing AG
A.V. Chemie AG
Aarpol AG
ABB Normelec AG
ABB Robotics AG
ABC Handels AG
Abegg-Bauguss AG
Abegglen AG
Abexim AG

Sortieren von mehreren Columns

Die Adressen sollen nun aufsteigend nach FullName und anschliessend absteigend nach Adress-Nr. sortiert werden.

Achtung

Das Verwenden von BoId-Attributen (z.B. Addr.BoId=...) ist aus Performancegründen unbedingt zu vermeiden.

Query

Query
Main=Addr
Columns=Addr.FullName, Addr.Number
MaxRows=10
Filter=Addr.FullName>="ABB"
OrderBy=Addr.FullName
OrderBy=-Addr.Number

Resultat

Addr.FullName	Addr.Number
ABB Normelec AG	10667
ABB Robotics AG	10248
ABC Handels AG	10925
Abegg-Bauguss AG	10784
Abegg-Bauguss AG	10333
Abexim AG	10962
Acanta Handels AG	10025
Achermann & Cie. AG	1044
Achermann & Cie. AG	1043
Achermann & Cie. AG	1041

Adressen mit demselben FullName werden nun nach Adress-Nr. absteigend sortiert.

Filter auf leeres Datum

Es sollen nur Adressen ausgegeben werden, bei denen ein Geburtsdatum (Addr.DateOfBirth) erfasst ist.

Query

Query
Main=Addr
Columns=Addr.FullName, Addr.Number, Addr.DateOfBirth
MaxRows=All
Filter=Addr.DateOfBirth<>EMPTY_DATE
OrderBy=Addr.FullName

Resultat

Addr.FullName	Addr.Number	Addr.DateOfBirth
Anton Rast	1050	12.01.1992
Beno Lütolf	1049	24.08.1972
Jakob Sieber	1042	15.07.1986

Hier werden alle Datensätze ausgefiltert, bei denen das Datum (Addr.DateOfBirth) nicht leer ist. Dafür steht bei Query die spezielle "Expression" EMPTY_DATE zur Verfügung.

Filter auf Datum

Es sollen nur Adressen ausgegeben werden, bei denen das Geburtsdatum grösser ist, als ein bestimmter Wert.

Query

Query
Main=Addr
Columns=Addr.FullName, Addr.Number, Addr.DateOfBirth
MaxRows=All
Filter=Addr.DateOfBirth>31.12.1979
OrderBy=Addr.FullName

Resultat

Addr.FullName	Addr.Number	Addr.DateOfBirth
Anton Rast	1050	12.01.1992
Jakob Sieber	1042	15.07.1986

Wichtig

Filter auf Datumswerte dürfen nicht als String erfasst werden.

Richtig: Filter=Addr.DateOfBirth>31.12.1979

Falsch: Filter=Addr.DateOfBirth>"31.12.1979"

Identische Datensätze (Resultat) ausblenden mit Distinct

Query

Query
Main=Addr
Columns=Addr.FullName
MaxRows=All
Filter=Addr.LastName like "Schild"
OrderBy=Addr.FullName

Resultat

Addr.FullName
Martin Schild
Martin Schild
Martin Schild
Martin Schild
Martin Schild
Volker Schild

Query

Query
Main=Addr
Columns=Addr.FullName
MaxRows=All
Filter=Addr.LastName like "Schild"
OrderBy=Addr.FullName
Distinct=1

Resultat

Addr.FullName
Martin Schild
Volker Schild

Filter mit mehreren Kriterien

Durch das Erfassen einer "Liste", können Sie ein Attribut nach mehreren Kriterien filtern. Die Verknüpfung der Kriterien erfolgt dabei mit ODER.

Beispiel: Es sollen alle Adressen aufgelistet werden, bei denen der Nachname (Addr.LastName) entweder Schild oder Vonwil lautet.

Query

Query
Main=Addr
Columns=Addr.FullName
MaxRows=All
Filter=Addr.LastName = ["Schild","Vonwil"]
OrderBy=Addr.FullName
Distinct=1

Wird die Bedingung mit <> (ungleich) abgefragt, werden die Werte dabei mit UND verknüpft.

Beispiel: Es sollen alle Adressen aufgelistet werden, bei denn der Nachname (Addr.LastName) nicht Schild und auch nicht Vonwil lautet.

Query
Query
Main=Addr
Columns=Addr.FullName
MaxRows=All
Filter=Addr.LastName <> ["Schild","Vonwil"]
OrderBy=Addr.FullName
Distinct=1

Zugriff auf Konstanten (Konstanten-Views)

Zugriffe auf Konstanten-Werte sind "by name" möglich. Hilfreich kann dies vor allem bei Filter-Funktionen sein.

Beispiel: Es sollen alle Rechnungen innerhalb eines bestimmten Zeitraums ausgegeben werden. Die entsprechende Query-Abfrage kann nun im Filter die Dokumentstufe oder die Konstante enthalten.

Query mit Filter auf Dokumentstufe (4 - Rechnung)

Query
Main=SalDoc
MaxRows=All
Filter=SalDoc.DocDate>30.11.2014 and SalDoc.DocDate<01.03.2015 and SalDoc.SalProcLevelCd=4
Columns=SalDoc.DocDate,SalDoc.SalProcLevelCd,SalDoc.Number

Query mit Filter auf Konstante

Query
Main=SalDoc
MaxRows=All
Filter=SalDoc.DocDate>30.11.2014 and SalDoc.DocDate<01.03.2015 and SalDoc.SalProcLevelCd=SalProcLevel.Invoice
Columns=SalDoc.DocDate,SalDoc.SalProcLevelCd,SalDoc.Number

Zugriff auf name und shortName (Ersatz für Qualifier ! und !!)

Bei GetBo Abfragen kann via die Qualifier ! und !! auf die Bezeichnung (name) und die Kurzbezeichnung (shortName) zugegriffen werden.

Bei Query-Abfragen ist dies nicht möglich. Als Ersatz stehen die Funktionen name() und shortName() zur Verfügung.

Beispiel: Es werden Kontaktplanungseinträge ausgegeben. Zu jedem Kontaktplanungseintrag soll die Bezeichnung des Bearbeitungsstatus ausgegeben werden.

Query

Query
Main=ContactPlan
MaxRows=All
Filter=ContactPlan.AddrNo=1070 and ContactPlan.IsDone=0
Columns=ContactPlan.AddrNo,ContactSort.Name,ContactPlan.Date,ContactPlan.ProcStateCd
Column=Bearb_Status,name(ContactPlan.ProcStateCd)

Soll anstelle der Bezeichnung die Kurzbezeichnung ausgegeben werden, ist shortName() zu verwenden.

Query

Query
Main=ContactPlan
MaxRows=All
Filter=ContactPlan.AddrNo=1070 and ContactPlan.IsDone=0
Columns=ContactPlan.AddrNo,ContactSort.Name,ContactPlan.Date,ContactPlan.ProcStateCd
Column=Bearb_Status,shortName(ContactPlan.ProcStateCd)

Zugriff auf sprachabhängige Attribute

Notiz

Sie können direkt (ohne Funktions-View) auf sprachabhängige Attribute zugreifen. Der Zugriff via Sprach-Qualifikator ist zudem schneller als der Zugriff via Funktions-View.

Der Zugriff auf sprachabhängige Attribute kann via Funktions-Views oder Sprach-Qualifikatoren erfolgen.

Betrachten wir das am Beispiel der Artikelbezeichnungen. Im WS 21000 bzw. im BC 21102 können Sie pro Datensprache drei Artikelbezeichnungen hinterlegen.

Für das Auslesen der Artikelbezeichnungen stehen Funktions-Views zur Verfügung. Wie diese angewendet werden, wird nun am Beispiel der Artikelbezeichnung 1 (Art.Name1) aufgezeigt.

Es wird eine Query-Abfrage auf das BO Art durchgeführt. Folgende Attribute sollen zurückgegeben werden: Artikel-Nr., Suchbegriff 1 und Bezeichnung 1.

Query

Query
Main=Art
MaxRows=All
Columns=Art.Number,Art.Keyword1
Filter=Art.Number="101"
Column=Bez1_Context,Art.Name1

Beachten Sie, dass der Wert von Art.Name1 hier im Kontext des Anwenders zurückgegeben wird. Also z.B. in Deutsch.

Nun sollen zwei zusätzliche Spalten eingefügt werden: Bezeichnung in Deutsch und Bezeichnung in Englisch. Dabei soll für jedes der beiden Attribute die "Sprache genau" abgefragt werden. d.h. ist in der jeweiligen Sprache kein Wert erfasst, soll das Attribut leer zurückgegeben werden. Der Zugriff auf die Artikelbezeichnungen erfolgt über eine Funktions-View.

Notiz

Beachten Sie in diesem Zusammenhang, dass für jede Bezeichnung (Bezeichnung 1 - 3) eine eigene Funktions-View existiert.

Über den Service GetInfoQueryView finden Sie heraus, welche Funktions-Views es gibt. Für die Artikelbezeichnungen stehen folgende drei Funktions-Views bereit: Art_Name1, Art_Name2 und Art_Name3. Über den Service GetInfoQueryViewArg lässt sich nun herausfinden, welche Argumente für das Bilden der Relation auf die Funktions-View benötigt werden. Die Funktions-Views Art_Name1 - 3 benötigen folgende drei Argumente:

Interne Artikelnummer
Sprach-Nr. (Datensprache)
Sprache genau (1=Ja, 0=Nein)

Somit ergibt sich folgende Relation: Related=Alias,Funktions-View(Argumente)

Beispiel (Artikelbezeichnung in englisch, Sprach-Nr=3): Related=Bez_EN,Art_Name1(Art.InternalNo,3,1)

Query (mit Art.Name1 in englisch und deutsch) über Funktions-View:

Query
Main=Art
MaxRows=All
Columns=Art.Number,Art.Keyword1
Filter=Art.Number="101"
Column=Bez1_Context,Art.Name1
Related=Bez_EN,Art_Name1(Art.InternalNo,3,1)
Column=Bez1_EN,Bez_EN.Name1
Related=Bez_DE,Art_Name1(Art.InternalNo,1,1)
Column=Bez1_DE,Bez_DE.Name1

Query mit Art.Name1 in deutsch (Sprache=1) und englisch (Sprache=3) über Sprach-Qualifikatoren:

Query
Main=Art
MaxRows=All
Columns=Art.Number,Art.Keyword1
Filter=Art.Number="101"
Column=Bez1_de,Art.Name1@@1
Column=Bez1_en,Art.Name1@@3

F-Script