Programmieren mit dBASE PLUS: SQL-Datenbanken: Einführung in die SQL-Syntax
Umgang mit Feldern, Feldlisten, Feldnamen, Tabellennamen
[Bearbeiten]Umgang mit Feldern, Duplikate entfernen
[Bearbeiten]Alle Adressen zeigen:
SELECT * FROM ADRESSEN
Einschränkung auf bestimmte Felder:
SELECT ID, VORNAME, FIRMA1, FIRMA2, STRASSE, PLZ, ORT FROM ADRESSEN
Verkettung von Feldern mit ||.
SELECT FIRMA1||', '||VORNAME AS NAME FROM ADRESSEN
Hinweis: Bei Feldern mit NULL-Werten wird in Firebird ein leerer Wert zurückgegeben, bei dbf-Tabellen nicht.
Mit DISTINCT werden alle Duplikate entfernt.
SELECT DISTINCT ORT FROM ADRESSEN
Hinweis: Alle doppelten Werte werden nicht angezeigt. Also werden Berlin und Hamburg nur jeweils einmal angezeigt, obwohl sie öfter in der Tabelle vorkommen.
DISTINCT bezieht sich immer auf die Übereinstimmung aller Felder einer Abfrage. Durch das Hinzufügen der weiteren Felder werden auch mehr Sätze angezeigt. Ausgefiltert wird nur, wenn alle drei Felder die gleichen Werte haben!
SELECT DISTINCT FIRMA1, PLZ, ORT FROM ADRESSEN
Maskieren von Feldnamen mit " Durch die Maskierung können Leerzeichen als Feld- und Aliasname verwendet werden. Außerdem können dadurch reservierte Worte der Datenbank als Feld- und Aliasname verwendet werden. (z.B. DATE - Siehe Anhang: Reservierte Wörter") !!! NUR FIREBIRD !!!
SELECT FIRMA1 AS "Firmen Name", ANLAGEDATUM AS "DATE" FROM ADRESSEN
Alias für Tabellen und Felder
[Bearbeiten]Für jede Tabelle und für jedes Feld kann ein Aliasname angegeben werden. Bei Feldern wird der Aliasname dann als Feldname in der Ergebnisliste angezeigt. Tabellen haben automatisch den Tabellennamen als Aliasnamen und Felder können über diesen Aliasnamen referenziert werden (ADRESSE.ID). Bei Mehrfachverwendung der selben Tabellen ist es aber notwendig, jeder Tabelle einen eigenen Aliasnamen zu geben. Indem kurze Namen verwendet werden (z.B. nur einen oder zwei Buchstaben) kann eine Menge Schreibarbeit gespart werden und umfangreiche Abfragen werden übersichtlicher. Wenn mehrere Tabellen verwendet werden, muss vor jedem Feldnamen der Aliasnamen der Tabelle stehen. Dadurch wird sichergestellt, dass die richtigen Felder angesprochen werden (z.B. bei JOINS). Ein Alias kann entweder direkt mit einem Leerzeichen getrennt hinter die Feldbeschreibung oder Tabelle geschrieben werden oder in der Form: AS ALIASNAME.
Anzeigen aller Adressen mit Bankverbindung. Feld Firma1 wird als Name und BLZ als Bankleitzahl angezeigt.
SELECT A.FIRMA1 AS NAME, B.BLZ AS BANKLEITZAHL FROM ADRESSEN A JOIN ADRESSENBANK B ON (B.NR = A.ID)
Mit einem Tabellenalias und dem * können von einer bestimmten Tabelle alle Felder angezeigt werden. Mit einem Komma getrennt, können dann einzelne weitere Felder angegeben werden.
SELECT A.ID AS NUMMER, A.* FROM ADRESSEN A
Umwandlungs-Funktionen in Abfragen
[Bearbeiten]Der SQL-Standard bietet einige rudimentäre Funktionen zur Umwandlung von Daten und Datentypen.
SUBSTRING() UPPER(), LOWER()
[Bearbeiten]Extrahieren eines Teilstrings aus Stringfeldern. Nur die ersten 5 Zeichen des Feldes FIRMA1 anzeigen
SELECT SUBSTRING(FIRMA1 FROM 1 FOR 5) AS NAME FROM ADRESSEN
Mit UPPER() werden Feldwerte in Großbuchstaben umgewandelt. Bei dbf-Tabellen geht auch LOWER() zur Umwandlung in Kleinbuchstaben.
SELECT UPPER(FIRMA1) AS GROSS FROM ADRESSEN
CAST() Umwandlung von Datentypen
[Bearbeiten]Umwandeln eines Datums in einen String
SELECT CAST(ANLAGEDATUM AS CHARACTER(10)) AS DATUM FROM ADRESSEN
Hinweis: Hier ist zu beachten, dass Firebird ein anderes Datumsformat zurückliefert (JJJJ-MM-TT) als dbf-Tabellen (TT.MM.JJJJ).
Umwandeln einer Zahl in einen String
SELECT GESAMT AS ZAHL, CAST(GESAMT AS CHARACTER(10)) AS ZEICHEN FROM RECHNUNG
Hinweis: Bei dbf-Tabellen wird als Dezimalstelle ein Komma verwendet, bei Firebird ein Punkt.
Umwandeln eines String in eine Zahl
SELECT BLZ, CAST(BLZ AS NUMERIC(10)) AS BLZNUMMER FROM ADRESSENBANK
Umwandeln einer Ganzzahl in eine Dezimalzahl mit zwei Stellen.
SELECT ID, CAST(ID AS NUMERIC(10,2)) AS NUMMER FROM ADRESSEN
EXTRACT() Extrahieren von Daten aus Datumsfeldern
[Bearbeiten]Jahr, Monat und Tag aus Datum extrahieren
SELECT EXTRACT(YEAR FROM ANLAGEDATUM) AS JAHR, EXTRACT(MONTH FROM ANLAGEDATUM) AS MONAT, EXTRACT(DAY FROM ANLAGEDATUM) AS TAG FROM ADRESSEN
Hinweis: Auch hier liefert Firebird (Ganzzahl) ein anderes Format zurück als dbf-Tabellen (Dezimalzahl).
Berechnungen, SUB-Selects
[Bearbeiten]Der SQL-Standard hält einige Funktionen zum Zählen und Berechnen von Werten bereit.
Einfache Berechnungen: + - * /
[Bearbeiten]Netto und MwSt.-Betrag addieren:
SELECT NETTO+MWSTBETRAG AS BRUTTO FROM RECHNUNG
Berechnet werden 16% vom Gesamtbetrag. Angezeigt werden beide Felder:
SELECT GESAMT, (GESAMT*16)/100 AS MWST FROM RECHNUNG
Berechnet werden 16% vom Gesamtbetrag und Gesamt + 16% , angezeigt werden Netto, MwSt. und Brutto:
SELECT GESAMT AS NETTO , (GESAMT*16)/100 AS MWST, GESAMT +((GESAMT*16)/100) AS BRUTTO FROM RECHNUNG
Aggregatfunktionen SUM(), COUNT(), MIN(), MAX(), AVG():
[Bearbeiten]Alle Aggregatfunktionen können mit einem Feldnamen, der Option ALL <Feldname> oder DISTINCT <Feldname> ausgeführt werden. Die Angabe eines Feldnamens oder ALL berücksichtigt alle Sätze, die nicht NULL sind. DISTINCT berücksichtigt doppelte Werte jeweils nur einmal. Bei SUM(), AVG(), MIN() und MAX() muss immer ein Feldname angegeben werden, bei COUNT kann auch ein * in der Klammer stehen, dann wird unabhängig von einem Feld gezählt.
Summe Gesamt aller Rechnungen
SELECT SUM(GESAMT) AS GESAMT FROM RECHNUNG
Anzahl aller Einträge in Adressen, mit der Angabe eines Feldes und DISTINCT.
SELECT COUNT(*) AS ANZAHL_GESAMT, COUNT(VORNAME) AS ANZAHL_EXISTIERENDE_VORNAMEN, COUNT(ALL VORNAME) AS MIT_ALL, COUNT(DISTINCT VORNAME) AS VERSCHIEDENE_VORNAMEN FROM ADRESSEN
Durchschnittsbetrag aller Rechnungen und kleinste und größte ID.
SELECT AVG(GESAMT) AS DURCHSCHNITT, MIN(GESAMT) AS KLEINSTER_BETRAG, MAX(GESAMT) AS GROESSTER_BETRAG FROM RECHNUNG
Feldwerte durch SUBSELECT ermitteln
[Bearbeiten]Mit einem sog. SUBSELECT kann eine eigene SELECT-ABFRAGE als Ergebnis für einen Feldwert benutzt werden. Ein SUBSELECT liefert üblicherweise einen Satz und ein Feld zurück. Für jeden Datensatz wird ein SUBSELECT ausgeführt, so dass die Abfrage bei großen Tabellen lange dauern kann. In den meisten Fällen ist das Ergebnis eines SUBSELECTS auch mit einem JOIN zu erreichen, was aus Performancegründen in jedem Fall vorzuziehen ist. Bei Verwendung von dbf-Tabellen ist der Performanceunterschied allerdings nicht so groß. Bei dbf-Tabellen funktioniert die Referenzierung über den Feldnamen im Zusammenhang mit Aggregatfunktionen leider nicht. Hier ist offensichtlich ein Fehler in der Umsetzung des SQL-Befehls vorhanden. Es empfiehlt sich also grundsätzlich, möglichst mit JOINS zu arbeiten.
Anzahl der Aufträge zu jeder Adresse.
SELECT A.FIRMA1, (SELECT COUNT(*) FROM AUFTRAG AU WHERE AU.ADRESSE = A.ID) AS AUFTRAEGE FROM ADRESSEN A ORDER BY A.FIRMA1
Alternativer Aufruf für dbf-Tabellen:
SELECT A.ID, A.FIRMA1, COUNT(AU.ID) AS AUFTRAEGE FROM ADRESSEN A JOIN AUFTRAG AU ON (A.ID = AU.ADRESSE) GROUP BY A.ID, A.FIRMA1 ORDER BY A.FIRMA1
Hinweis: Mit dem SUBSELECT kann ein Ergebnis aus einer beliebigen anderen Abfrage als Wert in die Feldliste eingefügt werden. Dabei ist zu beachten, dass in diesem Fall der SUB-Select nur einen Wert zurückgeben darf.
Ohne Tabellen-Alias ergibt sich ein völlig anderes Ergebnis
SELECT FIRMA1, (SELECT COUNT(*) FROM AUFTRAG WHERE ADRESSE = ID) AS AUFTRAEGE FROM ADRESSEN ORDER BY FIRMA1
Hinweis: Um festzulegen welche Felder verglichen werden sollen, ist es wichtig ALIASE für die Tabellen festzulegen. Im Beispiel wird festgelegt dass das Feld Adresse aus der Tabelle AUFTRAG mit dem Feld ID aus der Adresse verglichen werden soll. Da in der Tabelle AUFTRAG ebenfalls ein Feld ID existiert, käme ohne die Verwendung der ALIASE ein anderes Ergebnis heraus, da nicht klar ist, welches Feld verwendet werden soll.
Ergebnis einschränken mit der WHERE-Klausel
[Bearbeiten]Mit der WHERE-Klausel kann das Ergebnis einer Abfrage eingeschränkt werden. Es stehen die folgenden Operatoren zur Verfügung:
= (Ist gleich) != (Ungleich) < (Kleiner) > (Größer) <> (Ungleich) !< (Nicht kleiner als) !> (Nicht größer als) <= (Kleiner gleich) >= (Größer gleich) IS NULL (NULL-Wert, nicht die Zahl 0 oder Leerzeichen) BETWEEN <val> AND <val> ( Wert liegt zwischen) IN (<val>, <val>, ...) (Vergleichswert ist enthalten) LIKE (Teilweise Übereinstimmung. Kombination mit % und _ als Jokerzeichen) CONTAINING <val> (Enthält den Wert) STARTING WITH <val> (Vergleich: Anfang stimmt überein) CAST(), SUBSTRING(), EXTRACT(), UPPER(), LOWER() (Wie beschrieben)
In der WHERE-Klausel müssen die Filterwerte mit dem Datentyp des jeweiligen Feldes übereinstimmen. Numerische Werte sind ohne Hochkommata anzugeben. Dezimaltrennzeichen ist der Punkt. Datum, Zeit, und Textfelder müssen in einfachen Hochkommata eingeschlossen werden.
Vergleiche mit =, <, >, IS NULL
[Bearbeiten]Alle Adressen mit FIRMA1 = "Hamburg"
SELECT FIRMA1, PLZ, ORT FROM ADRESSEN WHERE FIRMA1 = 'Hamburg'
Bei folgender Abfrage wird "Hamburg" nicht gefunden. Die Vergleiche werden immer CASE-Sensitive ( Groß- und Kleinschreibung wird beachtet) durchgeführt.
SELECT FIRMA1, PLZ, ORT FROM ADRESSEN WHERE FIRMA1 = 'hamburg'
Deshalb ist es besser in der WHERE-Klausel den Feldwert und den Suchwert in Großbuchstaben umzuwandeln:
SELECT FIRMA1, PLZ, ORT FROM ADRESSEN WHERE UPPER(FIRMA1) = 'HAMBURG'
Anzahl Adressen ohne PLZ (NULL-Wert nicht Leerzeichen)
SELECT COUNT(*) AS ANZAHL FROM ADRESSEN WHERE PLZ IS NULL
Anzahl Adressen ohne PLZ (NULL-Wert und Leerzeichen)
SELECT COUNT(*) AS ANZAHL FROM ADRESSEN WHERE PLZ IS NULL OR PLZ =
Alle Adressen mit PLZ kleiner als 60000
SELECT PLZ, ORT FROM ADRESSEN WHERE PLZ < '60000'
Alle Adressen mit PLZ größer als 60000
SELECT PLZ, ORT FROM ADRESSEN WHERE PLZ > '60000'
BETWEEN - Sätze zwischen zwei Werten eingrenzen
[Bearbeiten]Adressen deren PLZ von 60000 bis 70000 liegt. Beide Vergleichswerte sind enthalten.
SELECT PLZ FROM ADRESSEN WHERE PLZ BETWEEN '60000' AND '70000'
Hinweis: Das gleiche Ergebnis würde WHERE PLZ >= '60000' AND PLZ <= '70000' liefern.
IN - Ein Wert aus einer Werteliste ist im Feld vorhanden
[Bearbeiten]Nur Adressen mit den PLZ 25421 oder 20095
SELECT PLZ, FIRMA1 FROM ADRESSEN WHERE PLZ IN ('25421', '20095')
Alle Adressen mit ID 1 oder 60
SELECT ID, FIRMA1 FROM ADRESSEN WHERE ID IN (1,60)
Adressen, zu denen ein Auftrag existiert. Wird ermittelt mit einem SUB-Select
SELECT ID,FIRMA1 FROM ADRESSEN WHERE ID IN (SELECT DISTINCT ADRESSE FROM AUFTRAG)
Hinweis: Mit einem SUB-SELECT wird eine zweite Abfrage gestartet, die als Ergebnis eine Werteliste liefert. Es werden in der Liste dann nur diese Sätze angezeigt. Im SUB-SELECT muss der Feldtyp des abgefragten Feldes dem des Feldes ID entsprechen. Durch die DISTINCT Einschränkung im SUB-SELECT wird gewährleistet, dass jede Adresse nur einmal gezählt wird auch wenn mehrere Aufträge zu der Adresse vorhanden sind.
Diese Variante ist je nach Datenbank sehr langsam wenn große Datenmengen vorhanden sind. Dasselbe Ergebnis kann mit einem JOIN erzielt werden. In Firebird ist die Verwendung eines JOINS in jedem Fall vorzuziehen wo es geht. Bei dbf-Tabellen muss das ausprobiert werden. Mehr zu JOINS später in Kapitel 9.
SELECT DISTINCT A.ID, A.FIRMA1 FROM ADRESSEN A JOIN AUFTRAG AU ON(A.ID = AU.ADRESSE)
LIKE, CONTAINING, STARTING WITH
[Bearbeiten]Alle Adressen, deren Namen mit "HAM" beginnt. Variante 1
SELECT FIRMA1 FROM ADRESSEN WHERE UPPER(FIRMA1) LIKE 'HAM%'
Hinweis: Das "%" Zeichen steht als Platzhalter (Jokerzeichen) für eine beliebige Anzahl beliebiger Zeichen analog zum "*" bei DOS. Der "_" steht als Platzhalter für genau ein beliebiges Zeichen analog zum "?" bei DOS. Die Jokerzeichen funktionieren nur zusammen mit LIKE!
Alle Adressen, deren Namen mit "HAM" beginnt. Variante 2 !!! NUR FIREBIRD !!!
SELECT FIRMA1 FROM ADRESSEN WHERE UPPER(FIRMA1) STARTING WITH 'HAM'
Alle Adressen, deren Namen mit "HAM" beginnt. Variante 3
SELECT FIRMA1 FROM ADRESSEN WHERE UPPER(SUBSTRING(FIRMA1 FROM 1 FOR 3)) = 'HAM'
Hinweis: Alle Varianten liefern das gleiche Ergebnis. Die Varianten 1 und 2 sind aber vorzuziehen, da keine zweite Umwandlung der Felder notwendig ist. Allerdings können in der STARTING-Variante keine Jokerzeichen verwendet werden. Jokerzeichen sind nur bei LIKE möglich.
Alle Adressen, deren Namen "BURG" enthält. Variante 1
SELECT FIRMA1 FROM ADRESSEN WHERE UPPER(FIRMA1) LIKE '%BURG%'
Alle Adressen, deren Namen "BURG" enthält. Variante 2 !!! NUR FIREBIRD !!!
SELECT FIRMA1 FROM ADRESSEN WHERE UPPER(FIRMA1) CONTAINING 'BURG'
Alle Adressen, deren Namen "B*RG" enthalten. Also ein "B" dann ein beliebiges Zeichen und dann "RG"
SELECT FIRMA1 FROM ADRESSEN WHERE UPPER(FIRMA1) LIKE '%B_RG%'
Wenn aber nach dem Zeichen "_" gesucht werden soll, muss dies mit der ESCAPE-Funktion angezeigt werden. Die Option ESCAPE '/' zeigt dem LIKE Befehl an, dass das Zeichen nach dem / nicht als Jokerzeichen interpretiert werden soll. Es kann auch ein beliebiges anderes Maskierungszeichen genommen werden. Sinnvoll ist natürlich ein Zeichen, dass möglichst nicht in der potentiellen Suchbedingung vorkommt.
SELECT FIRMA1 FROM ADRESSEN WHERE UPPER(FIRMA1) LIKE '%/_%' ESCAPE '/'
AND, OR, NOT, Klammersetzung
[Bearbeiten]Es können mehrere WHERE-Klauseln mit OR und AND gekoppelt werden. Mit NOT wird die Umkehrung der Klausel oder der eingeklammerten Klauseln erreicht.
Alle Adressen mit Name "Berlin" oder "Hamburg".
SELECT FIRMA1 FROM ADRESSEN WHERE UPPER(FIRMA1) = 'HAMBURG' OR UPPER(FIRMA1) = 'BERLIN'
Alle außer Hamburg werden selektiert:
SELECT FIRMA1 FROM ADRESSEN WHERE NOT UPPER(FIRMA1) = 'HAMBURG' ORDER BY FIRMA1
Alle außer Hamburg werden selektiert, deren ID kleiner 100 ist. Hier bezieht sich das NOT nur auf die erste Klausel.
SELECT ID, FIRMA1 FROM ADRESSEN WHERE NOT UPPER(FIRMA1) LIKE 'HAMBURG%' AND ID < 100
Klammersetzung
[Bearbeiten]Durch Klammersetzung können mehrere Klauseln zusammengefasst werden. NOT bezieht sich dann auf das Ergebnis der gesamten Klammer. So zeigt die folgende Klammersetzung etwas völlig anderes als vorher an. Nämlich alle Adressen außer Firma1 = Hamburg UND ID < 60.
SELECT ID, FIRMA1 FROM ADRESSEN WHERE NOT (UPPER(FIRMA1) = 'HAMBURG' AND ID < 60)
Gesucht sind alle Adressen die entweder PLZ 25421 oder 20095 haben und FIRMA1 mit H anfängt
SELECT PLZ,ORT,FIRMA1 FROM ADRESSEN WHERE PLZ = '25421' OR PLZ = '20095' AND FIRMA1 LIKE 'H%'
Hinweis: Hier werden alle Adressen angezeigt die 25421 ODER 20095 als PLZ haben und im Feld FIRMA mit "H" anfagen, also nicht das was wir wollten!
Richtig wäre die Abfrage so:
SELECT PLZ,ORT,FIRMA1 FROM ADRESSEN WHERE (PLZ = '25421' OR PLZ = '20095') AND FIRMA1 LIKE 'H%'
Hinweis: Anhand dieses Beispiels wird deutlich, wie wichtig die richtige Klammersetzung ist. Im ersten Beispiel wird durch die OR Klausel der erste Teil vom zweiten Teil getrennt. Der zweite Teil wird als Einheit betrachtet, der zusammen wahr sein muss. Also werden alle Sätze angezeigt, die entweder 25421 als PLZ haben oder 20095 als PLZ haben und ein H am Anfang haben. Im zweiten Beispiel wird des Ergebnis aus den ersten beiden Klauseln zusammengefasst. Nur Sätze, die eines der beiden Kriterien erfüllen werden ausgewertet und dann noch durch das AND nur die Sätze, die ein H am Anfang haben. Grundsätzlich gilt: Alles was zusammen in Klammern steht wird als eine Einheit ausgewertet und muss alle in der Klammer befindlichen Bedingungen erfüllen um ein Ergebnis zu bringen. Die Kombination von beliebig vielen Klammern und AND oder OR Bedingungen ist möglich. Es muss nur sehr genau auf die Position der Klammern geachtet werden.
Noch eine Variante. Hier werden nur Sätze angezeigt die PLZ 20095 haben und FIRMA1 mit H anfängt. Die OR Klausel ist hier völlig sinnlos, da mit der ersten Einschränkung der PLZ nur Sätze vorkommen können, die als PLZ 20095 haben. OR PLZ = '25421' könnte also auch weggelassen werden.
SELECT PLZ,ORT,FIRMA1 FROM ADRESSEN WHERE PLZ = '20095' AND (FIRMA1 LIKE 'H%' OR PLZ = '25421')
Funktionen in der WHERE-Klausel
[Bearbeiten]Berechnungsfunktionen wie SUM(), AVG(), MAX(), COUNT() können nicht direkt in der WHERE-Klausel verwendet werden. (Ausnahme in einem Select in der WHERE-Klausel.) Funktionen wie EXTRACT, SUBSTRING, CAST, UPPER, LOWER können verwendet werden. In Firebird können auch Benutzerdefinierte Funktionen (UDF) verwendet werden. Siehe Kapitel 12.
Alle Adressen, die an einem 26. angelegt wurden
SELECT ANLAGEDATUM, FIRMA1 FROM ADRESSEN WHERE EXTRACT(DAY FROM ANLAGEDATUM) = '26'
Alle Adressen, deren Name mit "HAM" anfängt
SELECT FIRMA1 FROM ADRESSEN WHERE UPPER(SUBSTRING(FIRMA1 FROM 1 FOR 3)) = 'HAM'
SUBSELECT in der WHERE-Klausel
[Bearbeiten]Es ist auch möglich in einer WHERE-Klausel das Ergebnis eines SUB-Selects zu verwenden. Alle Rechnungen deren Gesamtsumme größer-gleich dem Durchschnittswert aller Rechnungen ist:
SELECT ID, GESAMT FROM RECHNUNG WHERE GESAMT >= (SELECT AVG(GESAMT) FROM RECHNUNG)
Die Rechnung mit dem höchsten Rechnungsbetrag soll angezeigt werden.
SELECT ID, GESAMT FROM RECHNUNG WHERE GESAMT = (SELECT MAX(GESAMT) FROM RECHNUNG)
SUBSELECTs können mit verschiedenen Vergleichsoperatoren verwendet werden. EXISTS bedeutet, dass nur Sätze angezeigt werden, bei denen in der Unterabfrage ein Satz existiert. Wieviele Aufträge mit mindestens einer Rechnung gibt es.
SELECT COUNT(*) AS AUFTRAGE FROM AUFTRAG A WHERE EXISTS (SELECT * FROM RECHNUNG R WHERE A.ID = R.AUFTRAG)
Die eben gezeigte Variante ist vergleichsweise langsam. Das gleiche Ergebnis ist mit einem JOIN wesentich schneller. Hier zeigt sich allem bei dbf-Tabellen der Unterschied.
SELECT COUNT(DISTINCT A.ID) AS AUFTRAGE FROM AUFTRAG A JOIN RECHNUNG R ON(R.AUFTRAG = A.ID)
Gruppierung mit GROUP BY
[Bearbeiten]GROUP BY wird verwendet um Zusammenfassungen zu berechnen. Grundsätzlich gilt: Jedes Feld, mit dem keine Berechnung stattfindet, sollte auch in der GROUP BY Klausel stehen. Es werden nur die Sätze zusammengefasst, die in den in der GROUP BY Bedingung angegebenen Feldern gleiche Werte haben. Bei dbf-Tabellen können keine Felder mit Funktionen gruppiert werden. In Firebird geht das. Die Reihenfolge der Gruppierung richtet sich nach der Reihenfolge, in der die Feldnamen in der GROUP-BY Klausel angeordnet sind. Es ist möglich die auszuwertenden Datensätze mit der WHERE-Klausel einzuschränken.
Gruppierung nach Feldern
[Bearbeiten]Summierung der Rechnungsbeträge gruppiert nach Aufträgen:
SELECT AUFTRAG, SUM(GESAMT) AS BETRAG FROM RECHNUNG GROUP BY AUFTRAG
Wenn zwei oder mehr Gruppierungsfelder angegeben werden, wird eine Summe jeweils gebildet, solange alle Gruppierungsfelder identisch sind. Im folgenden Beispiel werden die Aufträge noch getrennt summiert nach Auftragsnummer und Storno (1 oder 0)
SELECT AUFTRAG, STORNO, SUM(GESAMT) AS BETRAG FROM RECHNUNG GROUP BY AUFTRAG, STORNO
Rechnungssumme zu allen Aufträgen, die nach dem 30.11.2005 erstellt wurden
SELECT AUFTRAG, SUM(GESAMT) AS BETRAG FROM RECHNUNG WHERE DATUM >= '01.12.2005' GROUP BY DATUM, AUFTRAG
Rechnungssumme zu allen Aufträgen, die einen größeren Rechnungswert als 1000 haben
SELECT AUFTRAG, SUM(GESAMT) AS BETRAG FROM RECHNUNG WHERE GESAMT >= 1000 GROUP BY AUFTRAG
Gruppierung mit Funktionen (ab Firebird 1.0 / 1.5)
[Bearbeiten]Seit Firebird 1.5 ist es möglich auch UPPER, SUBSTRING EXTRACT und CAST in Gruppierungen zu verwenden. Dazu muss das Feld in der Feldliste die gleiche Funktion haben wie in der GROUP BY Klausel. Es darf aber in der GROUP By Klausel kein Aliasname für das Feld verwendet werden. Dies führt dazu, dass bei umfangreichen Funktionen leicht Fehler passieren. Ab Firebird 2.0 soll es möglich sein, in der GROUP BY Bedingung einfach den ALIAS der Funktion zu verwenden. Auch Gruppierungen mit benutzerdeifinerten Funktionen (UDF) sind möglich. Siehe Kapitel 10.
Im folgenden Beispiel soll angezeigt werden, wie viele Adressen es zu jedem Anfangsbuchstaben gibt.
!!! NUR FIREBIRD !!!
SELECT UPPER(SUBSTRING(FIRMA1 FROM 1 FOR 1)) AS ABC, COUNT(*) AS ANZAHL FROM ADRESSEN GROUP BY UPPER(SUBSTRING(FIRMA1 FROM 1 FOR 1))
Hinweis: Ab Firebird 2.0 geht das auch so: SELECT UPPER(SUBSTRING(FIRMA1 FROM 1 FOR 1)) AS ABC, COUNT(*) AS ANZAHL FROM ADRESSEN GROUP BY ABC.
HAVING - Klausel
[Bearbeiten]Mit HAVING kann eine Abfrage mit Gruppierung eingeschränkt werden.
Aufträge, zu denen Rechnungen im Gesamtwert über 5000 existieren. Bei WHERE wird jeder Satz geprüft, es würden also nur Rechnungen summiert, die Gesamt > 5000 haben. Durch HAVING wird aber die Summe aller Rechungen zu einem Auftrag geprüft.
SELECT AUFTRAG, COUNT(GESAMT) ANZAHL, SUM(GESAMT) SUMME FROM RECHNUNG GROUP BY AUFTRAG HAVING SUM(GESAMT) > 5000
Nun alle Aufträge, deren Rechnungssumme höher ist als der Durchschnittswert aus allen Rechnungen
SELECT AUFTRAG, COUNT(GESAMT) ANZAHL, SUM(GESAMT) SUMME FROM RECHNUNG GROUP BY AUFTRAG HAVING SUM(GESAMT) > (SELECT AVG(GESAMT) FROM RECHNUNG)
Sortierung mit ORDER BY
[Bearbeiten]Mit ORDER BY lassen sich Ergebnismengen sortieren. Durch die Sortierung entsteht ein schreibgeschützter Datensatzbereich, es sei denn für das Sortierfeld ist ein INDEX vorhanden. Es kann auch nach mehreren Feldern sortiert werden. Diese sind dann durch ein Komma zu trennen. Dabei gilt, dass zuerst nach dem ersten Feld sortiert wird, dann nach dem zweiten etc. Sortierungen lassen sich Aufsteigend und Absteigend durchführen. Dabei ist bei jdem Feld bei absteigender Sortierung "DESCENDING"oder für aufsteigende Sortierung "ASCENDING" anzugeben. Default ist ASCENDING, deshalb muss dies nicht zwingend angegeben werden.
ORDER BY
[Bearbeiten]Alle Adressen aufsteigend sortiert nach FIRMA1:
SELECT VORNAME, FIRMA1 FROM ADRESSEN ORDER BY FIRMA1
Alle Adressen absteigend sortiert nach FIRMA1:
SELECT VORNAME, FIRMA1 FROM ADRESSEN ORDER BY FIRMA1 DESCENDING
Ab Firebird 1.5 kann festgelegt werden, wie mit NULL-Werten in der Sortierung umgegangen wird. Mit NULLS FIRST werden alle NULL-Werte zuerst angezeigt. Mit NULLS LAST alle NULL-Werte am Ende. Die Abfrage wird nach Vornamen sortiert und die NULL-Werte werden zuerst angezeigt !!! NUR FIREBIRD !!!
SELECT VORNAME, FIRMA1 FROM ADRESSEN ORDER BY VORNAME NULLS FIRST
Analog dazu: !!! NUR FIREBIRD !!!
SELECT VORNAME, FIRMA1 FROM ADRESSEN ORDER BY VORNAME NULLS LAST
Alle Adressen nach FIRMA1 und innerhalb FIRMA1 nach Vornamen sortieren
SELECT VORNAME, FIRMA1 FROM ADRESSEN ORDER BY FIRMA1, VORNAME
Alle Adressen nach FIRMA1 und innerhalb FIRMA1 nach Vornamen absteigend sortieren
SELECT VORNAME, FIRMA1 FROM ADRESSEN ORDER BY FIRMA1, VORNAME DESCENDING
Tabellen verbinden mit JOIN
[Bearbeiten]Mit JOINS ist es möglich mehrere Tabellen miteinander zu verknüpfen. Im Prinzip bildet ein JOIN die Beziehungen der Tabellen untereinander ab. Hier zeigt sich auch oft, ob das zugrundeliegende Datenmodell stimmt.
INNER JOIN
[Bearbeiten]Der gängigste JOIN ist der INNER JOIN oder auch einfach nur JOIN geschrieben. Beim INNER JOIN werden nur Datensätze angezeigt, die in beiden Tabellen eine Entsprechung haben.
Zu einigen Adressen gibt es Bankverbindungen in einer separaten Tabelle:
SELECT A.FIRMA1, B.BANK, B.BLZ, B.KONTO FROM ADRESSEN A JOIN ADRESSENBANK B ON (B.NR = A.ID)
Hinweis: Als Ergebnis werden alle Adressen angezeigt, die eine Bankverbindung haben. Alle anderen Adressen werden nicht angezeigt. Adressen zu denen es zwei oder mehr Bankverbindungen gibt werden entsprechend häufig angezeigt.
Beispiel mit drei Tabellen. Die mittlere Tabelle bildet das Bindeglied zwischen den äußeren Tabellen. Zu verschiedenen Fahrzeugen gibt es Tankdaten (TANKVERBRAUCH) und dazu verschiedene Verbrauchsmittel (TAB_VERBRAUCH) Es werden alle Tankdaten zu jedem Fahrzeug angezeigt, und das entsprechende Verbrauchsmittel
SELECT V.LITER, T.TEXT, F.TEXT AS FAHRZEUG FROM FAHRZEUG F JOIN TANKVERBRAUCH V ON (V.NR = F.ID) JOIN TAB_VERBRAUCH T ON (T.ID = V.ART)
OUTER JOIN
[Bearbeiten]Mit OUTER JOINS werden ebenfalls Tabellen verknüpft, allerdings werden auch Sätze angezeigt, bei denen keine Übereinstimmungen vorliegen. Es gibt LEFT OUTER JOIN, RIGHT OUTER JOIN und FULL OUTER JOIN. Wie die Namen schon sagen, werden beim LEFT OUTER JOIN alle Sätze der links stehenden Tabelle angezeigt, RIGHT OUTER JOIN alle Sätze der rechten Tabelle und bei FULL OUTER JOIN alle Sätze. Dabei ist es egal ob es in der verknüpften Tabelle einen zugehörigen Satz gibt oder nicht.
Es werden alle Adressen angezeigt, egal ob sie eine Bankverbindung haben oder nicht.
Wenn in der rechten Tabelle keine Entsprechung vorhanden ist, werden leere Felder für Bank, BLZ und Konto angezeigt.
SELECT A.FIRMA1, B.BANK, B.BLZ, B.KONTO FROM ADRESSEN A LEFT OUTER JOIN ADRESSENBANK B ON (B.NR = A.ID)
Bei RIGHT OUTER JOIN werden alle Banksätze angezeigt, egal ob es eine Adresse gibt oder nicht (was natürlich bei gepflegten Daten nicht der Fall sein sollte). Allerdings werden keine Adressen angezeigt, für die es keine Bankverbindung gibt.
SELECT A.FIRMA1, B.BANK, B.BLZ, B.KONTO FROM ADRESSEN A RIGHT OUTER JOIN ADRESSENBANK B ON (B.NR = A.ID) ORDER BY B.BANK
Bei FULL OUTER JOIN werden alle Adressen und alle Banksätze angezeigt, egal ob es Übereinstimmungen gibt. Beachten Sie, dass bei dbf-Tabellen immer das Feld in der Abfrage vorhanden sein muss, nach dem sortiert wird!
SELECT A.FIRMA1, B.BANK, B.BLZ, B.KONTO FROM ADRESSEN A FULL OUTER JOIN ADRESSENBANK B ON (B.NR = A.ID) ORDER BY A.ID
Alternativer Aufruf für dbf-Tabellen:
SELECT A.ID, A.FIRMA1, B.BANK, B.BLZ, B.KONTO FROM ADRESSEN A FULL OUTER JOIN ADRESSENBANK B ON (B.NR = A.ID) ORDER BY A.ID
Wir hatten das Beispiel mit den Tankdaten. Dort wurden keine Fahrzeuge angezeigt, zu denen es keine Tankdaten gibt. Um auch alle Fahzeuge anzuzeigen, die keine Tankdaten haben, muss mit einem OUTER JOIN gearbeitet werden. Dies funktioniert aber nur, wenn beide JOINS LEFT OUTER JOIN sind:
SELECT V.LITER, T.TEXT, F.TEXT AS FAHRZEUG FROM FAHRZEUG F LEFT OUTER JOIN TANKVERBRAUCH V ON (V.NR = F.ID) LEFT OUTER JOIN TAB_VERBRAUCH T ON (T.ID = V.ART)
Im folgenden Beispiel werden alle Verbrauchsdaten nach Fahrzeug und Verbrauchsmittel gruppiert. Hier gibt es also eine Kombination von JOIN und GROUP BY
SELECT SUM(V.LITER), T.TEXT, F.TEXT AS FAHRZEUG FROM FAHRZEUG F LEFT OUTER JOIN TANKVERBRAUCH V ON (V.NR = F.ID) LEFT OUTER JOIN TAB_VERBRAUCH T ON (T.ID = V.ART) GROUP BY F.TEXT, T.TEXT
Natürlich kann bei JOINS auch die WHERE-Klausel verwendet werden um das Ergebnis einzuschränken. Im folgenden Beispiel werden alle Verbrauchsdaten "Diesel" sowie die Kosten nach Fahrzeug und Verbrauchsmittel gruppiert
SELECT SUM(V.LITER) AS LITER, SUM(V.BETRAG) AS KOSTEN, T.TEXT, F.TEXT AS FAHRZEUG FROM FAHRZEUG F LEFT OUTER JOIN TANKVERBRAUCH V ON (V.NR = F.ID) LEFT OUTER JOIN TAB_VERBRAUCH T ON (T.ID = V.ART) WHERE T.TEXT = 'Diesel' GROUP BY F.TEXT, T.TEXT
Mehrere Abfragen zusammenfassen mit UNION
[Bearbeiten]Mit UNION können zwei oder mehr gleichartige SQL-Abfragen zu einem Datensatzbereich zusammengefasst werden. Die Feldnamen müssen dabei nicht gleich sein, aus Übersichtsgründen ist es aber besser. Die Feldtypen müssen aber für alle Abfragen identisch sein; auch in der Länge. Dies lässt sich zum Teil mit CAST oder SUBSTRING hinbiegen.
UNION
[Bearbeiten]Im folgenden Beispiel sollen in einer Tabelle die Summen der einzelnen Verbrauchswerte und die Gesamtsumme aller Verbrauchswerte angezeigt werden:
SELECT CAST('0' AS CHARACTER(1)) AS SORTIERUNG, T.TEXT AS ART, SUM(V.LITER) AS LITER
FROM TANKVERBRAUCH V
JOIN TAB_VERBRAUCH T ON (T.ID = V.ART)
GROUP BY T.TEXT
UNION SELECT CAST('1' AS CHARACTER(1)) AS SORTIERUNG, CAST('Gesamtsumme' AS CHARACTER(30)) AS ART, SUM(V1.LITER) AS LITER
FROM TANKVERBRAUCH V1
Hinweis: Das Feld SORTIERUNG ist eine Krücke um die Datensätze richtig zu sortieren. Ansonsten würde der Satz Gesamtsumme mitten in die anderen Verbrauchsmittel sortiert und das soll ja nicht sein. Da keine Sortierung vorgegeben ist, werden die Daten nach dem ersten Feld sortiert ausgegeben. Ein Grid würde dann so eingestellt, dass das Feld "SORTIERUNG" nicht angezeigt wird.
Mit Union ist es auch möglich völlig unterschiedliche Tabellen zu einem Ergebnis zu kombinieren. In unserem Beispiel gibt es für die Fahrzeuge noch eine Abschreibungstabelle. Um nun die Verbrauchsmittel und die Abschreibung in eine Tabelle zu bekommen, würde sich zuerst ein JOIN anbieten:
SELECT F.TEXT AS FAHRZEUG, SUM(V.LITER) AS LITER, SUM(A.EURO) AS ABSCHREIBUNG FROM FAHRZEUG F JOIN TANKVERBRAUCH V ON(F.ID = V.NR) JOIN ABSCHREIBUNG A ON (F.ID = A.NR) GROUP BY F.TEXT
Hinweis: Das Problem bei dieser Abfrage ist jedoch, dass sich hier die Sätze potenzieren. Für jeweils einen Abschreibungssatz gibt es alle Verbrauchssätze und umgekehrt. Im Ergebnis bedeutet dies völlig utopische Zahlen.
Mit UNION können nun die beiden Tabellen zusammengebracht werden:
SELECT F.TEXT AS FAHRZEUG, SUM(V.LITER) AS LITER, CAST(0 AS NUMERIC(12,2)) AS ABSCHREIBUNG FROM FAHRZEUG F JOIN TANKVERBRAUCH V ON(F.ID = V.NR) GROUP BY F.TEXT UNION SELECT F1.TEXT AS FAHRZEUG, CAST(0 AS NUMERIC(12,2)) AS LITER, SUM(A.EURO) AS ABSCHREIBUNG FROM FAHRZEUG F1 JOIN ABSCHREIBUNG A ON(F1.ID = A.NR) GROUP BY F1.TEXT
Hinweis: Nachteil hierbei ist allerdings, dass es im Ergebnis für jede Art einen eigenen Satz gibt. Mit Firebird könnte dies mit einer Stored Procedure gelöst werden. (Eine Funktion, die auf dem Server direkt programmiert wird)
Die Lösung hierfür besteht in einem JOIN in Verbindung mit einem SUBSELECT. Das Verbindungsfeld F.ID muss auch in der Feldliste vorhanden sein. Leider funktioniert diese Variante nicht mit dbf-Tabellen. !!! NUR FIREBIRD !!!
SELECT F.ID, F.TEXT AS FAHRZEUG, SUM(V.LITER) AS LITER, (SELECT SUM(A.EURO) FROM ABSCHREIBUNG A WHERE F.ID = A.NR) AS ABSCHREIBUNG FROM FAHRZEUG F JOIN TANKVERBRAUCH V ON(F.ID = V.NR) GROUP BY F.TEXT, F.ID
Sonderfunktionen Firebird
[Bearbeiten]Firebird bietet neben den Standard-SQL Funktionen wie SUBSTRING, UPPER etc. noch einige eigene Funktionen, die nicht zum SQL-Standard gehören.
SELECT FIRST SKIP (Ab Firebird 1.0)
[Bearbeiten]Mit FIRST und SKIP ist es möglich nur bestimmte Sätze aus der Ergebnismenge anzuzeigen.
Nur der erste Datensatz wird zurückgegeben:
!!! NUR FIREBIRD !!!
SELECT FIRST (1) ID,FIRMA1 FROM ADRESSEN
Die ersten 5 Sätze werden nicht angezeigt: !!! NUR FIREBIRD !!!
SELECT SKIP (5) ID,FIRMA1 FROM ADRESSEN
Die ersten 5 Sätze werden übersprungen und dann werden die nächsten 10 Sätze angezeigt: !!! NUR FIREBIRD !!!
SELECT FIRST(10) SKIP (5) ID,FIRMA1 FROM ADRESSEN
Hinweis: Diese Funktion nicht in einen SUB-Select im Zusammenhang mit DELETE verwenden, sonst wird evtl. die komplette Tabelle gelöscht. Siehe auch Release-Notes Firebird 1.0
CASE WHEN <Bedingung> THEN <val> ELSE <val> END (ab FB 1.5)
[Bearbeiten]Mit dieser neuen Funktion können Feldwerte schon in der Abfrage manipuliert werden. So kann beispielsweise je nach Bedingung ein anderes Feld angezeigt werden.
Im folgenden Beispiel gibt es zu Aufträgen eine Zuordnung entweder zu einer Anlage (Feld ANLAGE) oder zu einem Fahrzeug (Feld Fahrzeug). Die jeweiligen Stammdaten dazu sind in unterschiedlichen Tabellen gespeichert. Mit der CASE-Funktion ist es nun möglich den jeweils richtigen Wert aus einer der Tabellen im gleichen Feld anzeigen zu lassen.
In diesem Beispiel wird die CASE-Funktion wie die IIF() Funktion in dbase verwendet.
!!! NUR FIREBIRD !!!
SELECT A.ID, CASE WHEN (A.FAHRZEUG IS NULL OR A.FAHRZEUG = 0) THEN AN.TEXT ELSE F.TEXT END AS ZUORDNUNG FROM AUFTRAG A LEFT OUTER JOIN TAB_ANLAGEN AN ON (A.ANLAGE = AN.ID) LEFT OUTER JOIN FAHRZEUG F ON (A.FAHRZEUG = F.ID) ORDER BY A.ID
Es ist aber auch möglich in der CASE-Funtkion mehrere Werte zu prüfen. Für jede zu prüfende Bedingung gibt es eine WHEN-Klausel und am Ende ein ELSE. Das ganze Konstrukt wird mit END abgeschlossen. Im folgenden Beispiel wird zu jedem Auftrag die Abrechnungsart angezeigt. !!! NUR FIREBIRD !!!
SELECT ID,
CASE WHEN ABRECH = 1 THEN 'jährlich'
WHEN ABRECH = 2 THEN 'halbjährlich'
WHEN ABRECH = 3 THEN 'vierteljährlich'
WHEN ABRECH = 4 THEN 'monatlich'
WHEN ABRECH = 5 THEN 'keine'
ELSE 'keine'
END
AS ABRECHNUNG
FROM AUFTRAG ORDER BY ID
Hinweis: Die CASE-Funktion bietet einige Möglichkeiten, wie die Beispiele zeigen. So kann auf Server-Ebene schon einiges verarbeitet werden, was sonst erst später auf der Clientseite programmiert werden müsste.
COALESCE
[Bearbeiten]Mit COALESCE kann eine Reihe von Werten angegeben werden, die nach NULL durchsucht werden. Der erste Wert, der nicht NULL ist wird zurückgegeben.
In der Adressentabelle gibt es vier Felder mit Telefonnummern (TELEFON1 - TELEFON4) Mit der COALESCE-Funktion kann nun eine Telefonnummer angezeigt werden, egal in welchem Feld sich die Telefonnummer befindet. Voraussetzung dafür ist aber dass leere Felder mit NULL belegt sind.
!!! NUR FIREBIRD !!!
SELECT FIRMA1 AS NAME, COALESCE(TELEFON1,TELEFON2,TELEFON3,TELEFON4) AS TELEFON FROM ADRESSEN ORDER BY FIRMA1
Wenn in keinem Feld ein Wert vorhanden ist, kann ein Vorgabewert festgelegt werden. !!! NUR FIREBIRD !!!
SELECT FIRMA1 AS NAME, COALESCE(TELEFON1,TELEFON2,TELEFON3,TELEFON4,'unbekannt') AS TELEFON FROM ADRESSEN ORDER BY FIRMA1
NULLIF
[Bearbeiten]Mit NULLIF kann ein Feld mit einem bestimmten Wert als NULL-Wert zurückgegeben werden.
In unserem obigen Beispiel sind oft die Telefonfelder nicht mit NULL-Werten belegt sondern mit Leerzeichen. Um nun aber trotzdem diese Felder mit der COALESCE-Funktion zu verwenden, kann ein leeres Telefonfeld mit NULLIF in NULL umgewandelt werden:
!!! NUR FIREBIRD !!!
SELECT FIRMA1 AS NAME, COALESCE(NULLIF(TELEFON1,), NULLIF(TELEFON2,), NULLIF(TELEFON3,), NULLIF(TELEFON4,),'unbekannt') AS TELEFON FROM ADRESSEN ORDER BY FIRMA1
Benutzerdefinierte Funktionen in Firebird (UDF)
[Bearbeiten]Neben den fest in Firebird eingebauten Funktionen ist es auch möglich eigene Funktionen zu programmieren. Firebird bringt schon zwei Funktionsbibliotheken mit, in denen viele nützliche Funktionen enthalten sind. Am Beispiel der RTRIM-Funktion soll kurz die Umgangsweise mit Funktionen vorgestellt werden. Im Firebird Programmverzeichnis gibt es das Unterverzeichnis UDF. Dort befinden sich die DLL-Dateien für die UDFs (unter Linux heißen die Dateien *.so). In der ib_udf -Bibliothek gibt es die Funktion rtrim(). Ein TRIM-Befehl ist zur Zeit noch nicht in Firebird integriert. Allerdings wird TRIM in Firebird als reserviertes Wort deklariert, was darauf hindeutet, dass diese Funktion evtl. bald kommen wird. Um die externe Funktion nutzen zu können, muss diese für jede Datenbank einmalig deklariert werden. Hierzu dient der folgende Befehl:
DECLARE EXTERNAL FUNCTION rtrim
CSTRING(255) RETURNS CSTRING(255) FREE_IT ENTRY_POINT 'IB_UDF_rtrim' MODULE_NAME 'ib_udf';
Ist diese Deklaration erfolgt kann die Funktion ab sofort verwendet werden.
Die Beschreibung der Funktion ist jeweils in der zugehörigen .sql Datei enthalten, die ebenfalls im UDF-Verzeichnis liegen. Dort sind auch die Deklarationsanweisungen hinterlegt.
UDF´s können auch in der WHERE-Klausel und seit Version 1.0 auch in der GROUP BY-Klausel verwendet werden.
Beispiel RTRIM() Funktion
[Bearbeiten]Im folgenden Beispiel wird das Feld FIRMA1 getrimt.
SELECT RTRIM(FIRMA1) FROM ADRESSEN
Dies bringt jetzt natürlich kein aussagekräftiges Ergebnis. Interessant wird es, wenn zwei Felder zusammengefasst werden:
SELECT RTRIM(FIRMA1)||', '||RTRIM(VORNAME) AS NAME FROM ADRESSEN
Grid-Bohrer UNION und Subselect
[Bearbeiten]Aufbauend auf den vorhergehenden Beispielen sollen hier vor allem die Möglichkeiten aufgezeigt werden die sich mit SUBSELECTS und UNION bieten. Hauptgrund für deren intensive Verwendung ist auch hier wieder die einfache und übersichtliche Darstellung in Grids, oder die Verwendung als Datengrundlage in Reports. Im Gegensatz zu den bisherigen Beispielen habe ich nicht so sehr auf die Kompatibilität mit dbase-Tabellen geachtet. Ein großer Teil der Abfragen funktioniert so nicht mit dbase-Tabellen. Aber es geht hier vor allem darum, aufzuzeigen, welche Möglichkeiten sich mit UNION bieten. Letztendlich sollen die Beispiele vorwiegend als Denkanstöße und Anregung für eigene Problemstellungen dienen.
Umwandlung von Datentypen mit CAST
[Bearbeiten]Vor allem bei der Verwendung von UNION wird besonders häufig die CAST-Funktion zur Umwandlung von Datentypen benutzt. Deshalb hier zur Erinnerung noch mal kurz die Syntax der CAST-Funktion.
Umwandlung einer Zahl in einen String und Umwandlung eines festen Wertes in einen String.
SELECT CAST(GESAMT AS CHARACTER(10)) AS GESAMT,
CAST('Summe' AS CHARACTER(10)) AS SUMME
FROM RECHNUNG
Umwandlung eines String in eine Zahl
SELECT CAST(PLZ AS NUMERIC(8,2)) AS PLZ FROM ADRESSEN
Verkettung von zwei Werten mit der CAST-Funktion.
SELECT CAST(GESAMT AS CHARACTER(10))||' EUR' AS GESAMT FROM RECHNUNG
SUBSELECTS
[Bearbeiten]SUBSELECTS werden immer dann verwendet, wenn die Aggregatfunktionen, JOIN, GROUP BY etc. nicht ausreichen um das gewünschte Ergebnis zu erzielen. Dabei sollte aber immer zuerst geprüft werden, ob das Ergebnis nicht doch mit den Standardfunktionen erreicht werden kann, denn SUBSELECTS können die Abfrage erheblich verlangsamen, da für jeden Satz eine zusätzliche SQL-Abfrage ausgeführt wird.
Im folgenden Beispiel wird für jeden Kunden der Umsatz ermittelt und gleichzeitig der %-Anteil am Gesamtumsatz angezeigt.
Um den %-Anteil zu errechnen, muss die Gesamtsumme aller Rechnungen ermittelt werden, die dann mit dem Ergebnis der Gruppierung ins Verhältnis gesetzt wird. Hierzu wird in jedem Satz der Gesamtumsatz mit einem SUBSELECT ermittelt.
Mit der Angabe ORDER BY 3 DESC wird das Ergebnis nach dem zweiten Feld (Umsatz) absteigend sortiert.
SELECT A.ID, A.FIRMA1 AS NAME, SUM(R.GESAMT) AS UMSATZ, SUM(R.GESAMT) * 100 / ( SELECT SUM(GESAMT) FROM RECHNUNG ) AS PROZ_ANTEIL FROM RECHNUNG R JOIN AUFTRAG AU ON (R.AUFTRAG = AU.ID) JOIN ADRESSEN A ON (AU.ADRESSE = A.ID) GROUP BY A.ID, A.FIRMA1 ORDER BY 3 DESC
In Firbird können wir durch die Angabe FIRST 10 auch nur die Top-Ten anzeigen lassen.
SELECT FIRST 10 A.ID, A.FIRMA1 AS NAME, SUM(R.GESAMT) AS UMSATZ, SUM(R.GESAMT) * 100 / ( SELECT SUM(GESAMT) FROM RECHNUNG ) AS PROZ_ANTEIL FROM RECHNUNG R JOIN AUFTRAG AU ON (R.AUFTRAG = AU.ID) JOIN ADRESSEN A ON (AU.ADRESSE = A.ID) GROUP BY A.ID, A.FIRMA1 ORDER BY 3 DESC
UNION mit einer Tabelle
[Bearbeiten]Mit UNION werden zwei oder mehrere Abfragen hintereinander gehängt, die aber nur eine Ergebnisliste zurückgeben. Für die Verwendung von Grids ist diese Funktion optimal, denn so können zusätzliche Informationen in einem Grid angezeigt werden. Normalerweise werden mit UNION mehrere Abfragen mit gleichartigen Tabellen kombiniert. UNION kann aber auch zur mehrfachen Abfrage einer einzigen Tabelle verwendet werden.
In einer Verbrauchsliste soll der Verbrauch nach der jeweiligen Kraftstoffart sowie der Gesamtverbrauch aller Kraftstoffarten angezeigt werden.
Hierzu werden zwei SELECT-Abfragen mit UNION zusammengehängt.
Wichtig dabei ist, dass alle Feldtypen und Feldlängen übereinstimmen müssen. Um dies zu erreichen, werden die entsprechenden Werte mit CAST umgewandelt.
SELECT
CAST(T.TEXT AS CHARACTER(30)) AS ART,
CAST(SUM(V.LITER) AS NUMERIC(12,2)) AS LITER
FROM TANKVERBRAUCH V
JOIN TAB_VERBRAUCH T ON (T.ID = V.ART)
GROUP BY T.TEXT
UNION SELECT
CAST('Gesamtsumme' AS CHARACTER(30)) AS ART,
CAST(SUM(V1.LITER) AS NUMERIC(12,2)) AS LITER
FROM TANKVERBRAUCH V1
Das Problem bei der vorigen Abfrage war die Sortierung nach dem ersten Wert. Somit wurde der Satz mit der Gesamtsumme zwischen den anderen Werten angezeigt was nicht gewünscht ist. Um dies zu beeinflussen, wird das erste Feld "Sortierung" als Hilfsmittel verwendet um die Sortierung zu steuern. Im Grid in einem Formular braucht dieses Feld ja dann nicht angezeigt werden.
SELECT CAST('0' AS CHARACTER(1)) AS SORTIERUNG,
CAST(T.TEXT AS CHARACTER(30)) AS ART,
CAST(SUM(V.LITER) AS NUMERIC(12,2)) AS LITER
FROM TANKVERBRAUCH V
JOIN TAB_VERBRAUCH T ON (T.ID = V.ART)
GROUP BY T.TEXT
UNION SELECT CAST('2' AS CHARACTER(1)) AS SORTIERUNG, CAST('Gesamtsumme' AS CHARACTER(30)) AS ART,
CAST(SUM(V1.LITER) AS NUMERIC(12,2)) AS LITER
FROM TANKVERBRAUCH V1
ORDER BY 1, 2
Um das ganze noch übersichtlicher zu gestalten kann noch eine Trennlinie eingefügt werden. Dazu wird ein weiterer SELECT eingefügt mit der Sortierung 1 in dem nur ein Satz aus einer beliebigen Tabelle (Hier wird das mit der Firebird Funktion FIRST 1 erreicht) abgefragt wird. In diesem Satz werden mit der CAST-Funktion die gewünschten Werte eingefügt.
SELECT CAST('0' AS CHARACTER(1)) AS SORTIERUNG, CAST(T.TEXT AS CHARACTER(30)) AS ART,
CAST(SUM(V.LITER) AS NUMERIC(12,2)) AS LITER
FROM TANKVERBRAUCH V
JOIN TAB_VERBRAUCH T ON (T.ID = V.ART)
GROUP BY T.TEXT
UNION SELECT FIRST 1 CAST('1' AS CHARACTER(1)) AS SORTIERUNG,
CAST('------------------------------' AS CHARACTER(30)) AS ART, CAST(0 AS NUMERIC(12,2)) AS LITER
FROM TANKVERBRAUCH V1
UNION SELECT CAST('2' AS CHARACTER(1)) AS SORTIERUNG,
CAST('Gesamtsumme' AS CHARACTER(30)) AS ART,
CAST (SUM(V1.LITER) AS NUMERIC(12,2)) AS LITER
FROM TANKVERBRAUCH V1
ORDER BY 1, 2
Es ist auch möglich mit UNION eine Abfrage zu erstellen, die zwischen bestimmten Teilbereichen einen Trennstrich einfügt. Im Beispiel wird in einer Terminliste bei jedem neuen Tag ein Trennstrich eingefügt. Hierzu wird die selbe Tabelle mit dem selben Filter nochmals im UNION abgefragt. Damit pro Tag nur eine Zeile erscheint wird hier DISTINCT verwendet. Im UNION wird nur das Datum abgefragt und die restlichen Felder bleiben leer bzw. wird der Trennstrich eingefügt. Im Ergebnis sind nun die Sätze für jeden Tag zu sehen sowie für jeden Tag ein Trennsatz. Die Sortierung erfolgt nach Datum und Zeit. Da die Zeit im UNION leer ist wird dieser Satz immer zuerst angezeigt.
SELECT CAST(T.DATUM AS DATE) DATUM,
CAST(T.DATUM_ZEIT AS CHARACTER(5)) DATUM_ZEIT, CAST(T.DATUM_ZEIT_BIS AS CHARACTER(5)) DATUM_ZEIT_BIS, CAST(T.BETREFF AS CHARACTER(100)) BETREFF
FROM TERMIN T
WHERE T.DATUM >= '01.01.2007' AND T.DATUM <= '31.01.2007'
UNION SELECT DISTINCT CAST(T1.DATUM AS DATE) DATUM,
CAST( AS CHARACTER(5)) DATUM_ZEIT,
CAST( AS CHARACTER(5)) DATUM_ZEIT_BIS,
CAST('-------------------------------------------------------------------------------' AS CHARACTER(100)) BETREFF
FROM TERMIN T1
WHERE T1.DATUM >= '01.01.2007' AND T1.DATUM <= '31.01.2007'
ORDER BY 1,2
UNION mit mehreren Tabellen
[Bearbeiten]Mit UNION ist es auch möglich Tabellen mit völlig unterschiedlichen Strukturen zu verknüpfen. Dabei ist es nur wichtig das die Feldtypen und Feldlängen in allen Abfragen übereinstimmen. Um dies zu erreichen wird die Funktion CAST verwendet.
Zu jeder Adresse gibt es Termine und Aufträge. Zur Adresse 1 sollen alle Termine und Aufträge in einer Liste angezeigt werden. Sortierkriterium ist das Datum, dass es in beiden Tabellen gibt.
Zuerst wird der SELECT für die Termine erstellt. Dabei wird das zweite Feld mit dem Text "Termin" erstellt und mit der Uhrzeit verkettet.
Im zweiten SELECT werden die Aufträge abgefragt. Hier wird das zweite Feld mit dem Wert "Auftrag-Nr." gefüllt und mit der Auftragsnummer und der Anlage zu der dieser Auftrag zugeordnet ist (JOIN zur Tabelle TAB_ANLAGEN) verkettet.
Als Ergebnis erhalten wir eine Liste mit allen Terminen und Aufträgen.
SELECT CAST(T.DATUM AS DATE) DATUM,
CAST('Termin: '||T.DATUM_ZEIT||'-'||DATUM_ZEIT_BIS AS CHARACTER(100)) AS INFO
FROM TERMIN T
WHERE T.ID_ADRESSE = 1
UNION SELECT CAST(A.DATUM AS DATE) DATUM,
CAST('Auftrag-Nr. '||A.ID||' - '||TA.TEXT AS CHARACTER(100)) AS INFO
FROM AUFTRAG A
JOIN TAB_ANLAGEN TA ON (A.ANLAGE = TA.ID)
WHERE A.ADRESSE = 1
ORDER BY 1,2
Wenn nun zu der Liste noch die Rechnungsdaten dazukommen sollen, dann wird auch hier die Rechnungstabelle entsprechend zusammen geCASTet.
SELECT CAST(T.DATUM AS DATE) DATUM,
CAST('Termin: '||T.DATUM_ZEIT||'-'||DATUM_ZEIT_BIS AS CHARACTER(100)) AS INFO
FROM TERMIN T
WHERE T.ID_ADRESSE = 1
UNION SELECT CAST(A.DATUM AS DATE) DATUM,
CAST('Auftrag-Nr. '||A.ID||' - '||TA.TEXT AS CHARACTER(100)) AS INFO
FROM AUFTRAG A
JOIN TAB_ANLAGEN TA ON (A.ANLAGE = TA.ID)
WHERE A.ADRESSE = 1
UNION SELECT CAST(R.DATUM AS DATE) DATUM,
CAST('Rechnung-Nr. '||R.ID||' - '||R.GESAMT||' EUR' AS CHARACTER(100)) INFO
FROM RECHNUNG R
JOIN AUFTRAG AU ON (AU.ID = R.AUFTRAG)
WHERE AU.ADRESSE = 1
ORDER BY 1,2
Farbige Grids
[Bearbeiten]In Grids wäre es manchmal wünschenswert, wenn einzelne Zeilen andersfarbig angezeigt werden können. Zwar stellt dbase im Grid eine beforeCellPaint und onCellPaint Funktion bereit, mit der auch die Zellenfarbe beeinflußt werden kann. Diese sind aber nicht sehr komfortabel zu bedienen. Außerdem wird bei der Zuweisung einer anderen Farbe für eine Zelle auch der Markierungsbalken in dieser Spalte nicht mehr angezeigt (nur bei Versionen vor dBase 2.6). Seit dbase 2.5 ist es möglich, in einem Grid ein Editorfeld zu definieren. Da dbase in Editorfeldern rudimentäre HTML-Funktionen darstellen kann, kann man einen SQL mit der CAST-Funktion und Verkettungen so hinbasteln, dass auch farbiger Text etc. angezeigt werden kann. Da die HTML-Darstellung in Grids zuweilen recht träge ist, sollte man die Performance vorher ausgiebig testen.
Grundlage ist wieder der UNION-SQL mit den Terminen, Aufträgen und Rechnungen. Nur werden jetzt die Termine grün und Fett, die Aufträge rot und die Rechnungen blau und kursiv angezeigt.
Bei der Zusammenstellung des CAST muss man beachten, dass die Länge des Feldes ausreichend ist. Die Länge muss mindestens die Länge aller Felder, des Textes und der HTML-Zeichen sein.
SELECT CAST(T.DATUM AS DATE) DATUM,
CAST('<font color=green><b>Termin: '||T.DATUM_ZEIT||'-'||DATUM_ZEIT_BIS||'</b></font>' AS CHARACTER(200)) AS INFO
FROM TERMIN T WHERE T.ID_ADRESSE = 1
UNION SELECT CAST(A.DATUM AS DATE) DATUM,
CAST('<font color=red>Auftrag-Nr. '||A.ID||' - '||TA.TEXT||'</font>' AS CHARACTER(200)) AS INFO
FROM AUFTRAG A JOIN TAB_ANLAGEN TA ON (A.ANLAGE = TA.ID)
WHERE A.ADRESSE = 1
UNION SELECT CAST(R.DATUM AS DATE) DATUM,
CAST('<i><font color=blue>Rechnung-Nr. '||R.ID||' - '||R.GESAMT||' EUR'||'</font></i>' AS CHARACTER(200)) AS INFO
FROM RECHNUNG R JOIN AUFTRAG AU ON (AU.ID = R.AUFTRAG)
WHERE AU.ADRESSE = 1 ORDER BY 1,2
Die Möglichkeiten mit HTML-Tags erweitern sich bei Firebird noch deutlich mehr, wenn die Funktion CASE verwendet wird. Mit CASE kann in Abhängigkeit eines Feldwertes ein jeweils anderes Ergebnis ausgegeben werden.
Im Beispiel werden alle Termine die nach dem 31.1.2007 liegen blau angezeigt, alle Termine zwischen dem 1.1. und 31.1.2007 rot und alle Termine vor dem 1.1.2007 schwarz.
SELECT CAST(T.DATUM AS DATE) DATUM, CASE WHEN DATUM > '31.01.2007'
THEN CAST('<font color=blue><b>Termin: '||T.DATUM_ZEIT||'-'||DATUM_ZEIT_BIS||'</b></font>' AS CHARACTER(200))
WHEN DATUM >= '01.01.2007' AND DATUM <= '31.01.2007'
THEN CAST('<font color=red><b>Termin: '||T.DATUM_ZEIT||'-'||DATUM_ZEIT_BIS||'</b>' AS CHARACTER(200))
ELSE CAST('<font color=black><b>Termin: '||T.DATUM_ZEIT||'-'||DATUM_ZEIT_BIS||'</b></font>' AS CHARACTER(200)) END AS INFO
FROM TERMIN T WHERE T.ID_ADRESSE = 1 ORDER BY 1 DESC