which situation EXIST condition is better than IN, and vice versa.

which situation EXIST condition is better than IN, and vice versa.

ANSWER:

Result of the subquery is small Then "IN" is typicaly more appropriate.
and
Result of the subquery is big/large/long Then "EXIST" is more appropriate.



suppose :-


select * from table_1
where id in (select Id from table_2)

Is normaly processed as:
select * from table_1 , ( select distinct y from table_2 ) t2
where table_1.x = table_2.y;

Means the subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table

and oposite is (EXIST)

select * from table_1 where exists ( select null from table_2 where table_2.y = table_1.x )
That is processed more like:
for x in ( select * from table_1 ) loop
if ( exists ( select null from table_2 where table_2.y = x.x ) then
OUTPUT THE RECORD end
end if
end loop

It always results in a full scan of Table_1 whereas the first query can make use of an index on Table_1(x).

ROW_NUMBER, RANK, DENSE_RANK IN ORACLE

Top-n Query or 3th highest row by sql quiery in oracle .

Using ROW_NUMBER, RANK, DENSE_RANK

=====================================

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 13 13:06:27 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn scott@test107
Enter password:
Connected.
SQL>
SQL>
SQL> break on deptno skip 1
SQL>
SQL> select deptno, ename,sal,
2 row_number()
3 over (partition by deptno
4 order by sal desc)rn,
5 rank()
6 over (partition by deptno
7 order by sal desc)rnk,
8 dense_rank()
9 over (partition by deptno
10 order by sal desc)drnk
11 from emp
12 order by deptno, sal desc
13 /

DEPTNO ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ---------- ----------
10 KING 5000 1 1 1
CLARK 2450 2 2 2
MILLER 1300 3 3 3

20 SCOTT 3000 1 1 1
FORD 3000 2 1 1
JONES 2975 3 3 2
ADAMS 1100 4 4 3
SMITH 1000 5 5 4

30 BLAKE 2850 1 1 1

DEPTNO ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ---------- ----------
30 ALLEN 1600 2 2 2
TURNER 1500 3 3 3
MARTIN 1250 4 4 4
WARD 1250 5 4 4
JAMES 950 6 6 5


14 rows selected.

SQL>
SQL> select ename,sal,
2 row_number()
3 over (order by sal desc)rn,
4 rank()
5 over (order by sal desc)rnk,
6 dense_rank()
7 over (order by sal desc)drnk
8 from emp
9 order by sal desc
10 /

ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ----------
KING 5000 1 1 1
FORD 3000 2 2 2
SCOTT 3000 3 2 2
JONES 2975 4 4 3
BLAKE 2850 5 5 4
CLARK 2450 6 6 5
ALLEN 1600 7 7 6
TURNER 1500 8 8 7
MILLER 1300 9 9 8
WARD 1250 10 10 9
MARTIN 1250 11 10 9

ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ----------
ADAMS 1100 12 12 10
SMITH 1000 13 13 11
JAMES 950 14 14 12

14 rows selected.

SQL> ed
Wrote file afiedt.buf

1 select ename,sal,rn,rnk,drnk
2 from (
3 select ename,sal,
4 row_number()
5 over (order by sal desc)rn,
6 rank()
7 over (order by sal desc)rnk,
8 dense_rank()
9 over (order by sal desc)drnk
10 from emp
11 order by sal desc
12 )
13* where rn=5
SQL> /

ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ----------
BLAKE 2850 5 5 4

SQL> ed
Wrote file afiedt.buf

1 select ename,sal,rn,rnk,drnk
2 from (
3 select ename,sal,
4 row_number()
5 over (order by sal desc)rn,
6 rank()
7 over (order by sal desc)rnk,
8 dense_rank()
9 over (order by sal desc)drnk
10 from emp
11 order by sal desc
12 )
13* where rn=3
SQL>
SQL>
SQL> /

ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ----------
FORD 3000 3 2 2

SQL>
SQL>
SQL>

Day wise total SQL Query with opening and colsing balance

Example-1
==============

with t as
(
select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
--- select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
--- select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 3 as daynum, 'DR' as trantype, -500 as amount from dual
)
select *
from t
model
dimension by (daynum,cast(trantype as /*VARCHAR2(15)*/chAR(15)) trantype)
measures (amount)
rules upsert all
( amount[for daynum from 1 to 4 increment 1,'Opening balance'] = 0
, amount[any,'Closing balance'] order by daynum = sum(amount)
[daynum <= cv(daynum),trantype != 'Closing balance']
, amount[daynum != 1,'Opening balance'] = amount[cv()-1,'Closing balance']
)
order by daynum
, decode(trantype,'Opening balance',1,'CR',2,'DR',3,'Closing balance',4)
/


===========================
EXAMPLE-2
===========================

with tran as
(
select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, 1 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, 1 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 3 as daynum, 'DR' as trantype, -500 as amount from dual
), days as
( SELECT LEVEL daynum FROM DUAL CONNECT BY LEVEL <= 4)
SELECT daynum
, DECODE (trantype,'OB','Opening Balance','CB','Closing Balance',trantype) descr
, CASE
/* For opening balance/closing balance
amount will = sum of previous days transactions
*/
WHEN (trantype IN ('OB','CB'))
THEN NVL (SUM(amount) OVER (ORDER BY daynum,DECODE (trantype,'OB',1,'CB',3,2)),0)
ELSE amount
END amount
FROM
(
SELECT d.daynum
, CASE
WHEN ( GROUPING_ID (d.daynum,1)>0
AND GROUPING_ID (d.daynum,2)>0
)
THEN t.trantype
WHEN ( GROUPING_ID (d.daynum,1)>0)
THEN 'OB'
WHEN ( GROUPING_ID (d.daynum,2)>0)
THEN 'CB'
END trantype
, t.amount
FROM tran t
, days d
WHERE d.daynum = t.daynum(+)
/* ROWNUM required to preserve duplicate transactions */
GROUP BY GROUPING SETS ((d.daynum,1),(d.daynum,2),(d.daynum,t.trantype,t.amount,ROWNUM))
)
WHERE trantype IS NOT NULL /* Do not show NULL transaction
for days with no transaction
(outer join)
*/
ORDER BY daynum,DECODE (trantype,'OB',1,'CB',3,2)
/


===========================
EXAMPLE-3
===========================


with t as
2 (
3 select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
4 select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
5 select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
6 select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
7 select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
8 select 3 as daynum, 'DR' as trantype, -500 as amount from dual
9 ),
10 data
11 as
12 ( select daynum, r
13 from (select level daynum from dual connect by level <= 4),
14 (select level r from dual connect by level <= 2 )
15 )
16 select daynum,
17 r,
18 ttype,
19 amt,
20 case when ttype in ( 'close', 'open' )
21 then sum(samt) over (order by daynum, r)
22 end theamount
23 from (
24 select data.daynum,
25 data.r,
26 case when (nvl(t.trantype,'CR') = 'CR' and data.r = 1 )
27 then 'open'
28 when (nvl(t.trantype,'DR') = 'DR' and data.r = 2)
29 then 'close'
30 else t.trantype
31 end ttype,
32 case when (t.trantype = 'CR' and r = 2) or (t.trantype = 'DR' and r=1)
33 then t.amount
34 end amt,
35 case when (t.trantype = 'DR' and r = 2)
36 then sum(case when r=1 then t.amount end) over (partition by
data.daynum)
37 else 0
38 end samt
39 from t, data
40 where data.daynum = t.daynum (+)
41 )
42 order by daynum, r, decode( ttype, 'open', 1, 'CR', 2, 'DR', 3, 'close', 4 )
43 /



===========================
EXAMPLE-4
===========================



SQL> with my_tab as (select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
2 select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
3 select 1 as daynum, 'CR' as trantype, 400 as amount from dual union all
4 select 1 as daynum, 'DR' as trantype, -500 as amount from dual union all
5 select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
6 select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
7 select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
8 select 3 as daynum, 'DR' as trantype, -500 as amount from dual union all
9 select 4 as daynum, 'CR' as trantype, 900 as amount from dual union all
10 select 5 as daynum, 'CR' as trantype, 900 as amount from dual union all
11 select 5 as daynum, 'CR' as trantype, 200 as amount from dual union all
12 select 5 as daynum, 'DR' as trantype, -500 as amount from dual union all
13 select 6 as daynum, 'DR' as trantype, -200 as amount from dual),
14 dummy as (select 1 id, 'Opening Balance' descr from dual union all
15 select 2 id, 'CR' descr from dual union all
16 select 3 id, 'DR' descr from dual union all
17 select 4 id, 'Closing Balance' descr from dual)
18 select mt.daynum,
19 d.descr,
20 mt.trantype,
21 decode(d.id, 1, 0,
22 2, amount,
23 3, amount,
24 4, sum(decode(d.id, 1, 0, 4, 0, amount)) over (partition by mt.daynum))
amo
unt
25 from (select mt1.*, row_number() over (partition by mt1.daynum order by mt1.trantype) rn
26 from my_tab mt1) mt
27 left outer join dummy d partition by (id, descr) on (mt.trantype = d.descr)
28 where case when d.id = 1 and rn = 1 then 1
29 when d.id = 2 and mt.trantype = 'CR' then 1
30 when d.id = 3 and mt.trantype = 'DR' then 1
31 when d.id = 4 and rn = 1 then 1
32 else 0
33 end = 1
34 order by mt.daynum, d.id;



===========================
EXAMPLE-2
===========================

with t as (
select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 3 as daynum, 'DR' as trantype, -500 as amount from dual
)
select daynum,
trim(trantype),
amount
from t
model
dimension by (
daynum,
cast(trantype as char(15)) trantype
)
measures (
amount,
daynum + 1 dnum
)
rules upsert all (
dnum[1,'Opening balance'] = max(dnum)[any,any],
amount[for daynum from 1 to dnum[1,'Opening balance'] increment 1,'Opening balance'] = 0,
amount[any,'Closing balance'] order by daynum = sum(amount)[daynum <= cv(daynum),trantype != 'Closing balance'],
amount[daynum != 1,'Opening balance'] = amount[cv()-1,'Closing balance']
)
order by daynum,
decode(trantype,'Opening balance',1,'CR',2,'DR',3,'Closing balance',4)
/

Menu list of query of my software (tree node)

select count(progtype)
from (

select de,progname,progtype from (
select a.*,b.PROGTYPE from (
select PARENTNM, levlcode, decode(levlcode,1,progdesc,2,' '||progdesc,3,' '||progdesc,4,' '||progdesc,' '||progdesc) de,
decode(levlcode, 1,'nxtblk',2,'nxtblk',3,'nxtblk',4,'paste','paste') leave,progname ,MENUSLNO
from symentre
where levlcode != 0
start with PARENTNM='ROOTP'
connect by prior progname=parentnm
) a, symenmas b
where a.progname =b.progname(+)
)
start with PARENTNM='ROOT'
connect by prior progname=parentnm


)
where nvl(progtype,'X') ='R'

Find quarter date with sql query

select add_months(to_date(to_char('0101'||to_char(:P_Date,'YYYY')),'ddmmyyyy'),to_char(:P_Date,'Q')*3)-1
from dual


select add_months(to_date(to_char('0101'||to_char(:P_Date,'YYYY')),'ddmmyyyy'),to_char(:P_Date,'Q')*3)-1
from(select sysdate from dba_objects
where rownum<=4)

select add_months(last_day(:ysdate),to_char(:ysdate,'Q')*3) as year_quarter
from dual;

ALL DIFFERENCES FROM TWO TABLE

CREATE TABLE T11
(
N1 NUMBER NULL,
N2 NUMBER NULL,
V1 VARCHAR2(215 BYTE) NULL
)
/


Insert into T11 (N1, N2, V1)
Values (1, 1, 'HALIM');

Insert into T11 (N1, N2, V1)
Values (0, 0, 'x');

Insert into T11 (N1, N2, V1)
Values (0, 0, 'x ');

COMMIT;



CREATE TABLE T22
(
N1 NUMBER NULL,
N2 NUMBER NULL,
V1 VARCHAR2(215 BYTE) NULL
)
/


Insert into T22 (N1, N2, V1)
Values (1, 1, 'HALIM');

Insert into T22 (N1, N2, V1)
Values (2, 2, 'x');

Insert into T22 (N1, N2, V1)
Values (3, 3, 'rubel');


COMMIT;


---ALL DIFFERENCES FROM TWO TABLE

Select * from
(
select 'HTEST.T11' "Row Source", a.* from
(
select /*+ FULL(Tbl1) PARALLEL(Tbl1, 4) */
N1, N2, V1
from HTEST.T11 Tbl1
minus
select /*+ FULL(Tbl2) PARALLEL(Tbl2, 4) */
N1, N2, V1
from HTEST.T22 Tbl2
) A
union all
select 'HTEST.T2', b.* from
(
select /*+ FULL(Tbl2) PARALLEL(Tbl2, 4) */
N1, N2, V1
from HTEST.T22 Tbl2
minus
select /*+ FULL(Tbl1) PARALLEL(Tbl1, 4) */
N1, N2, V1
from HTEST.T11 Tbl1
) B
)
Order by 1

efficiant way to check the table is empty

----very efficiant way to check the table is empty
--or
-----checking the first rows value
=====================================================
---empno is primary_key column
--or
---empno is index column

select /*+ FIRST_ROWS */ empno from emp where rownum = 1;

How to fine User name of current session

User name of current session
===============================

SQL> SELECT USERNAME
from v$session
where audsid = sys_context('userenv','sessionid');

sql for Days of Month

=============================
=============================
sql for Days of year
=============================
=============================

SELECT TRUNC(SYSDATE,'Y')+ROWNUM-1 THEDATE ,To_char(SYSDATE,'mm')
FROM ( SELECT 1 just_a_column
FROM dual
CONNECT BY LEVEL <= 366
)
where rownum <= add_months(trunc(:p11,'MM'),12) - trunc(:p11,'MM')


SELECT TRUNC(SYSDATE,'Y')+ROWNUM-1 THEDATE
FROM ( SELECT 1 just_a_column
FROM dual
CONNECT BY LEVEL <= 366
)
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'Y'),12)-TRUNC(SYSDATE,'Y')



