SELECT einzelner Datensätze aus 1:N Beziehungen

Veröffentlicht am 29. August 2022 von

Jörg Brandeis

Ein häufiges Problem im SQL (und damit auch in CDS ABAP ) ist das Nachlesen einzelner Daten aus einer anderen Tabelle (hier Lookup-Tabelle genannt), ohne dass sich die Daten ausmultiplizieren. Solange eine :1 Beziehung vorliegt, ist die Lösung mit einem normalen Join trivial. Falls aber mehrere Datensätze in Frage kommen, es sich also um eine :N Beziehung handelt, wird die Sache schon schwieriger. Aber hierfür gibt es ein paar einfache Lösungsmuster.

Ein Beispiel aus dem Demo Datenmodell von "SQLScript for SAP HANA"

Das folgende Beispiel bezieht sich auf das Demo Datenmodell, das in der englischen Ausgabe des Buches SQLScript for SAP HANA verwendet wird. Dabei geht es um eine Aufgabenverwaltung. Jeder Benutzer (Tabelle USERS) hat mehrere Aufgaben (Lookup-Tabelle TASKS). Wenn wir also Abfragen auf die Benutzer machen, und dazu noch Informationen aus den Aufgaben hinzufügen, haben wir genau die beschriebene Situation.

SELECT u.id AS UserID,
       u.firstname, 
       u.lastname, 
       t.id AS TaskID,
       t.due_date 
  FROM users AS u
  LEFT OUTER JOIN tasks AS t 
  ON u.id = t.assignee;

Die Lösungsansätze

Wichtig ist, dass man immer die folgenden Fälle berücksichtigt:

• Es gibt in der Lookup-Tabelle keinen Datensatz - Wenn das passieren kann, muss man sich zwischen INNER und LEFT OUTER JOIN entscheiden, je nach dem ob hier gefiltert werden soll oder nicht. Falls nein müssen ggf. die NULL-Werte nachbearbeitet werden. Das habe ich in den Lösungsansätzen unten bewust nicht gemacht, damit man erkennt, dass ggf. NULL vorkommen kann.
• Es gibt in der Lookup-Tabelle genau einen passenden Datensatz - Dieser muss geliefert werden
• Es gibt in der Lookup-Tabelle mehrere passende Datensätze - Hier müssen wir uns irgendwie auf einen festlegen. Das ist das eigentliche Thema dieses Artikels.

Wie immer ist es im SQL möglich, die Anforderung ein einer großen Abfrage zu lösen. Das ist aber zum einen nicht ganz so übersichtlich. Und zum anderen bestehen alle Lösungen aus einer gemeinsamen Teillösung:

Wir brauchen eine Abfrage (d.h. Tabellenvarible bzw. CDS View), die für jeden Schlüssel genau einen Datensatz liefert

Mit Schlüssel meine ich den Schlüssel der Ausgangstabelle, in unserem Falle die Benutzer-ID. Dann können wir damit das eigentliche Problem mit einem LEFT OUTER JOIN ganz einfach lösen. Ich zeige im Folgenden drei Möglichkeiten, wie wir einen solche Abfrage erstellen können. Diese wird stets der Tabellenvariable ONE_TASK_PER_USER zugewiesen.

1. Mit Aggregatfunktionen

Wenn uns nur eine Spalte der Tabelle interessiert, können wir mit den Aggregatfunktionen MIN() oder MAX() den größten oder kleinsten Wert pro Schlüssel hinzufügen. Diese können wir auch verwenden, wenn der Wert für alle Zeilen gleich ist. Hauptsache wir bekommen nur eine Ausprägung. Wir können auch Daten mehrerer Datensätze aggregieren, in dem wir beispeisweise Zeichenketten mit STRING_AGG verketten.

DO BEGIN 

  one_task_per_user = SELECT assignee, 
                             max(due_date) AS due_date
                        FROM tasks
                      GROUP BY assignee;

  SELECT u.id AS UserID,
        u.firstname, 
        u.lastname, 
        t.due_date 
    FROM users AS u
    LEFT OUTER JOIN :one_task_per_user AS t 
    ON u.id = t.assignee
    ORDER BY UserID;

  END;

2. Irgendein Datensatz: Mit Unterabfrage auf den Schlüssel in der WHERE-Klausel

Wenn wir mehrere Spalten der Tabelle nachlesen wollen dann müssen natürlich alle Spalten pro Bearbeiter aus dem gleichen Datensatz kommen. Damit funktioniert der 1. Ansatz nicht mehr. Statt dessen können wir die Daten in der WHERE-Klausel pro Bearbeiter der Aufgabe einschränken. Im einfachsten Fall können wir das mit einer Unterabfrage auf den höchsten oder niedrigsten Schlüsselwert (hier die ID) pro Bearbeiter machen. Das ist vielleicht etwas willkürlich bzw. zufällig, aber auf jeden Fall ist es eindeutig.

DO BEGIN 

  one_task_per_user = 
    SELECT *
    FROM tasks AS o
    WHERE id = (SELECT MAX(id)
                  FROM tasks AS i 
                 WHERE i.assignee =  o.assignee);

  SELECT u.id AS UserID,
        u.firstname, 
        u.lastname, 
        t.id AS TaskID,
        t.due_date,
        t.title 
    FROM users AS u

    LEFT OUTER JOIN :one_task_per_user AS t 
    ON u.id = t.assignee

    ORDER BY UserID;

