Die SELECT-Anweisung

Brandeis Consulting

Die SELECT-AnweisungJörg Brandeis

Brandeis ConsultingDie SELECT-Anweisung…

▪ Ist die am häufigsten verwendete SQL-Anweisung überhaupt

▪ Ist nicht SQLScript-Spezifisch

▪ Wird von ABAP-Entwicklern in OpenSQL meistens nur in relativ einfacher Form

verwendet

▪ Ist extrem mächtig

▪ Kann sehr komplex werden

➔ Wir behandeln die SELECT-Anweisung, auch wenn Sie vielleicht schon bekannt

ist.

18.07.2018 SQLScript für BW-Berater 2

Brandeis ConsultingSELECT Anweisung vs. SELECT Abfrage

Eine SELECT-Anweisung beginnt mit dem Schlüsselwort SELECT und endet mit

einem Semikolon.

Eine SELECT-Abfrage ist ein Tabellenausdruck, der in anderen SQL-Anweisungen

verwendet wird. Z.B. als Unterabfrage.


Brandeis ConsultingDer Syntax der SELECT-Anweisung - Übersicht

SELECT <Feldliste>

FROM-Klausel

[WHERE <WHERE-Bedingung>]

[[GROUP BY <Gruppierung>

[HAVING <Having-Bedingung>]]

[ORDER BY <Sortierung>]

[LIMIT <Begrenzung>]


Brandeis Consulting

Feldliste

• Welche Felder/Spalten sind im Ergebnis der Ausgabe

WHERE Bedingung

• Welche Daten aus der Quelle sollen berücksichtigt werden

Aggregation

• Wie sollen die Daten zusammengefasst werden?

Wo sollen die Daten herkommen?

• FROM Klausen mit Tabellenausdrücken

SELECT


Brandeis ConsultingWas liefert eine SELECT-Abfrage zurück

Die Spalten einer SELECT-Abfrage werden durch die Feldliste festgelegt. In ABAP

steht hier häufig * für alle Spalten der Quelle.

Die Zeilen werden durch die Quellen in der FROM-Klausel und durch die WHERE-

Bedingung festgelegt. Gelegentlich auch durch GROUP BY und HAVING-Klausel


Brandeis ConsultingDie Feldliste

In der Feldliste können alle möglichen skalaren Ausdrücke stehen:

▪ Spaltennamen der Tabellen aus der FROM-Klausel

▪ Operatorausdrücke, z. B. für Berechnungen oder zum Verknüpfen von

Zeichenketten

▪ CASE-Ausdrücke für Fallunterscheidungen und Bedingungen

▪ Funktionsaufrufe

▪ Variablennamen

▪ Konstante Werte

▪ Unterabfragen mit SELECT

▪ Aggregatfunktionen


Brandeis ConsultingFeldnamen einer Abfrage

Jedes Feld in der Feldliste hat einen Namen. Dieser ist entweder

▪ mit AS explizit vergeben worden

▪ 1:1 aus dem Quellfeld übernommen, falls es sich um eine einfache Projektion

handelt

▪ Eine Zeichenkette mit dem Ausdruck des Feldes


SELECT id,

plan_aufwand - ist_aufwand AS restschaetzung,

(100-fertigstellung) / 100 * plan_aufwand

FROM aufgaben

Brandeis ConsultingFeldnamen einer Abfrage – Beispiel


SELECT aufgaben.id,

plan_aufwand - ist_aufwand AS restschaetzung,

(100-fertigstellung) / 100 * plan_aufwand

FROM aufgaben

Brandeis ConsultingEinzelne Spalten aus Quelltabellen

Wenn der Spaltenname eindeutig ist, braucht kein Korrelationsname mit

angegeben werden

Wenn immer der Korrelationsname angegeben wird, ist der Code besser zu lesen.


SELECT tab.table_name, --Spalte table_name nicht eindeutig!

col.column_name

FROM m_cs_tables AS tab

INNER JOIN m_cs_columns AS col

ON tab.table_name = col.table_name;

Brandeis ConsultingAlle Spalten der Quellen

Wie in ABAP kann man auch in SQLScript mit * alle Spalten aller Quellen

übernehmen. Oder über den Korrelationsnamen nur alle Spalten einer Quelle:


SELECT t1.* ,

t2.spalte1

FROM tabelle1 AS t1

JOIN tabelle2 AS t2

ON t1.key = t2.key;

Brandeis ConsultingOperatorausdrücke in der Feldliste - DEMO


SELECT id || ' - ' || titel AS Aufgabe,

plan_aufwand,

ist_aufwand,

round(ist_aufwand / plan_aufwand * 100, 0)

|| ' %' AS zeitverbrauch,

fertigstellung || ' %' AS fertigstellung

FROM aufgaben;

Brandeis ConsultingCASE-Ausdrücke in der Feldliste

Ein CASE-Ausdruck gibt genau eine Wert zurück. Es gibt zwei Varianten:

▪ Der einfache CASE-Ausdruck vergleicht den Wert eines Ausdrucks mit N

anderen Ausdrücken auf Gleichheit.

▪ Der komplexe CASE-Ausdruck wertet N unabhängige Bedingungen aus:


CASE spalte1

WHEN 'A' THEN 1

WHEN 'B' THEN 2

END AS spaltenname,

CASE

WHEN spalte1 = 'A' and spalte2 = 'X' THEN 1

WHEN spalte3 + spalte4 < 42 THEN 2

ELSE 0

END AS spaltenname,

Brandeis ConsultingCASE als IF


CASE WHEN wert2<>0 THEN wert1/wert2

ELSE 0

END AS division

Brandeis ConsultingFunktionsaufrufe in der Feldliste

▪ SQL-Funktionen

▪ UDFs


SELECT get_parcel_price(width, height, depth, weight )

FROM parcels;

SELECT id,

faelligkeit,

DAYS_BETWEEN( faelligkeit , current_date ) as overdue

FROM aufgaben;

Brandeis ConsultingAggregatausdrücke

Die Werte von mehreren Zeilen werden durch eine Aggregatfunktion

zusammengefasst. Z.B. SUM, COUNT, AVG, MAX, MIN…

Alle Spalten, die nicht aggregiert werden, müssen in der GROUP BY-Klausel

angegeben werden (dazu später mehr).

NULL wird in den Aggregaten nicht berücksichtigt. So berechnet AVG

beispielsweise den Durchschnittswert aller nicht-NULL Werte


Brandeis ConsultingSTRING_AGG

Die Aggregatfunktion STRING_AGG( <Ausdruck>, [<Trennzeichen>]) verkettet

Zeichenketten


SELECT sprache,

string_agg(team_text, ', ')

FROM team_text

GROUP BY sprache

Brandeis ConsultingUnterabfragen in der Feldliste


▪ Es sind nur skalare Abfragen erlaubt. D.h. das Ergebnis besteht aus einer Zeile

und einer Spalte

▪ Falls keine Zeile geliefert wird, ist das Ergebnis NULL

▪ Falls mehrere Zeilen geliefert werden, gibt es einen Laufzeitfehler

▪ Die Unterabfragen können korreliert sein. D.h. sie können sich auf Felder der

umgebenden Abfragen beziehen.

▪ Häufig kann man Unterabfragen in der Feldliste auch als Join umformulieren.

Brandeis ConsultingUnterabfragen in der Feldliste


SELECT

schema_name,

(SELECT

COUNT(*)

FROM tables

WHERE schema_name=schemas.schema_name)

AS table_count,

(SELECT

COUNT(*)

FROM procedures

WHERE schema_name=schemas.schema_name)

AS procedure_count,

schema_owner

FROM schemas;

Brandeis ConsultingWindow Functions

Window Functions ähneln den Aggregatfunktionen. Allerdings werden die Daten

nicht verdichtet. Es können aber Berechnungen innerhalb einer Partition

(=Gruppierung) durchgeführt werden.

Innerhalb der Partition kann es eine Sortierung geben, auf die sich die Funktionen

beziehen. Die Möglichkeiten sind sehr umfangreich. Im Folgenden dazu ein paar

Beispiele.


Brandeis ConsultingCOUNT als Window Function vs. Aggregat Function


--Aggregatfunktion COUNT

SELECT status,

COUNT(*) AS zaehler

FROM aufgaben

GROUP BY status

ORDER BY status;

--Window Function COUNT

SELECT id,

status,

COUNT(*) OVER (PARTITION BY status) AS zaehler

FROM aufgaben

ORDER BY id;

Brandeis ConsultingWindow Functions – Beispiele (1/3)

Zu jeder Aufgabe pro Bearbeiter den Vorgänger in Bezug auf Fälligkeit finden:


SELECT

BEARBEITER,

ID,

LAG(ID) OVER (PARTITION BY BEARBEITER ORDER BY FAELLIGKEIT) AS VORGAENGER

FROM AUFGABEN

ORDER BY BEARBEITER


Zu jeder Aufgabe die Anzahl der Vorgänger-Aufgaben für diesen Bearbeiter

ermitteln:


SELECT

bearbeiter,

id,

count(*) OVER (PARTITION BY bearbeiter ORDER BY faelligkeit) AS "#Vorgänger"

FROM aufgaben

ORDER BY bearbeiter,

faelligkeit


Der Planaufwand in Summe vor jeder Aufgabe und nach jeder Aufgabe pro

Bearbeiter:


SELECT

BEARBEITER,

ID,

FAELLIGKEIT,

PLAN_AUFWAND,

SUM(PLAN_AUFWAND) OVER (PARTITION BY BEARBEITER

ORDER BY FAELLIGKEIT

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS INCL_DAVOR,

SUM(PLAN_AUFWAND) OVER (PARTITION BY BEARBEITER

ORDER BY FAELLIGKEIT

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING )

- PLAN_AUFWAND

AS DANACH

FROM AUFGABEN

ORDER BY BEARBEITER,

FAELLIGKEIT

Brandeis ConsultingWindow Functions - Arten

▪ Aggregation – SUM, MAX, MIN, MEDIAN usw.

▪ Rang / Platznummer in der Partition gemäß Sortierung RANK, DENSE_RANK

▪ Position in der Partition (eindeutig im gegensatz zu RANK) – ROW_NUMBER

▪ Einzelner Datensatz davor oder danach, auch mit OFFSET – LAG und LEAD

▪ Statistische Funktionen für Glättung von Zahlenreihen oder den gleitenden

Durchschnitt


Brandeis ConsultingWindow Functions - Anmerkungen

▪ Window Functions werden immer auf der Row-Engine ausgeführt


Brandeis ConsultingFROM-Klausel

SELECT ... FROM <Tabellenausdruck>

Der Tabellenausdruck ist entweder:

▪ Datenbanktabellen, Tabellenvariablen oder Views

▪ Joins

▪ Tabellen-UDFs

▪ Unterabfragen

▪ Mit UNNEST aufgelöste Arrays


Brandeis ConsultingTabellenausdrücke

Tabellenausdrücke können auch wiederum Tabellenausdrücke enthalten:

SELECT *

FROM

(SELECT id,

status

FROM aufgaben)

AS a

LEFT OUTER JOIN

(SELECT id,

status_text

FROM status_text

WHERE sprache = 'DE' )

AS st

ON a.status = st.id


Brandeis ConsultingFROM Datenbanktabelle, Tabellenvariable oder View

▪ Einfache Notation nach Möglichkeit

▪ BW-Tabellen müssen in der speziellen Notation "/BIC/AMXXD602002"

angegeben werden, da der Schrägstrich im Namensraumpräfix nicht

kompatibel mit der einfachen Notation ist.

▪ Es können auch Calculation Views verwendet werden.


Brandeis ConsultingFROM JOIN

Joins verbinden die Daten von zwei Tabellenausdrücken zu einem neuen

Tabellenausdruck. Der Join hat mehrere Aufgaben:

▪ Hinzufügen von Informationen aus anderen Tabellen

▪ Filtern von Datensätzen

▪ Ausmultiplizieren von Datensätzen


Brandeis ConsultingJOIN Arten

▪ CROSS JOIN

▪ INNER JOIN

▪ LEFT/RIGHT OUTER JOIN

▪ FULL OUTER JOIN

Interessant: http://wikis.gm.fh-koeln.de/wiki_db/index.php?n=Datenbanken.Join-

Typ-SQL#Theta-Join


http://wikis.gm.fh-koeln.de/wiki_db/index.php?n=Datenbanken.Join-Typ-SQL#Theta-Join

Brandeis ConsultingCROSS JOIN

Ein Cross Join bildet das kartesische Produkt (Kreuzprodukt) von zwei

Tabellenausdrücken.

Statt dem Schlüsselwort CROSS JOIN kann auch ein Komma verwendet werden.

Das reduziert die Lesbarkeit aber erheblich.


SELECT ...

FROM <Tabellenausdruck1> CROSS JOIN <Tabellenausdruck2>

SELECT ...

FROM <Tabellenausdruck1> , <Tabellenausdruck2>

Brandeis ConsultingCROSS JOIN Beispiel


--Kreuzprodukt mit CROSS JOIN

SELECT *

FROM farben

CROSS JOIN groessen;

--Kreuzprodukt nur mit Komma in der FROM-Klausel

SELECT *

FROM farben,

groessen;

Brandeis ConsultingINNER JOIN

Beim INNER JOIN gibt es eine Join-Bedingung, die mit ON eingeleitet wird. Nur

Datensätze, die auf Grund dieser Bedingung einen Partner in der jeweils anderen

Tabelle finden, werden Teil der Ergebnismenge.


Brandeis ConsultingINNER JOIN – Beispiel


SELECT *

FROM belege AS b

INNER JOIN waehrungen AS w

ON b.waehrung = w.waehrung;

Brandeis ConsultingJOIN-Bedingungen

▪ JOIN-Bedingungen können beliebige Ausdrücke und Vergleichsoperatoren

nutzen

▪ Wenn nur auf Gleichheit von Feldern geprüft wird, spricht man von einem Equi-

Join. Dieser ist erheblich schneller, als ein Non-Equi-Join. Z.B. wenn beim

Nachlesen davon Stammdaten die Zeitdimension mit berücksichtigt werden

muss, wird der Join wesentlich langsamer.


Brandeis ConsultingLEFT OUTER JOIN

Alle Datensätze aus der linken Tabelle erscheinen (mindestens ein mal) in der

Ergebnismenge. Falls die JOIN-Bedingung zutrifft, werden die entsprechenden

Datensätze aus der rechten Tabelle hinzugezogen. Wo kein Treffer gefunden

wurde, werden die Felder mit NULL gefüllt.


Brandeis ConsultingLEFT OUTER JOIN - Beispiel


SELECT *

FROM belege AS b

LEFT OUTER JOIN waehrungen AS w

ON b.waehrung = w.waehrung

Brandeis ConsultingRIGHT OUTER JOIN

Wie LEFT OUTER JOIN, nur mit vertauschten Rollen…


SELECT *

FROM belege AS b

RIGHT OUTER JOIN waehrungen AS w

ON b.waehrung = w.waehrung

Brandeis ConsultingFULL OUTER JOIN

Alle Datensätze von beiden Tabellen werden in der Ergebnismenge auftauchen.


Brandeis ConsultingJOIN CARDINALITY (HANA 2.0 SPS03)

The join cardinality gives a hint to the SQL processing for better performance.

Incorrect cardinalities can lead to incorrect results.

Syntax:

SELECT … FROM <table1>

[INNER|[LEFT|RIGHT]OUTER] [<left side> TO <right side>] JOIN <table2>

Allowed values at both sides:

▪ MANY – 0..N

▪ ONE – 0..1

▪ EXACT ONE – 1

Blog von Jan Zwickel: https://blogs.sap.com/2017/10/27/join-cardinality-setting-in-calculation-views/


Brandeis ConsultingWHERE und ON-Bedingungen

▪ WHERE und ON-Bedingung können jeweils die Datenmenge einschränken.

▪ Diese Einschränkungen werden zu unterschiedlichen Zeitpunkten ausgewertet:

▪ Die Einschränkungen der ON-Bedingung werden schon vor dem Ausführen des Joins

berücksichtigt

▪ Einschränkungen der WHERE-Bedingung werden erst nach der Ausführung des Joins

berücksichtigt.

▪ Nach Möglichkeit in der ON-Bedingung einschränken.


Brandeis ConsultingWHERE und ON-Bedingungen - Beispiel


SELECT * FROM belege

LEFT OUTER JOIN waehrungen

ON belege.waehrung = waehrungen.waehrung

where waehrungen.waehrung <> 'EUR';

SELECT * FROM belege

LEFT OUTER JOIN waehrungen

ON belege.waehrung = waehrungen.waehrung

and waehrungen.waehrung <> 'EUR';

Brandeis ConsultingTabellen-UDFs

Als Tabellenausdrücke in der FROM-Klausel sind auch Funktionen erlaubt.

Demo:

select * from udf_statustexte( 'DE');


Brandeis ConsultingUnterabfragen in der FROM-Klausel

Gelegentlich findet man auch Unterabfragen in einer FROM-Klausel. Das ist vor

allem dann praktisch, wenn man nicht im Kontext von SQLScript ist.

In SQLScript kann man statt der Unterabfrage vorher ein entsprechende

Tabellenvariable definieren…


Brandeis ConsultingUnterabfragen in der FROM-Klausel & Alternative


SELECT bearbeiter,

todo

FROM ( SELECT bearbeiter,

SUM(plan_aufwand - ist_aufwand) as todo

FROM aufgaben

GROUP BY bearbeiter )

ORDER BY todo;

DO BEGIN

lt_todo = SELECT bearbeiter,

SUM(plan_aufwand - ist_aufwand) as todo

FROM aufgaben

GROUP BY bearbeiter;

SELECT bearbeiter,

todo

FROM :lt_todo

ORDER BY todo;

END;

Brandeis ConsultingWHERE-Klausel

Mit der WHERE-Klausel werden die Daten einer Abfrage eingeschränkt. Darin

können mehrere, mit logischen Operatoren AND, OR und NOT verknüpfte

Bedingungen vorkommen.


Brandeis ConsultingKomponenten der WHERE Klausel

Vergleichsausdrücke

• Typisch: Vergleich einer Spalte mit einem Literal

• Theoretisch jeder beliebige Ausdruck im Vergleich möglich

• Auch eine korrelierte Unterabfrage ist für den Vergleichswert möglich

IN/SOME/ANY/ALL Prädikat

• Vergleich eines Ausdrucks mit einer Menge von Werten

• Die Wertemenge kann auch aus einer Unterabfrage kommen

EXISTS Prädikat

• Unterabfrage, die entscheidet, ob ein Datensatz in der Ergebnismenge ist oder nicht.

Vergleich mit einem Muster: LIKE

• Platzhalter % für 0-N Zeichen, _ für genau ein Zeichen

• Mit LIKE_REGEXPR kann auch nach regulären Ausdrücken gesucht werden

WHERE Klausel


Brandeis ConsultingVergleich mit einzelnen Ausdrücken

Meist werden in der WHERE-Klausel Bedingungen der Form

SPALTE = Konstante

formuliert. Grundsätzlich sind aber bei einem Vergleich links und rechts vom

Gleicheitsszeichen beliebige Ausdrücke erlaubt. Z.B.

DAYS_BETWEEN( faelligkeit, current_date )

> plan_aufwand - ist_aufwand


Brandeis ConsultingVergleich mit mehreren Werten

Mit ANY/SOME bzw. ALL nach dem Vergleichsoperator, kann der Vergleich mit

einer ganzen Liste von Werten erfolgen.

Die Werte werden entweder als Liste in Klammern angegeben, oder von einer

Unterabfrage geliefert.


Brandeis ConsultingVergleich mit mehreren Werten – Beispiele


SELECT *

FROM aufgaben

WHERE STATUS = ANY ( 1, 2, 4 );

SELECT *

FROM aufgaben

WHERE STATUS = ANY ( SELECT id

FROM STATUS

WHERE is_final = true );

SELECT *

FROM aufgaben

WHERE STATUS <> ALL ( SELECT id

FROM STATUS

WHERE is_final = true );

Brandeis ConsultingVergleich mit einem Muster

Mit dem LIKE-Prädikat können Sie ein Muster in einer Zeichenkette suchen. Mit

NOT kann LIKE negiert werden. Platzhalter sind:

▪ Das Prozentzeichen % für eine beliebige Anzahl (0-N) von beliebigen Zeichen

▪ Der Unterstrich _ für exakt ein beliebiges Zeichen


--1. Negierung des ganzen Prädikats

SELECT count(*)

FROM aufgaben

WHERE NOT ( titel LIKE '%Aliquam%' );

--2. Äquivalente Abfrage mit NOT LIKE

SELECT count(*)

FROM aufgaben

WHERE titel NOT LIKE '%Aliquam%';

Brandeis Consulting

▪ select * from tables where table_name like '%DS!_TC%' escape '!'


Brandeis ConsultingVergleich mit einem Intervall

Das BETWEEN Prädikat prüft, ob ein Wert innerhalb eines abgeschlossenen

Intervalls liegt. Das bedeutet, dass die Werte von Ober- und Untergrenze zu

dem Intervall dazugehören.


Brandeis ConsultingVergleich mit einer Menge

Das IN-Prädikat ähnelt dem Vergleich mit ANY/SOME. Es wird geprüft, ob eine

Spalte in einer Menge von Werten ist. Diese kann wieder als Liste oder als

Unterabfrage formuliert werden.


--1. Abfrage für eine Spalte mit mehreren Werten mit OR

SELECT *

FROM benutzer

WHERE vorname = 'Aldo'

OR vorname = 'Olag'

OR vorname = 'Sascha';

--2. Analoge Abfrage mit dem IN-Prädikat

SELECT *

FROM benutzer

WHERE vorname IN ( 'Aldo', 'Olag','Sascha' );

Brandeis ConsultingVergleich mit einer Menge – Beispiele


--1. Abfrage für eine Spalte mit mehreren Werten mit OR

SELECT *

FROM benutzer

WHERE vorname = 'Aldo'

OR vorname = 'Elvin'

OR vorname = 'Sascha';

--2. Analoge Abfrage mit dem IN-Prädikat

SELECT *

FROM benutzer

WHERE vorname IN ( 'Aldo', 'Elvin', 'Sascha' );

--3. Nutzung einer Unterabfrage mit dem IN-Prädikat:

-- Benutzer, denen aktuell keine Aufgabe zugeordnet ist

SELECT *

FROM benutzer

WHERE id NOT IN ( SELECT DISTINCT bearbeiter

FROM aufgaben );

Das sieht man häufig in ABAP…

Brandeis ConsultingPrüfung auf Existenz

Mit dem EXISTS-Prädikat kann geprüft werden, ob eine korrelierte Unterabfrage

ein Ergebnis (d.h. mindestens eine Zeile) liefert oder nicht.


Brandeis ConsultingPrüfung auf Existenz – Beispiele


--1. Abfrage mit EXISTS-Prädikat - Alle Benutzer mit mindestens einer Aufgabe

SELECT *

FROM benutzer

WHERE EXISTS (

SELECT bearbeiter

FROM aufgaben

WHERE aufgaben.bearbeiter = benutzer.id );

--2. Abfrage mit NOT EXISTS - Aufgaben, deren Status nicht in der Statustabelle steht

SELECT *

FROM aufgaben

WHERE NOT EXISTS (

SELECT id

FROM STATUS

WHERE STATUS.id = aufgaben.STATUS );

--3. Analoge Abfrage mit NOT IN

SELECT *

FROM aufgaben

WHERE STATUS NOT IN (

SELECT id

FROM STATUS );

Brandeis ConsultingUnterschied EXISTS und IN-Prädikat

In den Beispielen haben wir gesehen, dass manche Bedingungen mit beiden

Prädikaten formuliert werden können.

Das IN-Prädikat setzt voraus, dass aus der Unterabfrage auch der entsprechende

Spaltenwert geliefert werden kann. Bei EXISTS reicht die Existenz einer Zeile aus.

Das EXISTS-Prädikat kann auch mehrere Spalten vergleichen, IN bezieht sich

immer nur auf eine Spalte.


Brandeis ConsultingPrüfung auf NULL

Mit

<Ausdruck> IS [NOT] NULL

kann geprüft werden, ob ein Ausdruck, insbesondere eine Tabellenspalte, den

Wert NULL hat. Diese Abfrage ist mit dem normalen Vergleichsprädikaten nicht

möglich.


Brandeis ConsultingSonstige Bedingungen in der WHERE-Klausel

▪ In der WHERE –Klausel kann auch mit CASE eine Fallunterscheidung

stattfinden. Z.B. anhand eines Parameters

▪ Mit CONTAINS kann auf Spalten in Column Tables unscharf gesucht werden.


Brandeis ConsultingÜbung Joins

▪ Welche Aufgaben sind in einem finalen Status?


Brandeis ConsultingÜbungen – Lösung


DO BEGIN

SELECT *

FROM aufgaben

INNER JOIN status

ON aufgaben.status = status.id

WHERE status.is_final = TRUE;

END;

Brandeis ConsultingGROUP BY Klausel

Wenn Aggregat-Funktionen verwendet werden, so muss nach allen nicht-

Aggregat-Spalten mit GROUP BY gruppiert werden. Damit wird die Granularität

der Aggregation festgelegt.

Feldliste und GROUP BY-Klausel müssen exakt zueinander passen.


SELECT bearbeiter,

COUNT(*) AS anzahl

FROM aufgaben

GROUP BY bearbeiter;

Brandeis ConsultingGROUP BY mit Ausdrücken

Falls einzelne Spalten in

der Feldliste mit einem

Ausdruck definiert wurden,

so muss sich dieser

Ausdruck in der GROUP BY-

Klausel entsprechend

wiederholen.


SELECT CASE

WHEN faelligkeit < to_date('2017-12-01')

AND status NOT IN ( 5, 6 )

THEN 'zu pruefen'

ELSE 'OK'

END AS "Prüfen?",

count(*) AS cnt

FROM aufgaben

GROUP BY CASE



THEN 'zu pruefen'

ELSE 'OK'

END;

Brandeis ConsultingGROUP BY in 2-Stufen

Weitere Alternative: Als mehrstufige Abfrage, siehe Folien zu Unterabfragen


DO BEGIN

--1. Neue Spalte mit CASE-Ausdruck erzeugen

lt_select = SELECT CASE



THEN 'zu pruefen'

ELSE 'OK'

END AS "Prüfen?"

FROM aufgaben;

--2. Gruppierung nach der neuen Spalte

SELECT "Prüfen?",

count(*) as cnt

FROM :lt_select

GROUP BY "Prüfen?";

END

Brandeis ConsultingGROUP BY – GROUPING SETS

Für BW-Entwicklung sind GROUPING SETS nicht relevant. Sie erlauben in

Abfragen die Gruppierung nach mehreren Kriterien gleichzeitig.


Brandeis ConsultingHAVING Klausel

▪ Bildet einen Filter auf die aggregierten Daten

▪ Wird erst nach der Aggregation ausgewertet

▪ Sollte nur verwendet werden, wenn sich das Kriterium nicht schon vorher mit

WHERE filtern lässt

▪ Die HAVING Klausel muss sich auf die Ausdrücke der Feldliste beziehen, nicht

auf die Spaltennamen:


SELECT bearbeiter,

COUNT(*) AS anzahl

FROM aufgaben

GROUP BY bearbeiter

HAVING COUNT(*) > 50;

Brandeis ConsultingORDER BY Klausel

Die Reihenfolge der Datensätze einer SELECT-Abfrage wird durch die ORDER BY-

Klausel festgelegt. Im Gegensatz zur GROUP BY, HAVING oder WHERE-Klausel

kann man sich hier aber auf die Spaltennamen beziehen:

Ohne ORDER BY-Klausel werden die Datensätze in zufälliger Reihenfolge

zurückgegeben.


SELECT bearbeiter,

COUNT(*) AS anzahl

FROM aufgaben

GROUP BY bearbeiter

HAVING COUNT(*) > 50

ORDER BY anzahl;

Brandeis ConsultingORDER BY mit sonstigen Ausdrücken

Die Sortierung mit ORDER BY kann sich aber auch auf beliebige andere Ausdrücke

beziehen. Auch auf solche, die nicht in der Feldliste vorhanden sind.


SELECT id,

titel

FROM aufgaben

ORDER BY plan_aufwand - ist_aufwand;

Brandeis ConsultingORDER BY mit Spaltennummern

Die Sortierung mit ORDER BY kann sich auch auf die Spaltennummern beziehen.

Das ist aber keine gute Idee, da es bei Änderungen an der Abfrage sehr

fehlerträchtig ist. Das gilt insbesondere in Kombination mit dem * in der Feldliste.


SELECT id,

titel

FROM aufgaben

ORDER BY 2, 1;

Brandeis ConsultingORDER BY – Zusätze zum Sortierausdruck

Nach einem Sortierausdruck können die folgenden Zusätze angegeben werden:

▪ ASC – Aufsteigende Sortierung (Standard)

▪ DESC – Absteigende Sortierung

▪ NULLS FIRST – NULL-Werte werden als kleinster Wert betrachtet (Standard)

▪ NULLS LAST – NULL-Werte werden als größter Wert betrachtet


Brandeis ConsultingMengenlehre

Wenn zwei SELECT-Abfragen die gleichen Spalten haben, dann kann man damit

Mengenoperationen durchführen. Zur Verfügung stehen die folgenden

Operatoren:

▪ Vereinigungsmenge (UNION)

▪ Schnittmenge (INTERSECT)

▪ Subtraktion von Mengen (EXCEPT)


Brandeis ConsultingMengenoperatoren Allgemein

Die Syntax zur Verwendung der Mengenoperatoren sieht so aus:

<Abfrage1> <Mengenoperator> <Abfrage2>

Alle Abfragen brauchen eine kompatible Spaltenstruktur:

▪ Der Datentyp und der Spaltenname wird aus der ersten Abfrage übernommen

▪ Der Inhalt der Spalten der zweiten Abfrage muss kompatibel sein

▪ Die Spaltennamen der zweiten Abfrage spielen keine Rolle


Brandeis ConsultingUNION

UNION ist der bekannteste Mengenoperator und kann nicht durch andere Tricks

ersetzt werden. Er führt die Ergebnisse von zwei Abfragen mit gleicher

Spaltenstruktur zusammen.

Mit UNION ALL wird erreicht, dass gegebenenfalls Duplikate erhalten bleiben,

wenn beispielsweise ein Datensatz in beiden Abfragen vorkommt.

Alle Mengenoperatoren, außer UNION ALL, entfernen alle Duplikate!


Brandeis ConsultingUNION – Beispiel


--Abfrage mit UNION: Duplikate werden entfernt

SELECT 'A' AS SPALTE1,

'B' AS SPALTE2

FROM DUMMY

UNION

SELECT 'A' AS SPALTE1A,

'B' AS SPALTE2A

FROM DUMMY;

--Zweite Abfrage mit UNION ALL ohne Duplikatsentfernung

SELECT 'A' AS spalte1,

'B' AS spalte2

FROM DUMMY

UNION ALL

SELECT 'A' AS spalte1,

'B' AS spalte2

FROM DUMMY;

Brandeis ConsultingINTERSECT

Die Schnittmenge von zwei Abfragen wird mit dem Mengenoperator INTERSECT

gebildet. In der Praxis sieht man diesen Operator relativ selten, da man das

gleiche Ergebnis häufig auch mit einem Inner-Join auf geeignete Schlüsselfelder

erreichen kann.

Vorteil von INTERSECT: Es werden immer alle Spalten berücksichtigt.


Brandeis ConsultingINTERSECT – Beispiel


--Abfrage aller Benutzer, die Projektleiter sind und eine

--Aufgabe zur Bearbeitung zugewiesen haben.

--Zunächst mit INTERSECT:

SELECT bearbeiter

FROM aufgaben

INTERSECT

SELECT projektleiter

FROM projekte;

--Und als INNER JOIN:

SELECT DISTINCT bearbeiter

FROM aufgaben

INNER JOIN projekte

ON bearbeiter = projektleiter;

Brandeis ConsultingEXCEPT

Um eine Ergebnismenge von einer anderen Ergebnismenge abzuziehen, kann der

EXCEPT-Mengenoperator verwendet werden. Dieser Operator wird in der Praxis

eher selten verwendet, da die Mengensubtraktion auch mit dem NOT EXISTS-

Prädikat abgebildet werden kann.


Brandeis ConsultingEXCEPT – Beispiel


--Abfrage aller Bearbeiter von Aufgaben, ohne Projektleiter

--Zunächst mit EXCEPT

SELECT bearbeiter

FROM aufgaben

EXCEPT

SELECT projektleiter

FROM projekte;

--Und die gleiche Abfrage mit NOT EXISTS

SELECT DISTINCT bearbeiter

FROM aufgaben AS a

WHERE NOT EXISTS (

SELECT id

FROM projekte AS p

WHERE p.projektleiter = a.bearbeiter );

Brandeis ConsultingUnterabfragen

Abfragen, die als Ausdruck in anderen SQL-Anweisungen verwendet werden,

nennen wir Unterabfragen (Subqueries).


Brandeis ConsultingSkalare Unterabfragen

Skalare Unterabfrage liefern genau einen Wert:


SELECT *

FROM aufgaben

WHERE faelligkeit = (SELECT MAX(faelligkeit)

FROM aufgaben);

Brandeis ConsultingSpalten-Unterabfragen

Skalare Unterabfrage liefern eine Tabelle mit genau einer Spalte:

Es kann sein, dass die Treffermenge der Unterabfrage leer ist.


SELECT *

FROM aufgaben

WHERE status IN (SELECT id

FROM status

WHERE is_final = TRUE);

Brandeis ConsultingTabellen-Unterabfragen

Tabellen-Unterabfrage liefern eine Tabelle, die man überall dort verwenden kann,

wo ein Tabellenausdruck erforderlich ist.

Z.B. mehrstufige Abfragen statt Tabellenvariablen, vgl. GROUP BY Folie…


SELECT "Prüfen?",

count(*)

FROM ( SELECT CASE



THEN 'zu pruefen'

ELSE 'OK'

END AS "Prüfen?",

id

FROM aufgaben)

GROUP BY "Prüfen?";

Brandeis ConsultingKorrelierte Unterabfragen

Eine Unterabfrage, die sich auf die Spalten einer umgebenden Abfrage bezieht,

bezeichnet man als korrelierte Unterabfrage.


SELECT bearbeiter,

faelligkeit,

id,

titel

FROM aufgaben AS a

WHERE faelligkeit = (SELECT MAX(faelligkeit)

FROM aufgaben

WHERE bearbeiter = a.bearbeiter)

ORDER BY faelligkeit DESC,

bearbeiter ASC;

Brandeis ConsultingAliasnamen

Sowohl Spalten als auch Tabellen können mit AS in Abfragen einen neuen Namen

verpasst bekommen.

▪ Spaltenalias geben einer Spalte einen neuen Namen. Dieser ist für den

Aufurfer der Abfrage sichtbar.

▪ Tabellenalias geben einem Tabellenausdruck in der FROM-Klausel innerhalb

einer Abfrage einen Namen. Das kann erforderlich sein, wenn der

Tabellenname ansonsten nicht eindeutig ist, z.B. wenn eine Tabelle doppelt in

einer Abfrage vorkommt.


Brandeis ConsultingVorteile von Alias Namen

▪ Bessere Lesbarkeit des Codes

▪ Lange oder sehr technische Tabellenamen können ersetzt werden

▪ Der Name einer Tabelle kann die Rolle der Tabelle statt dem Inhalt repräsentieren:

▪ Bearbeiter statt Benutzer oder

▪ Projektleiter statt Benutzer

▪ Ein Austausch von Tabellen in Abfragen ist möglich


Brandeis ConsultingBeispiel für Aliasnamen


SELECT aufgaben.id AS aufgabe_id,

aufgaben.titel AS aufgabe_titel,

bearbeiter.id AS bearbeiter_id,

bearbeiter.vorname

|| ' '

|| bearbeiter.nachname AS bearbeiter_name,

projektleiter.id AS projektleiter_id,

projektleiter.vorname

|| ' '

|| projektleiter.nachname AS projektleiter_name

FROM aufgaben

INNER JOIN projekte

ON aufgaben.projekt = projekte.id

INNER JOIN benutzer AS projektleiter

ON projekte.projektleiter = projektleiter.id

INNER JOIN benutzer AS bearbeiter

ON aufgaben.bearbeiter = bearbeiter.id;

Documents

Die SELECT-Anweisung