select trunc(:p11,'MM')-1+rownum Date_month
from all_objects
where rownum <= add_months(trunc(:p11,'MM'),12) - trunc(:p11,'MM')


=================================
=================================
sql for Days of Month
=================================
=================================

SELECT TRUNC(SYSDATE,'Y')+ROWNUM-1 THEDATE ,To_char(SYSDATE,'mm')
FROM ( SELECT 1 just_a_column
FROM dual
CONNECT BY LEVEL <= 366
)
where rownum <= add_months(trunc(:p11,'MM'),1) - trunc(:p11,'MM')



select trunc(:p11,'MM')-1+rownum Date_month
from all_objects
where rownum <= add_months(trunc(:p11,'MM'),1) - trunc(:p11,'MM')

SQL or PL/SQL techniques to generate rows.

=======================================================
=======================================================
SQL or PL/SQL techniques to generate rows.
=======================================================
=======================================================

1.

SELECT ROWNUM
FROM ( SELECT 1 FROM DUAL GROUP BY CUBE(1,2,3,4,5,6,7,8,9,10) )
WHERE ROWNUM <= &N

2.

SELECT *
FROM ( SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <= &N )

3.

SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <= &N;

4.

create or replace type row_table as table of number;


Create or replace function gen_row (num_rows in number)
return row_table
parallel_enable pipelined is
begin
for x in 1..num_rows loop
pipe row (x);
end loop;
return;
end;