END;

Wichtig ist, das wir in der WHERE-Klausel sicherstellen, das exakt ein Datensatz pro Bearbeiter gefunden wird.

3. Irgendein Datensatz mit grösten Fälligkeitsdatum pro Bearbeiter

Der letzte Satz im vorherigen Abschnitt ist aber nicht immer so einfach zu erfüllen. Falls wir beispielsweise die Aufgabe mit dem höchsten Fälligkeitsdatum selektieren wollen, dann wird es komplexer. Denn es kann mehr als eine Aufgabe pro Bearbeiter geben, auf die das zutrifft. Damit brauchen wir einen Zwischenschritt:

1. Wir brauchen zunächst eine Tabelle mit den Aufgaben mit dem jeweils höchsten Fälligkeitsdatum pro Bearbeiter. Hier TASKS_WITH_MAX_DD
2. Daraus müssen wir dann wie oben mehr oder weniger willkürlich einen Datensatz auswählen.

DO BEGIN 

  tasks_with_max_dd = 
    SELECT *
    FROM tasks AS o
    WHERE due_date = 
             (SELECT MAX(due_date)
                FROM tasks AS i 
               WHERE i.assignee =  o.assignee);

  one_task_per_user = 
    SELECT *
    FROM tasks AS o
    WHERE id = (SELECT MAX(id)
                  FROM :tasks_with_max_dd AS i 
                 WHERE i.assignee =  o.assignee);

  SELECT u.id AS UserID,
        u.firstname, 
        u.lastname, 
        t.id AS TaskID,
        t.due_date,
        t.title 
    FROM users AS u

    LEFT OUTER JOIN :one_task_per_user AS t 
    ON u.id = t.assignee

    ORDER BY UserID;

END;

Und in CDS?

Die Beispiele beziehen sich auf SQLScript, d.h. in der SQL-Konsole bzw. im AMDP lassen sie sich exakt so umsetzen. Wenn wir diese Logik im CDS ABAP benötigen, dann müssen wir mit den aktuellen Einschränkungen bzw. Unterschieden zwischen CDS und SQLScript umgehen. Diese sind hier:

• SELECT * ist nicht möglich. Wir müssen die Felder einzeln benennen Felder.
• Tabellenvariablen gibt es nur bei CDS-Table Functions, denn diese sind AMDP Funktionen. Wenn wir bei CDS Views oder CDS View Entities bleiben wollen, dann müssen wir statt dessen mehrere CDS Views anlegen.
• In CDS Views und CDS View Entities gibt es keine Möglichkeit für Unterabfragen in WHERE-Klauseln, siehe SAP Dokumentation. Trotzdem ist die gezeigte Logik zum Filtern möglich. Nur muss statt dessen für jede Unterabfrage wiederum ein CDS-View angelegt werdne und dieser jeweils mit einem INNER JOIN zum Filtern verwendet werden. Das ist leider nicht schön. Das folgende Beispiel zeigt die notwendigen CDS-Views:

View zum Filtern der spätesten Fälligkeit pro Bearbeiter

DEFINE VIEW cds1 AS 
SELECT FROM tasks
{ 
  assignee, 
  MAX(due_date) AS max_dd
}
  GROUP BY assignee;

Filterung damit auf die Aufgaben per INNER JOIN

DEFINE VIEW cds2 AS 

SELECT FROM tasks AS t

INNER JOIN :cds1 AS filter 
        ON t.assignee = filter.assignee
       AND t.due_date = filter.max_dd; 
{ 
  t.assignee, 
  t.id, 
  t.due_date,
  t.title
}

View zum eindeutigen Selektieren einer Aufgabe pro Bearbeiter

DEFINE VIEW cds3 AS 

SELECT FROM cds2
{  
  MAX(id) as max_id, 
  assignee
}
  GROUP BY assignee;

Filterung auf die Aufgaben per INNER JOIN

DEFINE VIEW cds4 AS 

SELECT FROM cds2 AS t

INNER JOIN cds3 AS filter
   ON t.assignee = filter.assignee
  AND t.id = filter.max_id

{ 
  t.assignee, 
  t.id, 
  t.due_date, 
  t.title
} 

JOIN der eindeutigen Aufgaben an die Benutzerdaten

DEFINE VIEW cds5 AS 
  SELECT FROM users AS u

  LEFT OUTER JOIN CDS4 AS t 
    ON u.id = t.assignee
{  
   u.id AS UserID,
        u.firstname, 
        u.lastname, 
        t.id AS TaskID,
        t.due_date,
        t.title 
}
    ORDER BY UserID;

Wir sehen deutlich, dass eine Impmelmentierung mit CDS nicht mehr so elegant ist. Am Ende müssen wir 5 Views für diese Logik anlegen.

Fazit

Beim Nachlesen ist die Auswahl des passenden Datensatzes nicht immer einfach. Es bietet sich an mehrstufig vorzugehen, um am Ende eine Tabelle mit einem eindeutigen Datensatz pro Schlüssel zu erzeugen. Damit können auch komplexere Aufgabenstellungen gelöst werden. CDS ist für solch verschachtelte Aufgabenstellungen nicht so elegant wie SQLScript, aber es funktioniert.