## Pages

### 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’
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;