|
Page 4 of 7
NEXT_DAY(date, ‘day’ ) : It gives date of the next occurrence of the specified day after the given date.
SQL> SELECT NEXT_DAY('01-JAN-05','FRI') FROM DUAL;
NEXT_DAY( (Next Friday after 1-jan-05 is on 7-jan-05)
---------
07-JAN-05
TO_DATE (string): This function converts a string into an Oracle date.
SQL> SELECT TO_DATE('01 JANUARY 2005','DD MONTH YYYY') FROM DUAL;
TO_DATE('
---------
01-JAN-05
SQL> SELECT TO_DATE('MAR 05 01','MON YY DD') FROM DUAL;
TO_DATE('
---------
01-MAR-05
SQL> SELECT TO_DATE('01/01/05', 'DD/MM/YY') FROM DUAL;
TO_DATE('
---------
01-JAN-05
The USER, SYSDATE Functions:
USER function displays login name of the user.
SQL> SELECT USER FROM DUAL;
USER
------------------------------
SCOTT
SYSDATE function displays system date.(Date in your windows)
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
---------
1-JAN-05
CONVERSION FUNCTIONS:
TO_CHAR: This function is used to convert a date or number to character string.
SQL> SELECT TO_CHAR(SYSDATE,'DAY DD MONTH YYYY') FROM DUAL
TO_CHAR (SYSDATE,'DAYDDMONTH
---------------------------
SATURDAY 01 JANUARY 2005
SQL> SELECT TO_CHAR (SYSDATE,' DD DY MM YY') FROM DUAL;
TO_CHAR (SYSDA
-------------
01 SAT 01 05
NVL() Function: This function is used to substitute any null value with a user-defined value.
Consider the following data from EMP table of SCOTT.
SQL> SELECT EMPNO, ENAME, SAL, COMM FROM EMP;
EMPNO ENAME SAL COMM
---------- ---------- ---------- -------------------------
7369 SMITH 800
7499 ALLEN 1600 300
7521 WARD 1250 500
7566 JONES 2975
7654 MARTIN 1250 1400
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500 0
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
In the above table except for 7499, 7521, 7654 and 7844 all others commissions are null
To display their commission as “0” (zero)
We can use NVL() function as shown below.
SQL> SELECT EMPNO, ENAME, SAL, NVL (COMM, 100) FROM EMP
EMPNO ENAME SAL NVL(COMM,100)
---------- ---------- ---------- -------------
7369 SMITH 800 100
7499 ALLEN 1600 300
7521 WARD 1250 500
7566 JONES 2975 100
7654 MARTIN 1250 1400
7698 BLAKE 2850 100
7782 CLARK 2450 100
7788 SCOTT 3000 100
7839 KING 5000 100
7844 TURNER 1500 0
7876 ADAMS 1100 100
7900 JAMES 950 100
7902 FORD 3000 100
7934 MILLER 1300 100
SQL> SELECT EMPNO, ENAME, SAL, NVL (COMM, 888) FROM EMP
EMPNO ENAME SAL NVL(COMM,888)
---------- ---------- ---------- -------------
7369 SMITH 800 888
7499 ALLEN 1600 300
7521 WARD 1250 500
7566 JONES 2975 888
7654 MARTIN 1250 1400
7698 BLAKE 2850 888
7782 CLARK 2450 888
7788 SCOTT 3000 888
7839 KING 5000 888
7844 TURNER 1500 0
7876 ADAMS 1100 888
7900 JAMES 950 888
7902 FORD 3000 888
7934 MILLER 1300 888
In above queries we have seen how to substitute a value when the comm is null.
If one want to display "Commission not payed" against the employees who have no
commission we can write the following query.
SQL > SELECT ENAME, SAL, NVL(TO_CHAR(COMM),'Commission Not Payed') FROM EMP;
ENAME SAL NVL(TO_CHAR(COMM),'COMMISSIONNOTPAYED')
---------- ---------- ----------------------------------------
SMITH 800 Commission Not Payed
ALLEN 1600 300
WARD 1250 500
JONES 2975 Commission Not Payed
MARTIN 1250 1400
BLAKE 2850 Commission Not Payed
CLARK 2450 Commission Not Payed
SCOTT 3000 Commission Not Payed
KING 5000 Commission Not Payed
TURNER 1500 0
ADAMS 1100 Commission Not Payed
JAMES 950 Commission Not Payed
FORD 3000 Commission Not Payed
MILLER 1300 Commission Not Payed
|
