NVL, Decode And Case Functions

Handling NULL values:
Ø  NULL: It is value which is…
  • Unavailable
  • Unassigned
  • Unknown
  • Inapplicable
Ø  A NULL is not same as zero or blank space.
Ø  If a row lacks the data for a particular column, than that value is said to be null or to containing null.
  • Select Ename, job, sal, comm from emp;
Ø  If any column value in an arithmetic expression is null, the overall result is also null.
Ø  The above situation is termed as null propagation and has to be handled very carefully.
  • Select ename, job, sal, comm, sal+comm from emp;
  • Select ename, job, sal, comm, 12 * (sal + comm) from emp;
NVL Function:
Ø  The NVL function is used to convert a null value to an actual value.
Syntax: NVL(expr1, expr2)
Ø  Expr1: it is the source value or expression that may contain null.
Ø  Expr2: it is the target value for converting NULL.
Ø  NVL function can be used to convert any data type; the return value is always the same as the data type of expr1.
Ø  The data types of the source and destination must match.
  • NVL(comm,0)
  • NVL(hiredate,’01-JUN-99’)
  • NVL(job, ‘NOT ASSIGNED’)
Ex:
  • Select ename, sal, comm, sal + nvl(comm,0) from emp;
  • Select ename, sal, comm, (sal * 12) + nvl(comm,0) from emp;
  • Select ename, sal, comm, (sal+500) + nvl(comm,0) from emp; 
DECODE Function:
Ø  It is a single row function.
Ø  The function works on the same principle as the if – then – else.
Ø  We can pass a variable number of values into the call of the decode ()
Ø  Function.
Ø  The first item is always the name of the column that needs to be decoded.
Ø  Once all value-substitute pairs have been defined, we can optionally specify a default value.
Syntax:
  • Select decode(colname, value 1, substitute1, value 2, substitute2, … returndefault) from tablename;
Ø  The function has no restriction on the input and output data type.
Ø  It is the most power full function in oracle.
Ø  The Function can work for only an analysis that considers an equality operator in the logical comparison.
Ex:
  • Select Ename, job, sal,
            Decode(deptno,
                        10,’ACCOUNTING’,
                        20,’RESEARCH’,
                        30,’SALES’,
                        40,’OPERATIONS’,
                        ’OTHERS’) Departments
            From Emp
            Order by Departments;

Working with CASE expressions:
Ø  The case expression can be used to perform if-then-else logic in SQL.
Ø  Case is similar to decode but it is ANSI-compliant.
Ø  It can be used even for executing conditions on range based comparison.
Ø  Case expressions are of two types
  • Simple case expressions
  • Searched case expressions
Simple case expressions:
Ø  These expressions are used to determine the returned value.
Ø  They work with equality comparison only, almost all similar to decode.
Ø  It has a selector which associates to the compared value either from the column or constant.
Ø  The value in the selector is used for comparison with the expressions used in the when clause.
Syntax:
            Case search_expr
                        When expr_1 then result 1
                        When expr_2 then result 2
                        Else default_result
            End
Ex:
  • Select Ename, Deptno,
                        Case
                                    When 10 then ’ACCOUNTS’
                                    When 20 then ’RESEARCH’
                                    When 30 then ’SALES’
                                    When 40 then ’OPERATIONS’
                        Else ’NOT FOUND’
                        End
            From emp;
Searched case expressions:
Ø  The statement uses conditions to determine the returned value.
Ø  It helps in writing multiple conditions for evaluation.
Ø  Used in range analysis of values also.
Syntax:
            Case
                        When condition1 then result1
                        When condition2 then result2
                        When condition N then result
            Else default result
            End
Ex:
  • Select ename, deptno,
                        Case
                                    When deptno = 10 then ’ACCOUNTS’
                                    When deptno = 20 then ’RESEARCH’
                                    When deptno = 30 then ’SALES’
                                    When deptno = 40 then ’OPERATIONS’
                        Else ’not specified’
                        End
            From emp;
  • Select Ename, Sal,
                        Case
                                    When sal >= 800 and sal <= 2000 then ’Lowest pay’
                                    When sal >=2001 and sal <= 4000 then ’Moderate pay’
                        Else ’High pay’
                        End

            From Emp;