Query - Set-Funktionen

Zählen und gruppieren von Datensätzen mit den Funktionen count() und countDistinct()

Falls keine Relation angegeben wird, liefert die Funktion die Anzahl passender Rows einer Query-Abfrage zurück.

Wird eine Relation durch ihren Namen spezifiziert, liefert die Funktion die entsprechende Anzahl Rows zurück.

count() ohne Relation

Beispiel: Anzahl Mitarbeiter (Empl) feststellen.

Query

Query
Main=Empl
Column=Anzahl_Mitarbeiter,count()
MaxRows=1

Resultat

Anzahl_Mitarbeiter
30

Abfragen dieser Art lassen sich auch mit Filtern kombinieren.

Beispiel: Anzahl Mitarbeiter (Empl) deren Adresse (Addr) nicht passiv ist.

Query

Query
Main=Empl
Column=Anzahl_Mitarbeiter,count()
MaxRows=1
Filter=Addr.IsPassive=0

Resultat

Anzahl_Mitarbeiter
25

Beispiel: Anzahl Adressen pro Land abfragen.

Query

Query
Main=Addr
MaxRows=All
Columns=Addr.CountrySc
Column=Anzahl_pro_Land,count()

Resultat

Addr.CountrySc	Anzahl_pro_Land
AT	1
CH	1134
DE	9
F	3
FL	2

count([Relation.Attribut]) mit Relation

Beispiel: Es sollen zu einer Adresse (Adress Nr.) jeweils alle Kontaktpersonen ausgegeben werden. Auf jeder Kontaktperson soll zudem angegeben werden, wie viele Kontaktpersonen auf der Adresse total erfasst sind. Dafür wird nun count() mit einer Relation verwendet.

Query

Query
Main=Addr
MaxRows=All
Columns=Addr.Number
Related=Kontakt,Contact,All,,Kontakt.AddrNo = Addr.Number
Filter=Addr.Number>1015 and Addr.Number<1022
Columns=Kontakt.Number,Kontakt.LastName,Kontakt.FirstName
Column=Anzahl_Kontakte,count(Kontakt)

Resultat

Addr.Number	Kontakt.Number	Kontakt.LastName	Kontakt.FirstName	Anzahl_Kontakte
1016	1	Hügli	Hanspeter	3
1016	3	Klauser	Herbert	3
1016	2	Mäder	Guido	3
1019				0
1020	1	Menzi	Heinrich	1

countDistinct([Relation.Attribut])

Beispiel: Basierend auf Verkaufspositionen sollen die Anzahl der Positionen sowie die Anzahl der Dokumente ermittelt werden.

Query

Query 
Main=SalDocItem 
MaxRows=ALL 
Column=SalDocItemCount,count() 
Column=SalDocCount,countDistinct(SalDocItem.SalDocInternalNo)

Resultat

SalDocItemCount	SalDocCount
1350	428

min(number), max(number) und avg(number)

Mit den Set-Funktionen min(number), max(number) und avg(number) können numerische Attribute auf Minimal-, Maximal- und Durchschnittswert geprüft werden.

min(number) - Bestimmt den Minimalwert mehrerer Zahlen. (Eventuell vorhandene NULL-Werte werden ignoriert.)
max(number) - Bestimmt den Maximalwert mehrerer Zahlen. (Eventuell vorhandene NULL-Werte werden ignoriert.)
avg(number) - Bestimmt den Durchschnittswert mehrerer Zahlen. (Eventuell vorhandene NULL-Werte werden ignoriert.)

Beispiel: Auf Rechnungen sollen bestimmte Artikel geprüft werden. Von interesse sind dabei die fakturierten Preise.

Query

Query
Main=SalDocItem
MaxRows=All
Columns=SalDocItem.ArtNo
Filter=SalDoc.SalProcLevelCd=4 and SalDoc.DocDate>31.12.2013 and SalDocItem.ArtNo="CRANBERRY"
Column=,SalDocItem.SalesPrice

Resultat