select * from table(gen_row(&N));

5.

select to_number(column_value) colval
from xmltable('for $i in 1 to &n return $i');

6.

select integer_value
from dual
where 1=2
model
dimension by ( 0 as key )
measures ( 0 as integer_value )
rules upsert ( integer_value[ for key from 1 to 9 increment 1 ] = cv(key) );

7.

select rownum from all_objects where rownum<=5;


8.

select val
from (select 1 rn from dual)
model
dimension by (rn)
measures (1 val)
rules
iterate (&N)
( val[iteration_number] = iteration_number+1 )
order by val


[note: this is not mine]

Rows to columns wise query

Oracle 9i xmlagg

In Oracle 9i we can use the xmlagg function to aggregate multiple rows onto one column:

select
deptno,
rtrim (xmlagg (xmlelement (e, ename || ',')).extract ('//text()'), ',') enames
from
emp
group by
deptno
;

DEPTNO ENAMES
---------- ----------------------------------------
10 CLARK,MILLER,KING
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD



Use 11g SQL pivot for single row output


The SQL pivot operator allows you to take multiple rows and display them on a single line.

select *
from
(select fk_department
from employee)
pivot
(count(fk_department)
for fk_department in ('INT', 'WEL', 'CEN', 'POL'));

'INT' 'WEL' 'CEN' 'POL'
---------- ---------- ---------- -------
7 6 0 8


