CASE Statement in SQL

CASE Statement in SQL

Can we use CASE Statement in Where clause?

It's also important to understand that in SQL, CASE is an expression and not an executable "control-of-flow" statement. Thus, the CASE expression, when evaluated, just returns a value which can then be tested or otherwise combined with other expressions (in statements). You cannot use the result of a CASE expression directly as the predicate of a WHERE clause. Thus:

Code:

SELECT * FROM sometable

WHERE CASE WHEN somecondition THEN 1

            WHEN someothercondition THEN 2

            ELSE ... END

is not legal, because the WHERE clause would be the equivalent of

Code:

WHERE 1

 or

WHERE 2

which is not legal syntax. Instead, the code could read:

Code:

SELECT * FROM sometable

WHERE 1 = CASE WHEN somecondition THEN 1

                WHEN someothercondition THEN 2

                ELSE ... END

and that's legal.

Use the CASE Statement in a SQL SELECT Clause

SQL Server provides a mechanism for returning different values in a SELECT clause based on Boolean conditions: the CASE statement. This statement resembles Visual Basics Select Case statement.

The SQL CASE statement has WHEN, THEN, and ELSE clauses along with an END terminator. The syntax is:

CASE [expression]

        WHEN [value | Boolean expression] THEN [return value]

        [ELSE [return value]]

END

The [expression] is optional and contains a table column or a variable. When you specify [expression] directly after the CASE, you must populate the [value] parameter in the WHEN clause:

DECLARE @TestVal int

SET @TestVal = 3

SELECT

        CASE @TestVal

               WHEN 1 THEN 'First'

               WHEN 2 THEN 'Second'

               WHEN 3 THEN 'Third'

               ELSE 'Other'

        END

SQL Server compares this value to the expression and when the values match, it returns the THEN clauses [return value]. If none of the WHEN clauses equates to true, SQL Server returns the [return value] in the optional ELSE clause. If the ELSE clause is omitted and no value is matched, NULL is returned.

If you dont specify [expression], you must include the [Boolean expression] in the WHEN clause. This can contain any valid Boolean expression SQL Server allows:

DECLARE @TestVal int

SET @TestVal = 5

SELECT

        CASE

               WHEN @TestVal <=3 THEN 'Top 3'

               ELSE 'Other'

        END

Using CASE in an ORDER BY clause

The following examples uses the CASE expression in an ORDER BY clause to determine the sort order of the rows based on a given column value.

Transact-SQL

SELECT BusinessEntityID, SalariedFlag

FROM HumanResources.Employee

ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC

        ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;

GO

Transact-SQL

SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName

FROM Sales.vSalesPerson

WHERE TerritoryName IS NOT NULL

ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName

         ELSE CountryRegionName END;

Using CASE in a HAVING clause

The following example uses the CASE expression in a HAVING clause to restrict the rows returned by the SELECT statement. The statement returns the the maximum hourly rate for each job title in the HumanResources.Employee table. The HAVING clause restricts the titles to those that are held by men with a maximum pay rate greater than 40 dollars or women with a maximum pay rate greater than 42 dollars.

Transact-SQL

USE AdventureWorks2012;

GO

SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate

FROM HumanResources.Employee AS e

JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID

GROUP BY JobTitle

HAVING (MAX(CASE WHEN Gender = 'M' 

        THEN ph1.Rate 

        ELSE NULL END) > 40.00

     OR MAX(CASE WHEN Gender  = 'F' 

        THEN ph1.Rate  

        ELSE NULL END) > 42.00)

ORDER BY MaximumRate DESC;