T-SQL Ninja #21

IIF

Was ist IIF?

Die Funktion IIF ist eine logische Funktion, die drei Argumente übergeben bekommt. Sie wertet das erste Argument zu einem Bool’schen Ausdruck aus und gibt, wenn das erste Argument zu TRUE ausgewertet wird das zweite Argument zurück, sonst das dritte. Das ist ähnlich wie die ternäre Schreibweise in Programmiersprachen wie C# wo mit Fragezeichen und Doppelpunkt ausgewertet wird. Der C#-Ausdruck string result = (i < 3? "less than": "more than") wäre dann gleichbedeutend mit dem TSQL-Ausschnitt @Result = IIF(@i<3,"less than", "greater than").

Wofür könnt ihr IIF verwenden?

Die IIF-Funktion ist also zunächst einmal eine kürzere Schreibweise für CASE-WHEN-Abfrage-Konstrukte. Nehmen wir uns als Beispiel die Wide World Importers Datenbank und fragen wir darin die StockItemTransaction-Tabelle ab. Dabei möchten wir uns einmal die TransactionTypeID näher ansehen. Dabei fällt uns auf, dass in der Tabelle nur drei Transaktionstypen vorkommen:

SELECT DISTINCT TransactionTypeID 
FROM Warehouse.StockItemTransactions

Wenn wir nun den Transaktionstypen auflösen wollten, könnten wir natürlich ein Join auf die entsprechende Tabelle ausführen, um den Namen aufzulösen:

SELECT
     StockItemTransactionID
    ,StockItemID
    ,TransactionTypeName
    ,CustomerID
    ,InvoiceID
    ,SupplierID
    ,PurchaseOrderID
FROM Warehouse.StockItemTransactions sit
LEFT JOIN Application.TransactionTypes tt
on sit.TransactionTypeID = tt.TransactionTypeID

Wenn wir nur für drei Werte in einer Spalte keinen Join machen möchten, könnten wir stattdessen auch ein CASE-Statement verwenden:

SELECT 
     StockItemTransactionID
    ,StockItemID
    ,TransactionTypeName = 
        CASE TransactionTypeID 
        WHEN 10 THEN 'Stock Issue' 
        WHEN 11 THEN 'Stock Receipt' 
        WHEN 12 THEN 'Stock Adjustment at Stocktake' 
        ELSE 'Unknown' END
    ,CustomerID
    ,InvoiceID
    ,SupplierID
    ,PurchaseOrderID
FROM Warehouse.StockItemTransactions 

Das wiederum könnten wir umschreiben zu:

SELECT 
     StockItemTransactionID
    ,StockItemID
    ,TransactionTypeName = IIF(TransactionTypeID = 10, 'Stock Issue',
        IIF(TransactionTypeID = 11, 'Stock Receipt',
            IIF(TransactionTypeID = 12, 'Stock Adjustment at Stocktake', 
                'Unknown')))
    ,CustomerID
    ,InvoiceID
    ,SupplierID
    ,PurchaseOrderID
FROM Warehouse.StockItemTransactions 

Warum dieser Aufwand, wenn wir das doch mit CASE-Statements alles auch abbilden können? Ganz einfach: weil eine Inline-Funktion sich oftmals besser liest als ein CASE-Statement. Als Beispiel nehmen wir folgende Abfrage:

SELECT 
     NStockIssues       = SUM(IIF(TransactionTypeID = 10, 1, 0))
    ,NStockReceipts     = SUM(IIF(TransactionTypeID = 11, 1, 0))
    ,NStockAdjustments  = SUM(IIF(TransactionTypeID = 12, 1, 0)) 
FROM Warehouse.StockItemTransactions

Hier empfinden viele Ninjas das Zusammenspiel von IIF mit SUM sehr viel besser lesbar als die entsprechende CASE-Formulierung:

SELECT 
     NStockIssues       = SUM(CASE TransactionTypeID WHEN 10 THEN 1 ELSE 0 END)
    ,NStockReceipts     = SUM(CASE TransactionTypeID WHEN 11 THEN 1 ELSE 0 END)
    ,NStockAdjustments  = SUM(CASE TransactionTypeID WHEN 12 THEN 1 ELSE 0 END) 
FROM Warehouse.StockItemTransactions

Ob man die erste oder die letzte Form bevorzugt, ist natürlich Geschmackssache, ihr solltet euch aber bewusst sein, dass IIF keine ANSI-SQL Funktionalität ist (und auch erst seit 2012 in T-SQL enthalten ist, man munkelt dass das mit der Integration von Access-Datenbanken in den SQL Server zu tun haben könnte). Wenn euch Kompatibilität zu anderen Datenbanksystemen also wichtig sein sollte (oder ihr noch ältere SQL Server Versionen im Einsatz habt), dann solltet ihr auf die Kurzform mit IIF verzichten und lieber CASE Statements schreiben.

Referenzen

Ninja-Notebooks @ GitHub

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.