OpaccERP logo

F-Script

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
Wichtig
BO-Attributnamen müssen immer "qualified" (d.h. inkl. BO-Name als Prefix) 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
Hinweis
Hinweis
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 Prefix. Beispiel: Anstelle von Addr.LastName wird Firma.LastName verwendet.
Ja
MaxRows=nn/All
Anzahl Datensätze bzw. Resultat-Zeilen.
MaxRows=15
Ja
Filter=FilterExpression
Filter auf das Main-BO.
Filter=Firma.HighParentCompany <> 0
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
Für die Rückgabe gewünschtes 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

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

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

Da Query-Abfragen keine Qualifier erlauben, ist der Zugriff auf sprachabhängige Attribute etwas umständlicher, als bei GetBo-Abfragen.
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 Funktionsviews 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.
Hinweis
Hinweis
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)
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