Hi friends today we will discuss about LEAD & LAG Functions..

Lead:

       This lead function will work like order by in sql with more flavours ..

select * from emp order by sal asc;

EMPNO	ENAME	JOB	MGR	JOIN_DATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-DEC-80 00:00:00	800		20
7900	JAMES	CLERK	7698	03-DEC-81 00:00:00	950		30
7876	ADAMS	CLERK	7788	12-JAN-83 00:00:00	1100		20
7654	MARTIN	SALESMAN	7698	28-SEP-81 00:00:00	1250	1400	30
7521	WARD	SALESMAN	7698	22-FEB-81 00:00:00	1250	500	30
7934	MILLER	CLERK	7782	23-JAN-82 00:00:00	1300		10
7844	TURNER	SALESMAN	7698	08-SEP-81 00:00:00	1500	0	30
7499	ALLEN	SALESMAN	7698	20-FEB-81 00:00:00	1600	300	30
7782	CLARK	MANAGER	7839	09-JUN-81 00:00:00	2450		10
7698	BLAKE	MANAGER	7839	01-MAY-81 00:00:00	2850		30
7566	JONES	MANAGER	7839	02-APR-81 00:00:00	2975		20
7902	FORD	ANALYST	7566	03-DEC-81 00:00:00	3000		20
7788	SCOTT	ANALYST	7566	09-DEC-82 00:00:00	3000		20
7839	11raj	PRESIDENT		17-NOV-81 00:00:00	5000		10

Lead function:

select empno,ename,sal,Lead(sal,0) over (order by sal)  as lead_sal from emp;

O/p:

EMPNO	ENAME	SAL	Lead_sal
7369	SMITH	800	800
7900	JAMES	950	950
7876	ADAMS	1100	1100
7654	MARTIN	1250	1250
7521	WARD	1250	1250
7934	MILLER	1300	1300
7844	TURNER	1500	1500
7499	ALLEN	1600	1600
7782	CLARK	2450	2450
7698	BLAKE	2850	2850
7566	JONES	2975	2975
7902	FORD	3000	3000
7788	SCOTT	3000	3000
7839	11raj	5000	5000

ex2:

   select empno,ename,sal,Lead(sal,1) over (order by sal) from emp;
1 record is in lead..
     

EMPNO	ENAME	SAL	Lead_sal
7369	SMITH	800	950
7900	JAMES	950	1100
7876	ADAMS	1100	1250
7654	MARTIN	1250	1250
7521	WARD	1250	1300
7934	MILLER	1300	1500
7844	TURNER	1500	1600
7499	ALLEN	1600	2450
7782	CLARK	2450	2850
7698	BLAKE	2850	2975
7566	JONES	2975	3000
7902	FORD	3000	3000
7788	SCOTT	3000	5000
7839	11raj	5000

Lag Function:

    select empno,ename,sal,lag(sal,1) over (order by sal)  as lag_sal from emp;

1 record in lag..

EMPNO	ENAME	SAL	Lag_sal
7369	SMITH	800
7900	JAMES	950	800
7876	ADAMS	1100	950
7654	MARTIN	1250	1100
7521	WARD	1250	1250
7934	MILLER	1300	1250
7844	TURNER	1500	1300
7499	ALLEN	1600	1500
7782	CLARK	2450	1600
7698	BLAKE	2850	2450
7566	JONES	2975	2850
7902	FORD	3000	2975
7788	SCOTT	3000	3000
7839	11raj	5000	3000

Thank u..!!!!

Hi friends today we will discuss about LISTAGG in ORACLE..

SYNTAX:

LISTAGG(<Column Name>,”) WITHIN GROUP(ORDER BY <Column Name1>,<Column Name2>)

Example:

CREATE TABLE EMPLOYE
(
ENO VARCHAR2(10),
DNO VARCHAR2(10),
ENAME VARCHAR2(20),
SALARY VARCHAR2(10)
);

SELECT * FROM EMPLOYE;

ENO	DNO	ENAME	SALARY
101	10	RAJESH	18000
102	10	SURESH	28000
103	11	NARAYANA	18000
104	11	KIRAN	18000

From this we need the department wise employee names as in single row with
separated by ‘; ’ .

SELECT LISTAGG(ENAME,’; ‘) WITHIN GROUP(ORDER BY DNO) as EMPLOYEE NAMES,  DNO  AS Dept.No.
FROM   EMPLOYE   GROUP   BY   DNO;

Result:

EMPLOYEE NAMES

Dept.No.

RAJESH; SURESH

10

KIRAN; NARAYANA

11

Good Morning friends today we will discuss about the concept of COALESCE in ORACLE..

COALESCE returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurrences of expr evaluate to null, then the function returns null.

Syntax: COALESCE (expr1, expr2);

This is equivalent to

CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END;

Example 1:

Select  COALESCE (1*2,3+4) FROM DUAL;

Output  1 :

2

Example 2:

Select  COALESCE (NULL,3+4) FROM DUAL;

Output 2:

7

Thankyou...!!!!

LEVEL in Oracle

DECODE in Oracle

CASE in Oracle..