Use SQL within group for moving rows onto one line and listagg to display multiple column values in a single column

In Oracle 11g, we have the within group SQL clause to pivot multiple rows onto a single row. We also a have direct SQL mechanism for non first-normal form SQL display. This allows multiple table column values to be displayed in a single column, using the listagg built-in function :

select
deptno,
listagg (ename, ',')
WITHIN GROUP
(ORDER BY ename) enames
FROM
emp
GROUP BY
deptno
/
DEPTNO ENAMES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD


Use the SYS_CONNECT_BY_PATH operator



select
deptno,
substr(SYS_CONNECT_BY_PATH(lname, ','),2) name_list
from
(
select
lname,
deptno,
count(*) OVER ( partition by deptno ) cnt,
ROW_NUMBER () OVER ( partition by deptno order by lname) seq
from emp
where
deptno is not null)
where
seq=cnt
start with
seq=1
connect by prior
seq+1=seq
and prior
deptno=deptno;

DEPTNO NAME_LIST
1 Komers,Mokrel,Stenko
2 Hung,Tong
3 Hamer
4 Mansur


Use a Cross join


Matt contributed this handy SQL techniques to pivot one row of several columns into a single column with several row, using the Oracle Cross join syntax. Matt notes that the Cross join "has other uses in conjunction with a WHERE clause to create triangular result sets for rolling totals etc (though thanks to analytic functions those things are very nice and easy)".

SELECT
ite,
case
when ite = 'item1' then item1
when ite = 'item2' then item2
when ite = 'item3' then item3
end as val
FROM
(
SELECT
pivoter.ite,
item1,
item2,
item3
FROM
someTable
CROSS JOIN
(
select 'item1' as ite from dual
UNION ALL
select 'item2' as ite from dual
UNION ALL
select 'item3' as ite from dual
)pivoter
)



Use the Oracle analytic Lag-Over Function


Analytic functions have a pronounced performance improvement since they avoid an expensive self-join and only make one full-table scan to get the results. This site shows an example of using the Oracle LAG function to display multiple rows on a single column:

SELECT
ename,
hiredate,sal,LAG (sal, 1, 0)
OVER (ORDER BY hiredate) AS PrevSal
FROM
emp
WHERE
job = 'CLERK';

Use the SQL CASE operator to pivot rows onto one line

You can use the CASE statement to create a crosstab to convert the rows to columns. Below, the Oracle CASE function to create a "crosstab" of the results, such as this example from SearchOracle:

select Sales.ItemKey
, sum(Sales.QtySold) as Qty
, sum(
case when OH.MOHClass = 'Fixed'
then OH.Amt
else .00 end ) as MOHFixed
, sum(
case when OH.MOHClass = 'Var'
then OH.Amt
else .00 end ) as MOHVar
, sum(
case when OH.MOHClass = 'Cap'
then OH.Amt
else .00 end ) as MOHCap
from Sales
left outer
join OH
on Sales.ItemKey = OH.ItemKey
group
by Sales.ItemKey

Horizontally data generate by QUERY

SQL>
CREATE TABLE LOOP_TABLE
(
EMPID NUMBER,
EMP_TIME DATE,
EMPSTATUS NUMBER
);


SQL> select * from loop_table;

EMPID EMP_TIME EMPSTATUS
---------- --------- ----------
1 01-JAN-08 800
1 02-JAN-08 588
1 03-JAN-08 478
1 04-JAN-08 123
1 05-JAN-08 789
2 01-JAN-08 111
2 02-JAN-08 222
2 03-JAN-08 555
2 04-JAN-08 333
2 05-JAN-08 444

10 rows selected.

SQL> select empid
2 , max(decode(emp_time, '01-JAN-08', empstatus)) "JAN 1ST"
3 , max(decode(emp_time, '02-JAN-08', empstatus)) "JAN 2ND"
4 , max(decode(emp_time, '03-JAN-08', empstatus)) "JAN 3RD"
5 , max(decode(emp_time, '04-JAN-08', empstatus)) "JAN 4TH"
6 , max(decode(emp_time, '05-JAN-08', empstatus)) "JAN 5TH"
7 from (select * from loop_table)
8 group by empid;

EMPID JAN 1ST JAN 2ND JAN 3RD JAN 4TH JAN 5TH
---------- ---------- ---------- ---------- ---------- ----------
1 800 588 478 123 789
2 111 222 555 333 444

Computes the cumulative running sum

CREATE TABLE demo (
submit_date DATE NOT NULL,
num_votes NUMBER NOT NULL);

INSERT INTO demo VALUES (TRUNC(SYSDATE)-4, 100);
INSERT INTO demo VALUES (TRUNC(SYSDATE)-3, 150);
INSERT INTO demo VALUES (TRUNC(SYSDATE)-2, 75);
INSERT INTO demo VALUES (TRUNC(SYSDATE)-3, 25);
INSERT INTO demo VALUES (TRUNC(SYSDATE)-1, 50);
COMMIT;

SELECT * FROM demo;

SELECT submit_date, num_votes, SUM(num_votes)
OVER(ORDER BY submit_date ROWS UNBOUNDED PRECEDING) TOT_VOTE
FROM demo
ORDER BY submit_date;

analytical query

Previous value and Next value
----------------------------

