CASE expression,NULLIF,COALESCE

A CASE statement can be either

1. Valued Case statement

2. Searched Case statement

Valued CASE statement:

Syntax:

CASE value-expr  < column or expression involving columns >

WHEN expr1 THEN result1

WHEN expr2 THEN result2

.

.

.

ELSE resultx

END

CASE provides the IF-THEN-ELSE logic in SQL.

In the above WHEN clauses are tested in sequence until we get a hit (match). Once we get a match further WHEN statements are not evaluated.

Example 1:

SELECT

EMPLOYEEID,

CASE DEPARTMENTNO

WHEN 100 THEN 'SUPPORT'

WHEN 200 THEN 'SALES'

WHEN 300 THEN 'FINANCE'

WHEN 400 THEN 'DEVELOPMENT'

ELSE 'UNKNOWN'

END AS DEPERTMENT_NAME

FROM EMPLOYEE

Employeeid	DEPERTMENT_NAME
1	SUPPORT
2	SALES
3	FINANCE
4	DEVELOPMENT
5	SUPPORT
6	SALES
7	FINANCE
8	DEVELOPMENT
9	SUPPORT
10	SALES
11	FINANCE
12	DEVELOPMENT

Example 2: Find the sum of salary for the department 400(without using a where clause in the query)

SELECT SUM( CASE DEPARTMENTNO

            WHEN 400 THEN SALARY

            ELSE 0 /* For all other departments we make salary as 0*/

            END) as salary400

FROM EMPLOYEE;

salary400

24000.00

Example 3: Find the sum of salaries for department number 200 and 300 (without using a where clause)

SELECT SUM( CASE DEPARTMENTNO

            WHEN 200 THEN SALARY

            WHEN 300 THEN SALARY

            ELSE 0 /* For all other departments we make salary as 0*/

            END) as salary400

FROM EMPLOYEE;

39000.00

Example 4: find the ratio of department 100 salaries to total salaries

SELECT SUM( CASE DEPARTMENTNO

            WHEN 400 THEN SALARY

            ELSE 0 /* For all other departments we make salary as 0*/

            END)/ SUM(salary) as salary400ratio

FROM EMPLOYEE;

salary400ratio

0.31

Searched CASE statement:

Syntax of searched CASE statement:

CASE WHEN condition1 THEN value-expr1

     WHEN condition2 THEN value-expr2

     .

     .

ELSE value-expr

END

This syntax is more functional than valued CASE statement.

Advantages as compared to valued CASE statement:
 

1. Conditions can involve equality or non-equality operators.
    
2. Conditions may involve multiple columns.
    

3. Advantage Each condition can refer to different columns.

Example:

SELECT

EMPLOYEEID,

CASE

WHEN DEPARTMENTNO=100 THEN 'SUPPORT'

WHEN DEPARTMENTNO=200 AND Employeeid=2 THEN 'SALES_SPECIAL' /*multiple columns used in Case*/

WHEN DEPARTMENTNO=200 AND Employeeid != 2 THEN 'SALES'

WHEN DEPARTMENTNO=300 THEN 'FINANCE'

WHEN DEPARTMENTNO=400 THEN 'DEVELOPMENT'

ELSE 'UNKNOWN'

END AS DEPERTMENT_NAME

FROM EMPLOYEE;

Employeeid	DEPERTMENT_NAME
1	SUPPORT
2	SALES_SPECIAL
3	FINANCE
4	DEVELOPMENT
5	SUPPORT
6	SALES
7	FINANCE
8	DEVELOPMENT
9	SUPPORT
10	SALES
11	FINANCE
12	DEVELOPMENT

NULLIF :

NULLIF returns a NULL if the equality condition is determined.

NULLIF is a ANSI standard substitute for Teradata NULLIFZERO function.

NULLIF function is commonly applied to denominators to avoid zeros in the denominator. Zero in the denominator produces the SQL error. Applying NULLIF function to denominator produces null result.

Example: When the departmentno is 400 , it returns NULL

SELECT employeeid, NULLIF(departmentno,400) from employee;

Employeeid	<CASE  expression>
8	?
2	200
6	200
4	?
12	?
1	100
3	300
10	200
7	300
11	300
9	100
5	100

COALESCE Function:

COALESCE returns the 1st NON NULL in the expression list

Syntax: COALESCE(Expr-value1, Expr-value2, Expr-value3) --> will return 1st non null value.

If all the expressions are NULL then NULL value will be returned.

Example:

SELECT NAME, COASESCE(office_number,home_number,mobile_number) from employee;

Following is the algorithm:

IF office_number IS NULL

  THEN

    IF home_number is NULL

      THEN

        IF mobile_number iS NULL

          THEN NULL

        ELSE

          mobile_number

    ELSE

        home_number

ELSE

Office_number

COALESCE can be used to convert NULL value to 0 as follows:

Select employee_number, COALESCE(salary, 0) from  employee;