SalDocItem.ArtNo	SalDocItem.SalesPrice
CRANBERRY	78.7037
CRANBERRY	78.7037
CRANBERRY	78.7037
CRANBERRY	78.7037
CRANBERRY	18.75
CRANBERRY	18.75
CRANBERRY	78.7037
CRANBERRY	78.7037
CRANBERRY	85
CRANBERRY	85
CRANBERRY	85
CRANBERRY	78.7037
CRANBERRY	18.75
CRANBERRY	18.75
CRANBERRY	18.75

Nun sollen Maximal-, Minimal- und Durchschnittspreis ausgegeben werden.

Query

Query
Main=SalDocItem
MaxRows=All
Columns=SalDocItem.ArtNo
Filter=SalDoc.SalProcLevelCd=4 and SalDoc.DocDate>31.12.2013 and SalDocItem.ArtNo="CRANBERRY"
Column=MaxPrice,max(SalDocItem.SalesPrice)
Column=MinPrice,min(SalDocItem.SalesPrice)
Column=AvgPrice,avg(SalDocItem.SalesPrice)

Resultat

SalDocItem.ArtNo	MaxPrice	MinPrice	AvgPrice
CRANBERRY	85	18.75	59.978393

Soll dazu noch die Anzahl ausgewerteter Positionen ausgegeben werden, kommt wieder die Funktion count() zur Verwendung.

Query

Query
Main=SalDocItem
MaxRows=All
Columns=SalDocItem.ArtNo
Filter=SalDoc.SalProcLevelCd=4 and SalDoc.DocDate>31.12.2013 and SalDocItem.ArtNo="CRANBERRY"
Column=AnzPos,count()
Column=MaxPrice,max(SalDocItem.SalesPrice)
Column=MinPrice,min(SalDocItem.SalesPrice)
Column=AvgPrice,avg(SalDocItem.SalesPrice)

exists([Relation])

Mit der Set-Funktion exists([Relation]) kann überprüft werden, ob durch die übergebene Relation mindestens eine Row zurückgegeben wird.

Wichtig

Im Gegensatz zu den anderen Set-Funktionen hat exists() keine gruppierende Wirkung.

Abfragen über exists() sind viel performanter als das Arbeiten mit Filter-Funktionen.

Betrachten wir das am Beispiel von Adressen und deren Erweiterungen (Kunde, Lieferant, Mitarbeiter). Sind auf einer Adresse eine oder mehrere Erweiterungen erfasst, so existiert eine 1:1 Relation von der Adresse auf die entsprechende Erweiterung. Wenn nun innerhalb einer Adress-Abfrage geprüft werden soll ob Erweiterungen aktiv sind, dann wird dies am besten über die Set-Funktion exists() gemacht.

Anforderung: Ausgeben einer Adressliste. Zusätzlich soll ein virtuelles Attribut ausgegeben werden, welches die aktiven Adress-Erweiterungen darstellt.

In einem ersten Schritt, soll geprüft werden, ob die aktuelle Adresse auch ein Kunde ist. Wenn ja, dann wird im virtuellen Attribut "Ja" ausgegeben, sonst "Nein.

Query

Query
Main=Addr
MaxRows=All
Filter=Addr.Number>999 and Addr.Number<1022
Columns=Addr.Number,Addr.LastName,Addr.FirstName
Column=Kunde,if(exists(Cust)=1,"Ja","Nein")

Nun sollen alle drei Erweiterungen (Kunde, Lieferant, Mitarbeiter) geprüft werden. Anstelle von "Ja" oder "Nein" soll bei aktiver Erweiterung "K", "L" oder "M" ausgegeben werden. Damit nur ein Attribut (Column) definiert werden muss, werden die Ausgaben mit einem Plus-Zeichen verbunden.

Query

Query
Main=Addr
MaxRows=All
Filter=Addr.Number>999 and Addr.Number<1022
Columns=Addr.Number,Addr.LastName,Addr.FirstName
Column=Kunde,if(exists(Cust)=1,"K","-") + if(exists(Suppl)=1,"L","-") + if(exists(Empl)=1,"M","-")

F-Script