select empno , sal,
lead(sal) over(order by to_number(empno)) lead1,
lag(sal) over(order by to_number(empno)) lag1
from emp

Runing avg balance
-------------------

CREATE TABLE demo (
submit_date DATE NOT NULL,
num_votes NUMBER NOT NULL);

INSERT INTO demo VALUES (TRUNC(SYSDATE)-
4, 100);

INSERT INTO demo VALUES (TRUNC(SYSDATE)-
3, 150);

INSERT INTO demo VALUES (TRUNC(SYSDATE)-
2, 75);

INSERT INTO demo VALUES (TRUNC(SYSDATE)-
3, 25);

INSERT INTO demo VALUES (TRUNC(SYSDATE)-
1, 50);

COMMIT;

SELECT * FROM demo;

SELECT submit_date, num_votes, TRUNC(AVG
(num_votes)
OVER(ORDER BY submit_date ROWS UNBOUNDED
PRECEDING)) AVG_VOTE_PER_DAY
FROM demo
ORDER BY submit_date;

SELECT submit_date, num_votes, TRUNC(AVG
(num_votes)
OVER(PARTITION BY submit_date ORDER BY
submit_date ROWS UNBOUNDED PRECEDING))
AVG_VOTE_PER_DAY
FROM demo
ORDER BY submit_date;


Returns a running count of all records or by partition
-------------------------------------------------------
COUNT(<*, [DISTINCT | ALL] >) OVER ()

SELECT submit_date, num_votes, TRUNC(COUNT(num_votes)
OVER(ORDER BY submit_date ROWS UNBOUNDED PRECEDING)) AS DAY_COUNT
FROM demo
ORDER BY submit_date;

SELECT submit_date, COUNT(*)
OVER(PARTITION BY submit_date ORDER BY submit_date
ROWS UNBOUNDED PRECEDING) NUM_RECS
FROM demo;

Return a frequency distribution
-----------------------------

CREATE TABLE test (
prod1 NUMBER(3),
prod2 NUMBER(3),
prod3 NUMBER(3));

INSERT INTO test VALUES (34,23,45);
INSERT INTO test VALUES (34,22,34);
INSERT INTO test VALUES (54,44,45);
INSERT INTO test VALUES (23,22,45);
INSERT INTO test VALUES (45,22,34);

SELECT prod1, COUNT(prod1) OVER (PARTITION BY prod1) freq1,
prod2, COUNT(prod2) OVER (PARTITION BY prod2) freq2,
prod3, COUNT(prod3) OVER (PARTITION BY prod3) freq3
FROM test;


MAX

Returns the maximum value by partition
---------------------------------------
MAX ( expression) OVER ()
conn oe/oe

SELECT manager_id, last_name, salary
FROM (
SELECT manager_id, last_name, salary,
MAX(salary) OVER (PARTITION BY manager_id) AS rmax_sal
FROM employees)
WHERE salary = rmax_sal;


Returns the minimum value by partition
--------------------------------------
MIN ( expression) OVER ()
conn oe/oe

SELECT manager_id, last_name, salary
FROM (
SELECT manager_id, last_name, salary,
MAX(salary) OVER (PARTITION BY manager_id) AS rmax_sal
FROM employees)
WHERE salary = rmax_sal;

Get seconds between dates

CREATE OR REPLACE FUNCTION time_diff (
DATE_1 IN DATE, DATE_2 IN DATE) RETURN NUMBER IS

NDATE_1 NUMBER;
NDATE_2 NUMBER;
NSECOND_1 NUMBER(5,0);
NSECOND_2 NUMBER(5,0);

BEGIN
-- Get Julian date number from first date (DATE_1)
NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, 'J'));

-- Get Julian date number from second date (DATE_2)
NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, 'J'));

-- Get seconds since midnight from first date (DATE_1)
NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, 'SSSSS'));

-- Get seconds since midnight from second date (DATE_2)
NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, 'SSSSS'));

RETURN (((NDATE_2 - NDATE_1) * 86400)+(NSECOND_2 - NSECOND_1));
END time_diff;
/

USE OF CASE AND DECODE

CASE AND DECODE : Two powerful constructs of SQL
________________________________________
CASE and DECODE are the two widely used constructs in the SQL . And both have the functionality of an IF-THEN-ELSE statement to return some specified value meeting some criteria.Even though they are used interchangeably there are some differences between them.

This article tries to show list the advantage of CASE over DECODE and also explain how to convert DECODE to CASE and vice versa.

CASE was introduced in Oracle 8.1.6 as a replacement for the DECODE . Anyway it is much better option than DECODE as it is ,

1. More Flexible than DECODE

2. More easier to read

3. ANSI Compatible

4. compatible in PL/SQL Context

SIMPLE CASE

Generally CASE has two syntaxes as below

a. Expression Syntax
Code:
CASE [ expression ]
WHEN Value_1 THEN result_1
WHEN Value_2 THEN result_2
...
WHEN Value_n THEN result_n
[ELSE else_result]
END
Here CASE checks the value of Expression and returns the result each time for each record as specified. Here is one such example to list the new salaries for all employees
Code:
SQL> SELECT EMPNO,JOB , SAL ,
2 CASE JOB WHEN 'ANALYST' THEN SAL*1.2
3 WHEN 'MANAGER' THEN SAL*1.4
4 ELSE SAL END NEWSAL
5 FROM EMP;

EMPNO JOB SAL NEWSAL
---------- --------- ---------- ----------
7369 CLERK 800 800
7499 SALESMAN 1600 1600
7521 SALESMAN 1250 1250
7566 MANAGER 2975 4165
7654 SALESMAN 1250 1250
7698 MANAGER 2850 3990
7782 MANAGER 2450 3430
7788 ANALYST 3000 3600
7839 PRESIDENT 5000 5000
7844 SALESMAN 1500 1500
7876 CLERK 1100 1100
7900 CLERK 950 950
7902 ANALYST 3000 3600
7934 CLERK 1300 1300

