SQL – Comando CASE

Il seguente articolo utilizza come esempi codici trovati sull’internet, talvolta ripetuti anche in più siti diversi.

Cominciamo con una rapida descrizione del comdando CASE in T-SQL; questo ha due comportamenti principali:

Confronto di un valore con n

Ho l’espressione da valutare subito dopo il CASE e i valori con cui confrontarli dopo il WHEN

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  WHEN valueN THEN resultN
  [
    ELSE elseResult
  ]
END

Espressioni booleane indipendenti

Invece di specificare una singola espressione dopo il CASE, contiene una serie di valori booleani indipendenti tra di loro nelle clausole WHEN

CASE
  WHEN booleanExpression1 THEN result1
  WHEN booleanExpression2 THEN result2
  ...
  WHEN booleanExpressionN THEN resultN
  [
    ELSE elseResult
  ]
END

Di seguito illustriamo esempi di utilizzo del comando CASE nel mondo reale:

Esempi applicativi

Migliorare l’output di una SELECT

Il caso più semplice dell’utilizzo del CASE è migliorare l’outoput di una SELECT. Per esempio, assumiamo di avere una colonna che indica il sesso di un dipendente: M per maschio e F per femmina.
Posso migliorare il risultato di uscita andando a scrivere ’Male’ e ’Female’ utilizzando il CASE nel seguente modo:

SELECT
  FirstName, LastName,
  Salary, DOB,
  CASE Gender
    WHEN 'M' THEN 'Male'
    WHEN 'F' THEN 'Female'
  END
FROM Employees

Fornire descrizioni descrittive

Per esempio, assumiamo di avere una colonna prezzo e voler offrire delle descrizioni parlanti in base al range in cui si trova tale prezzo.
Questo obiettivo può essere facilmente raggiunto con l’istruzione CASE nel seguente modo:

SELECT   ProductNumber, Name, 'Price Range' =
      CASE
         WHEN ListPrice =  0 THEN 'Mfg item - not for resale'
         WHEN ListPrice < 50 THEN 'Under $50'
         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
         ELSE 'Over $1000'
      END
FROM Production.Product
ORDER BY ProductNumber;

Fornire un risultato nel caso di valore NULL

Talvolta devo lavorare con colonne che possono essere più o meno valorizzate. Nel caso in cui assumano valore NULL, spesso può essere non adeguato fornire in uscita valore NULL, meglio gestirlo con una stringa parlante.

SELECT FirstName, LastName, TelephoneNumber, 'When to Contact' = 
     CASE
          WHEN TelephoneSpecialInstructions IS NULL THEN 'Any time'
          ELSE TelephoneSpecialInstructions
     END
FROM Person.vAdditionalContactInfo;

Useguire il CASE con un istruzione di UPDATE

Voglio aggiornare il valore di alcune righe in base al valore delle stesse.
Un primo esempio è una query per il passaggio da abbreviazione a stringa estesa per i valori di una colonna

UPDATE Customer
SET StateCode = CASE StateCode
        WHEN 'MP'
            THEN 'Madhya Pradesh'
        WHEN 'UP'
            THEN 'Uttar Pradesh'
        WHEN 'DL'
            THEN 'Delhi'
        ELSE NULL
        END

Un secondo caso è l’impostazione del valore di determinate colonne in base al valore che avevano prima le stesse. In particolare, se il campo VacationHours - 10 mi fornisce un valore negativo, allora aumento tale campo di 40, altrimenti solo di 20.

UPDATE HumanResources.Employee
SET VacationHours =
    ( CASE
         WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
         ELSE (VacationHours + 20.00)
       END
    )
OUTPUT Deleted.BusinessEntityID, Deleted.VacationHours AS BeforeValue, 
       Inserted.VacationHours AS AfterValue
WHERE SalariedFlag = 0;

La clausola OUTPUT viene utilizzata per visualizzare i valori precedenti e successivi alle ferie.

Usare l’istruzione SET con CASE

Voglio assegnare una variabile in base all’esistenza o meno di un valore in una SELECT. Invece di usare il comando IF posso scrivere tutto in una sola riga con il comando CASE.

SET @ContactType = CASE 
        -- Check for employee
        WHEN EXISTS (
                SELECT *
                FROM HumanResources.Employee AS e
                WHERE e.BusinessEntityID = @BusinessEntityID
                )
            THEN 'Employee'
                -- Check for vendor
        WHEN EXISTS (
                SELECT *
                FROM Person.BusinessEntityContact AS bec
                WHERE bec.BusinessEntityID = @BusinessEntityID
                )
            THEN 'Vendor'
        END;

Formattare le colonne raggruppate con ROLLUP

Assumiamo di avere una classica query che, per ogni dipartimento, mi fornisce la media dei salari

SELECT
  Department, AVG(Salary) as AvgSalary
FROM Employees
GROUP BY Department

Ottengo:

Voglio, nella stessa query, sapere quale è il salario medio per l’intera compagnia su tutti i dipartimenti: SQL fornisce la parola chiave ROLLUP per eseguire il comando (AVG in questo caso) su tutti i dati indipendentemente dal raggruppamento.

Il problema è che l’ultima riga che fornisce il sommario ha valore NULL per il dipartimento. SQL Server fornisce una comando GROUPING(columnName) che fornisce valore 1 quando la colonna selezionata è un raggruppamento.
Usando quindi il comando CASE posso ottenere l’output desiderato

SELECT
  CASE 
    WHEN GROUPING(Department) = 1 THEN 'Company Average'
    ELSE Department
  END AS Department,
  AVG(Salary) as AvgSalary
FROM Employees
GROUP BY Department
WITH ROLLUP

Ordinare dinamicamente i risultati di una query

Come indicato negli articoli precedenti, spesso posso avere l’esigenza di eseguire qualcosa di analogo a:

SELECT * FROM Employees ORDER BY @OrderByClause

Che non è possibile. Per risolvere posso usare o le SQL dinamiche (sconsigliato) o un uso virtuoso del CASE, come nell’esempio seguente.

SELECT EmployeeID, FirstName, LastName, SSN, Salary
FROM Employees
ORDER BY
  CASE WHEN @ColumnName='LastName' THEN LastName
       WHEN @ColumnName='SSN' THEN SSN
  END

Raggruppare risultati in RANGE

Assumiamo di dover inviare un report che indica il numero di soldi che un cliente ha pagato negli utlimi 30 giorni, 30-60 giorni, 60-90 e oltre 90 giorni.
Questo obiettivo può essere raggiunto nel seguente modo:

 SELECT
   SUM(CASE 
          WHEN DateDiff(d, InvoiceDate, getdate()) BETWEEN 0 AND 29 THEN InvoiceAmount 
       END) as Current,
   SUM(CASE 
          WHEN DateDiff(d, InvoiceDate, getdate()) BETWEEN 30 AND 59 THEN InvoiceAmount 
       END) as ThirtyToSixty,
   SUM(CASE 
          WHEN DateDiff(d, InvoiceDate, getdate()) BETWEEN 60 AND 89 THEN InvoiceAmount 
       END) as SixtyToNinty,
   SUM(CASE 
          WHEN DateDiff(d, InvoiceDate, getdate()) >= 90 THEN InvoiceAmount 
       END) as NintyPlus
 FROM Invoices