Was ist CHOOSE?
CHOOSE
gehört sicher zu den eher unbekannteren Funktionen im SQL Server, dabei fristet diese Funktion zu Unrecht ein Schattendasein, denn sie kann euch viele lästige Abfragen deutlich erleichtern. CHOOSE
könnt ihr oft verwenden, um lange, komplexe CASE
–WHEN
-Anweisungen zu vereinfachen, nämlich immer dann, wenn es darum geht auf Basis einer Zahl ein Element aus einer Liste auszuwählen, also wie bei einem Array-Zugriff über einen Index.
Wie könnt ihr mit CHOOSE arbeiten?
Die grundlegende Funktionalität von CHOOSE
ist recht einfach: ihr übergebt der CHOOSE
-Funktion eine Zahl (den Index, den ihr haben wollt und der hier im Übrigen 1-basiert ist) als erstes Argument und dann eine Liste mit Rückgabewerten. CHOOSE
liefert den entsprechenden Wert aus der Liste. Sehen wir uns dafür einmal ein Beispiel an, dafür erzeugen wir uns eine Tabelle zum Testen:
DROP TABLE IF EXISTS #Ninjas CREATE TABLE #Ninjas ( NinjaName varchar(250) ,StyleID int )
Fügen wir nun einige Werte in die Tabelle ein:
INSERT INTO #Ninjas (NinjaName, StyleID) VALUES ('Deadflow', 1) ,('Jadestroke', 3) ,('Phantom Flow', 2) ,('Death Flash', 3)
Wenn wir, ohne eine Tabelle zu haben, die die Stile auflöst, die StyleID
zu einem lesbaren Namen auflösen möchten, können wir das wie folgt tun:
SELECT NinjaName ,StyleID ,CASE StyleID WHEN 1 THEN 'Katana' WHEN 2 THEN 'Bajutsu' WHEN 3 THEN 'Bojutsu' WHEN 4 THEN 'Shuriken' WHEN 5 THEN 'Assassin' END FROM #Ninjas
Diese Syntax ist aber nicht gerade übersichtlich und kann mit CHOOSE
abgekürzt werden. Dieselbe Abfrage würde dann wie folgt aussehen:
SELECT NinjaName ,StyleID ,CHOOSE(StyleID, 'Katana', 'Bajutsu', 'Bojutsu', 'Shuriken', 'Assassin') FROM #Ninjas
Ähnlich könnt ihr natürlich beispielsweise mit Datumsspalten arbeiten, dafür könnt ihr auf der WideWorldImporters Datenbank folgende Abfrage ausführen, die die Monatsnamen auflöst:
SELECT OrderID ,OrderDate ,CHOOSE(MONTH(OrderDate) ,'Jan' ,'Feb' ,'Mar' ,'Apr' ,'May' ,'Jun' ,'Jul' ,'Aug' ,'Sep' ,'Oct' ,'Nov' ,'Dec') as OrderMonth FROM Sales.Orders
Und auch mit anderen Zahlenwerten geht das, versuchen wir beispielsweise zu klassifizieren, wie lange ein Kunde in der WideWorldImporters Datenbank bereits Kunde ist:
SELECT CustomerID ,AccountOpenedDate ,CHOOSE(DATEDIFF(YEAR, AccountOpenedDate, GETDATE()) ,'Recent' ,'Recent' ,'Recent' ,'Average' ,'Ancient') FROM Sales.Customers
Wenn ihr diese Abfrage ausführt, dann seht ihr, dass es einige Kunden gibt, die bereits seit dem Jahr 2013 Kunden sind und daher aus dem Index herauslaufen. Für diese Kunden gilt es nun eine weitere Klassifizierung zu finden. Glücklicherweise liefert CHOOSE
bei einem Wert, der keinem Index entspricht (beispielsweise einem Wert kleiner eins oder größer der Anzahl der Elemente im Array einen NULL
-Wert zurück. Diesen können wir nun natürlich entsprechend behandeln:
SELECT CustomerID ,AccountOpenedDate ,ISNULL(CHOOSE(DATEDIFF(YEAR, AccountOpenedDate, GETDATE()) ,'Recent' ,'Recent' ,'Recent' ,'Average' ,'Ancient') ,'Jurassic') FROM Sales.Customers
Leider sehen wir hier noch, dass der Rückgabewert für die ältesten Kunden nur noch “Jurassi” ist, das “c” wird abgeschnitten, da CHOOSE
den kleinstmöglichen Datentypen als Rückgabewert wählt, an den ISNULL
sich hält und den Rückgabewert entsprechend abschneidet. Schöner ist das, wenn ihr COALESCE
verwendet:
SELECT CustomerID ,AccountOpenedDate ,COALESCE(CHOOSE(DATEDIFF(YEAR, AccountOpenedDate, GETDATE()) ,'Recent' ,'Recent' ,'Recent' ,'Average' ,'Ancient') ,'Jurassic') FROM Sales.Customers
Nun sind die Dinosaurier unter euren Kunden entsprechend gekennzeichnet und ihr könnt mit der Bearbeitung fortfahren.