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;