Skip to main content

Posts

Showing posts from October, 2011

Sorting by day of the week

Have you thought of sorting by the day of the week.

Firstly you have to get the day of the week, check the statement below to get the day of the week.























If you try to order day of the week by using ORDER BY on column 3 which is a character column, ORDER BY treats this as text and sorts it from A-Z. So if your intention is to sort from MONDAY to SUNDAY, beginning with  MONDAY as day1 and SUNDAY as day7 then you need to use either CASE or DECODE functions.

Check an example with decode below.

10 comments
Read more

Difference between NVL and NVL2

Definition:
NVL ( expr1 , expr2 )
If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.
Example:
Login as scott user and access table EMP.

First insert a row in table EMP using the following statement.

INSERT INTO EMP VALUES(321,'MITHUN',NULL,7782,sysdate,600,300,10);
In the above statement am trying to insert an employee MITHUN with job value NULL.

Now,

SELECT ename,job,NVL(JOB,'NO DESIGNATION') from emp;
Here first column is name, second is job and third column is again job with NVL function. For all other employees whose job is not null the value returned from NVL function is job but in case of ename MITHUN nvl returns the second argument that is 'NO DESIGNATION'.

Now consider the next statement,

SELECT ename,job,NVL(JOB,123) from emp;
In the above statement am trying to replace job with 123 in case if it is NULL.


This gets executed and 123 is displayed instead of NULL for ename MITHUN. This is because Oracle database does a imp…
5 comments
Read more