14 rows selected.

SQL>
The Equivalent DECODE syntax will be
Code:
SQL> SELECT EMPNO,JOB , SAL ,
2 DECODE (JOB,'ANALYST', SAL*1.2 ,
3 'MANAGER', SAL*1.4,
4 SAL ) NEWSAL
5 FROM EMP;

EMPNO JOB SAL NEWSAL
---------- --------- ---------- ----------
7369 CLERK 800 800
7499 SALESMAN 1600 1600
7521 SALESMAN 1250 1250
7566 MANAGER 2975 4165
7654 SALESMAN 1250 1250
7698 MANAGER 2850 3990
7782 MANAGER 2450 3430
7788 ANALYST 3000 3600
7839 PRESIDENT 5000 5000
7844 SALESMAN 1500 1500
7876 CLERK 1100 1100
7900 CLERK 950 950
7902 ANALYST 3000 3600
7934 CLERK 1300 1300

14 rows selected.

SQL>
b. Conditional syntax
Code:
CASE
WHEN Condition_1 THEN result_1
WHEN Condition_2 THEN result_2
...
WHEN Condition_n THEN result_n
[ELSE else_result]
END
Here CASE tries to return the values on meeting some conditions rather than checking for the expressions . Here is such an example with the same functionality as above .
Code:
SQL> SELECT EMPNO, JOB , SAL ,
2 CASE WHEN JOB='ANALYST' THEN SAL*1.2
3 WHEN JOB='MANAGER' THEN SAL*1.4
4 ELSE SAL END NEWSAL
5 FROM EMP;

EMPNO JOB SAL NEWSAL
---------- --------- ---------- ----------
7369 CLERK 800 800
7499 SALESMAN 1600 1600
7521 SALESMAN 1250 1250
7566 MANAGER 2975 4165
7654 SALESMAN 1250 1250
7698 MANAGER 2850 3990
7782 MANAGER 2450 3430
7788 ANALYST 3000 3600
7839 PRESIDENT 5000 5000
7844 SALESMAN 1500 1500
7876 CLERK 1100 1100
7900 CLERK 950 950
7902 ANALYST 3000 3600
7934 CLERK 1300 1300

14 rows selected.

SQL>
Even DECODE can be a replacement for such condition scenario ,but it cannot have as flexibility as CASE can have . Here is such scenario to display the hiked the salary for only for those employees who were hired before 01-JAN-1982.
Code:
SQL> SELECT EMPNO,HIREDATE ,JOB , SAL ,
2 CASE WHEN HIREDATE 3 THEN SAL*1.6
4 ELSE SAL
5 END NEWSAL
6 FROM EMP;

EMPNO HIREDATE JOB SAL NEWSAL
---------- --------- --------- ---------- ----------
7369 17-DEC-80 CLERK 800 1280
7499 20-FEB-81 SALESMAN 1600 2560
7521 22-FEB-81 SALESMAN 1250 2000
7566 02-APR-81 MANAGER 2975 4760
7654 28-SEP-81 SALESMAN 1250 2000
7698 01-MAY-81 MANAGER 2850 4560
7782 09-JUN-81 MANAGER 2450 3920
7788 09-DEC-82 ANALYST 3000 3000
7839 17-NOV-81 PRESIDENT 5000 8000
7844 08-SEP-81 SALESMAN 1500 2400
7876 12-JAN-83 CLERK 1100 1100
7900 03-DEC-81 CLERK 950 1520
7902 03-DEC-81 ANALYST 3000 4800
7934 23-JAN-82 CLERK 1300 1300

14 rows selected.

SQL>
The Equivalent code for DECODE will be ,
Code:
SQL> SELECT EMPNO ,HIREDATE ,JOB , SAL ,
2 DECODE (LEAST ( HIREDATE , TO_DATE('01/01/1982','DD/MM/YYYY')),
3 HIREDATE ,SAL*1.6,
4 SAL) NEWSAL
5 FROM EMP;

EMPNO HIREDATE JOB SAL NEWSAL
---------- --------- --------- ---------- ----------
7369 17-DEC-80 CLERK 800 1280
7499 20-FEB-81 SALESMAN 1600 2560
7521 22-FEB-81 SALESMAN 1250 2000
7566 02-APR-81 MANAGER 2975 4760
7654 28-SEP-81 SALESMAN 1250 2000
7698 01-MAY-81 MANAGER 2850 4560
7782 09-JUN-81 MANAGER 2450 3920
7788 09-DEC-82 ANALYST 3000 3000
7839 17-NOV-81 PRESIDENT 5000 8000
7844 08-SEP-81 SALESMAN 1500 2400
7876 12-JAN-83 CLERK 1100 1100
7900 03-DEC-81 CLERK 950 1520
7902 03-DEC-81 ANALYST 3000 4800
7934 23-JAN-82 CLERK 1300 1300

14 rows selected.

SQL>
But here CASE query is more flexible , readable and elegant.

NESTED CASE

What we discussed till now is about the simple CASE . But it can be nested also. Lets make it clear with some examples . Here is such an example to hike the salaries only for Analysts and Managers joined before 01-JAN-1982 and Analysts joined on or after the same date.
Code:
SQL> SELECT EMPNO,HIREDATE ,JOB , SAL ,
2 CASE WHEN HIREDATE < TO_DATE('01/01/1982','DD/MM/YYYY')
3 THEN CASE WHEN JOB='ANALYST' THEN SAL*1.2
4 WHEN JOB='MANAGER' THEN SAL*1.4
5 ELSE SAL END
6 ELSE CASE WHEN JOB='ANALYST' THEN SAL*1.6
7 ELSE SAL END END NEWSAL
8 FROM EMP;

