Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Wednesday, 6 February 2013

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;