Miscellaneous Single Row Functions
COALESCE
Coalesce function returns the first not null value in the expression list.
Example.
The following query returns salary+commision, if commission is null then returns salary, if salary is also null then returns 1000.
select empno,ename,salary,comm,coalesce(salary+comm,salary,1000) “Net
Sal” from emp;
ENAME SALARY COMM NET SAL
----- ------ ---- -------
SMITH 1000 100
1100
SAMI 3000 3000
SCOTT 1000
RAVI
200 1000
DECODE
DECODE(expr, searchvalue1, result1,searchvalue2,result2,..., defaultvalue)
Decode functions compares an expr with search value one by one. If the expr does not match any of the search value then returns the default value. If the default value is omitted then returns null.
Example
The following query returns the department names according the deptno. If the deptno does not match any of the search value then returns “Unknown Department”
select decode(deptno,10,'Sales',20,'Accounts,30,'Production,
40,'R&D','Unknown Dept') As DeptName from emp;
DEPTNAME
----------
Sales
Accounts
Unknown Dept.
Accounts
Production
Sales
R&D
Unknown Dept.
GREATEST
GREATEST(expr1, expr2, expr3,expr4...)
Returns the greatest expr from a expr list.
Example
select greatest(10,20,50,20,30) from dual;
GREATEST
--------
50
select greatest('SAMI','SCOTT','RAVI','SMITH','TANYA') from dual;
GREATEST
--------
TANYA
LEAST
LEAST(expr1, expr2, expr3,expr4...)
It is simillar to greatest. It returns the least expr from the expression list.
select least(10,20,50,20,30) from dual;
LEAST
--------
10
select least('SAMI','SCOTT','RAVI','SMITH','TANYA') from dual;
LEAST
--------
RAVI
NVL
NVL2(expr1,expr2)
This function is oftenly used to check null values. It returns expr2 if the expr1 is null, otherwise returns expr1.
Example
The following query returns commission if commission is null then returns 'Not Applicable'.
Select ename,nvl(comm,'Not Applicable') “Comm” from dual;
ENAME COMM
------ ----
Scott 300
Tiger 450
Sami Not Applicable
Ravi 300
Tanya Not Applicable
NVL2
NVL2(expr1,expr2,expr3)
NVL2 returns expr2 if expr1 is not null, otherwise return expr3.
Example
The following query returns salary+comm if comm is not null, otherwise just returns salary.
select salary,comm,nvl2(comm,salary+comm,salary) “Income” from emp;
SALARY COMM INCOME
------ ---- ------
1000 100
1100
2000
2000
2300 200
2500
3400
3400
NULLIF
NULLIF(expr1, expr2)
Nullif compares expr1 with expr2. If they are equal then returns null, otherwise return expr1.
Example.
The following query shows old jobs of those employees who have changed their jobs in the company by comparing the current job with old job in oldemp table.
Select ename,nullif(e.job,o.job) “Old Job” from emp e, oldemp o where
e.empno=o.empno;
ENAME OLD JOB
----- -------
SMITH CLERK
SAMI
SCOTT MANAGER
UID
Returns the current session ID of user logged on.
Example
select uid from dual;
UID
----
20
USER
Returns the username of the current user logged on.
select user from dual;
USER
---------
SCOTT
SYS_CONTEXT
SYS_CONTEXT returns the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements.
EXAMPLE
The following query returns the username of the current user.
Select sys_context('USERENV','SESSION_USER') “Username” from dual;
USERNAME
---------
SCOTT
Similar to SESSION_USER parameter for namespace USERENV the other important parameters are
ISDBA :To check whether the current user is having DBA privileges or not.
HOST :Returns the name of host machine from which the client is connected.
INSTANCE :The instance identification number of the current instance
IP_ADDRESS: IP address of the machine from which the client is connected.
DB_NAME :Name of the database as specified in the DB_NAME initialization parameter
VSIZE
VSIZE(expr)
Returns the internal representation of expr in bytes.
Example
The following query return the representation of ename in bytes.
select ename,vsize(ename) as Bytes from emp;
ENAME BYTES
------ ------
SCOTT 5
SAMI 4
RAVI 4
KIRAN 5