EMPNO HIREDATE JOB SAL NEWSAL
---------- --------- --------- ---------- ----------
7369 17-DEC-80 CLERK 800 800
7499 20-FEB-81 SALESMAN 1600 1600
7521 22-FEB-81 SALESMAN 1250 1250
7566 02-APR-81 MANAGER 2975 4165
7654 28-SEP-81 SALESMAN 1250 1250
7698 01-MAY-81 MANAGER 2850 3990
7782 09-JUN-81 MANAGER 2450 3430
7788 09-DEC-82 ANALYST 3000 4800
7839 17-NOV-81 PRESIDENT 5000 5000
7844 08-SEP-81 SALESMAN 1500 1500
7876 12-JAN-83 CLERK 1100 1100
7900 03-DEC-81 CLERK 950 950
7902 03-DEC-81 ANALYST 3000 3600
7934 23-JAN-82 CLERK 1300 1300

14 rows selected.

SQL>
It equivalent decode statement will be
Code:
SQL> SELECT EMPNO,HIREDATE ,JOB , SAL ,
2 DECODE (LEAST ( HIREDATE , TO_DATE('01/01/1982','DD/MM/YYYY')),
3 HIREDATE ,
4 DECODE (JOB,'ANALYST', SAL*1.2 ,
5 'MANAGER', SAL*1.4,
6 SAL ),
7 DECODE (JOB, 'ANALYST', SAL*1.6,
8 SAL )) NEWSAL
9 FROM EMP;

EMPNO HIREDATE JOB SAL NEWSAL
---------- --------- --------- ---------- ----------
7369 17-DEC-80 CLERK 800 800
7499 20-FEB-81 SALESMAN 1600 1600
7521 22-FEB-81 SALESMAN 1250 1250
7566 02-APR-81 MANAGER 2975 4165
7654 28-SEP-81 SALESMAN 1250 1250
7698 01-MAY-81 MANAGER 2850 3990
7782 09-JUN-81 MANAGER 2450 3430
7788 09-DEC-82 ANALYST 3000 4800
7839 17-NOV-81 PRESIDENT 5000 5000
7844 08-SEP-81 SALESMAN 1500 1500
7876 12-JAN-83 CLERK 1100 1100
7900 03-DEC-81 CLERK 950 950
7902 03-DEC-81 ANALYST 3000 3600
7934 23-JAN-82 CLERK 1300 1300

14 rows selected.

SQL>
One major advantage of CASE over DECODE

CASE works good in bot SQL and PL/SQL Context while DECODE is supported only in SQL Context. Below example explains this scenario.
Code:
SQL> CREATE OR REPLACE FUNCTION GIMME_VAL ( N NUMBER)
2 RETURN NUMBER AS
3 BEGIN
4 RETURN N*N ;
5 END;
6 /

Function created.

SQL> variable x number
SQL> begin
2 :x :=10;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select Gimme_val(decode(:x,0,1,:x)) from dual;

GIMME_VAL(DECODE(:X,0,1,:X))
----------------------------
100

SQL> select Gimme_val(CASE :x WHEN 0 THEN 1 ELSE :x End ) from dual;

GIMME_VAL(CASE:XWHEN0THEN1ELSE:XEND)
------------------------------------
100

SQL>
Both are working well in SQL context . And to test it in PL/SQL , lets create two procedures ; one with CASE and another with DECODE.
Code:
SQL> CREATE OR REPLACE PROCEDURE GETME_VAL_1 AS
2 X NUMBER :=10 ;
3 Z NUMBER ;
4 BEGIN
5 Z := GIMME_VAL(CASE X WHEN 0 THEN 1 ELSE X END );
6 DBMS_OUTPUT.PUT_LINE('GIMME_VAL = '||Z);
7 END;
8 /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE GETME_VAL_2 AS
2 X NUMBER :=10;
3 Z NUMBER;
4 BEGIN
5 Z := GIMME_VAL(DECODE(X,0,1,X));
6 DBMS_OUTPUT.PUT_LINE('GIMME_VAL = '||Z);
7 END;
8 /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE GETME_VAL_2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1 PL/SQL: Statement ignored
5/16 PLS-00204: function or pseudo-column 'DECODE' may be used inside
a SQL statement only

SQL>
We got one compilation error while creating a procedure to use the DECODE inside the PL/SQL context . ie "function or pseudo-column 'DECODE' may be used inside a SQL statement only"

Anyway there is alternative to overcome this error. We need to incorporate the DECODE in an Select statement as below.
Code:
SQL> CREATE OR REPLACE PROCEDURE GETME_VAL_2 AS
2 X NUMBER :=10;
3 Z NUMBER;
4 BEGIN
5 SELECT GIMME_VAL(DECODE(X,0,1,X))
6 INTO Z
7 FROM DUAL;
8 DBMS_OUTPUT.PUT_LINE('GIMME_VAL = '||Z);
9 END;
10 /

Procedure created.

SQL>
And now both the procedure will work fine .
Code:
SQL> Exec getme_val_1 ;
GIMME_VAL = 100

PL/SQL procedure successfully completed.

SQL> Exec getme_val_2 ;
GIMME_VAL = 100

PL/SQL procedure successfully completed.

SQL>
An interesting scenario

