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:
Department | AvgSalary |
---|---|
Sales | 78,500.00 |
Marketing | 81,250.00 |
IT | 55,000.50 |
Executive | 91,900.75 |
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.
Department | AvgSalary |
---|---|
Sales | 78,500.00 |
Marketing | 81,250.00 |
IT | 55,000.50 |
Executive | 91,900.75 |
NULL | 76,662.81 |
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