Its really interesting !!! Understand the scenario without any explanation .
Code:
SQL> SELECT DECODE (NULL,NULL,'NULL','NOTNULL') NICE
2 FROM DUAL;

NICE
----
NULL

SQL> SELECT CASE NULL
2 WHEN NULL THEN 'NULL'
3 ELSE 'NOTNULL' END NICE
4 FROM DUAL;

NICE
-------
NOTNULL

SQL> SELECT CASE
2 WHEN NULL IS NULL THEN 'NULL'
3 ELSE 'NOTNULL' END NICE
4 FROM DUAL;

NICE
-------
NULL

SQL>
Conclusion

Though both CASE and DECODE are used interchangeably , CASE is definitely proved to be better and elegant option over DECODE as it is more flexible , readable and ANSI Compatible . And moreover CASE is perffered in PL/SQL statements as DECODE is only supported in the SQL statements.

For every record in the master table i want the records from the detail table

select nvl2( empno, null, deptno ) deptno,
2 nvl2( empno, null, dname ) dname,
3 empno, ename, sal
4 from (
5 select d.deptno deptno, d.dname, e.empno empno, e.ename, e.sal
6 from dept d, emp e
7 where d.deptno = e.deptno
8 union all
9 select d.deptno, d.dname, null, null, null
10 from dept d
11 order by deptno, empno NULLS FIRST
12 ) x
13 /

DEPTNO DNAME EMPNO ENAME SAL
---------- -------------- ---------- ---------- ----------
10 ACCOUNTING
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
20 RESEARCH
7369 SMITH 800
7566 JONES 2975
7788 SCOTT 3000
7876 ADAMS 1100
7902 FORD 3000
30 SALES
7499 ALLEN 1600
7521 WARD 1250
7654 MARTIN 1250
7698 BLAKE 2850
7844 TURNER 1500
7900 JAMES 950
40 OPERATIONS

18 rows selected.

use of REgexp_replace

The innermost regexp_replace gets rid of all the non-letter characters out of the string:

SQL> with src as ( select 'Internet + Multimedia Services (KT Whole)' col_1 from dual)
2 select regexp_replace(col_1,'([^A-Za-z ])','')
3 from src;

REGEXP_REPLACE(COL_1,'([^A-ZA-Z])','')
--------------------------------------
Internet Multimedia Services KT Whole


The second Regexp_replace takes the output from the first, and looks for strings matching the pattern '3 letters followed by any number of characters that aren't spaces. It replaces each of these strings with the '3 letters' part of the string.

SQL> with src as ( select 'Internet Multimedia Services KT Whole' col_1 from dual)
2 select regexp_replace(col_1,'([A-Za-z]{3})[^ ]*','\1')
3 from src;

REGEXP_REPLACE(COL_
-------------------
Int Mul Ser KT Who


The final regexp_replace takes this output, and replaces 1 or more contiguous spaces with a single underscore character:

SQL> with src as ( select 'Int Mul Ser KT Who' col_1 from dual)
2 select regexp_replace(col_1,'([ ]+)','_')
3 from src;

REGEXP_REPLACE(COL
------------------
Int_Mul_Ser_KT_Who
Report message to a moderator

--------------------------------------------
Another option using regex:


SQL> select a1
2 , rtrim(regexp_replace(a1,'([^[:alpha:]]*)([[:alpha:]]{1,3})([^ ]*)', '\2_'), '_') str
3 from full_name;

A1 STR
--------------------------------------------- -------------------------
Internet & Multimedia Services (KT Whole) Int_Mul_Ser_KT_Who
ABCD + XYZ Corp (PQ Half) ABC_XYZ_Cor_PQ_Hal
A B C D E F G A_B_C_D_E_F_G


with src as ( select 'Internet + Multimedia Services (KT Whole)' col_1 from dual)
select regexp_replace(regexp_replace(regexp_replace(col_1,'([^A-Za-z ])',''),'([A-Za-z]{3})[^ ]*','\1'),'([ ]+)','_')
from src;


with src as ( select 'Internet + Multimedia Services (KT Whole)' col_1 from dual)
select regexp_replace(regexp_replace(regexp_replace(col_1,'([^A-Za-z ])',''),'([A-Za-z]{3})[^ ]*','\1'),'([ ]+)','_')
from src;

REGEXP_LIKE

select ename,sal,deptno from scott.emp
where
ename not like '%ED%'
and
ename not like '%CD%'
and
ename not like '%FD%'
and
ename not like '%RD%'
and
ename not like '%SD%'
and
ename not like '%KD%' ;

you can change above query very simply--------

select ename,sal,deptno
from scott.emp
where not REGEXP_LIKE(ename,'RD|ED|CD|FD|SD|KD')


select ename,sal,deptno from emp
where not regexp_like(ename, '(E|C|F|R|S|K)D');


Also you can do,
SELECT ename, sal, deptno
FROM emp_test
WHERE NOT REGEXP_LIKE (ename, '[ECFRSK]D');


A longer format using like is,

select ename, sal, deptno
from emp, table (sys.dbms_debug_vc2coll (
'ED',
'CD',
'FD',
'RD',
'SD',
'KD'))
where ename not like '%' || column_value || '%';
Or,select ename,sal,deptno from emp
where not regexp_like(ename, '(ED|CD|FD|RD|SD|KD)');

GROUP WISE QUERY

select deptno,
decode(grouping(ename),
1, 'Total dept '||deptno||': '||count(*) over(partition by deptno),
deptno) dept,
decode(grouping(ename),
1, to_number(null),
row_number() over(partition by deptno order by ename)) rn,
ename
from emp
group by rollup(deptno,ename)
having grouping(deptno) = 0
order by deptno, rn
/