Sample Script

View All Public Scripts Login to Run Script

Script Name Sample Script
Visibility Unlisted - anyone with the share link can access
Description Script for ASK Tom experts
Area SQL Analytics
Contributor harismart28@yahoo.co.in
Created Wednesday December 12, 2018

Statement 1

create table test1 (col1 number,col2 varchar2(5),col3 date,col4 number,col5 number,col6 number)

Table created.

Statement 2

insert into test1 values(1,'A',trunc(sysdate),0,5.5,2)

1 row(s) inserted.

Statement 3

insert into test1 values(1,'A',trunc(sysdate)+1,0,3.5,3)

1 row(s) inserted.

Statement 4

insert into test1 values(1,'A',trunc(sysdate)+2,0,2.5,0)

1 row(s) inserted.

Statement 5

insert into test1 values(1,'A',trunc(sysdate)+3,0,6.5,0)

1 row(s) inserted.

Statement 6

insert into test1 values(1,'A',trunc(sysdate)+4,0,8,0)

1 row(s) inserted.

Statement 7

select test1.*,sum(col5) over (partition by col1,col2 order by col3 desc,col4 desc rows col6 preceding ) derived_field from test1 
where col1=1 order by col1,col2,col3,col4


COL1 COL2 COL3 COL4 COL5 COL6 DERIVED_FIELD
1 A 12-DEC-18 0 5.5 2 11.5
1 A 13-DEC-18 0 3.5 3 20.5
1 A 14-DEC-18 0 2.5 0 2.5
1 A 15-DEC-18 0 6.5 0 6.5
1 A 16-DEC-18 0 8 0 8

5 rows selected.

COL1	COL2	COL3	COL5	COL6	DERIVED_FIELD
1	A	12-DEC-18	5.5	2	11.5
1	A	13-DEC-18	3.5	3	20.5
1	A	14-DEC-18	2.5	0	2.5
1	A	15-DEC-18	6.5	0	6.5
1	A	16-DEC-18	8	0	8

Statement 8

insert into test1 values(2,'B',trunc(sysdate),0,5.5,2)

1 row(s) inserted.

Statement 9

insert into test1 values(2,'B',trunc(sysdate),1,3.5,3)

1 row(s) inserted.

Statement 10

insert into test1 values(2,'B',trunc(sysdate)+1,0,3.5,5)

1 row(s) inserted.

Statement 11

insert into test1 values(2,'B',trunc(sysdate)+2,0,2.5,2)

1 row(s) inserted.

Statement 12

insert into test1 values(2,'B',trunc(sysdate)+3,0,6.5,0)

1 row(s) inserted.

Statement 13

insert into test1 values(2,'B',trunc(sysdate)+4,0,8,0)

1 row(s) inserted.

Statement 14

insert into test1 values(2,'B',trunc(sysdate)+4,1,7.9,0)

1 row(s) inserted.

Statement 15

insert into test1 values(2,'B',trunc(sysdate)+5,0,10.5,0)

1 row(s) inserted.

Statement 16

insert into test1 values(2,'B',trunc(sysdate)+6,0,1.5,0)

1 row(s) inserted.

Statement 17

select test1.*,sum(col5) over (partition by col1,col2 order by col3 desc,col4 desc rows col6 preceding )derived_field from test1 
where col1=2 order by col1,col2,col3,col4


COL1 COL2 COL3 COL4 COL5 COL6 DERIVED_FIELD
2 B 12-DEC-18 0 5.5 2 12.5
2 B 12-DEC-18 1 3.5 3 16
2 B 13-DEC-18 0 3.5 5 38.9
2 B 14-DEC-18 0 2.5 2 17
2 B 15-DEC-18 0 6.5 0 6.5
2 B 16-DEC-18 0 8 0 8
2 B 16-DEC-18 1 7.9 0 7.9
2 B 17-DEC-18 0 10.5 0 10.5
2 B 18-DEC-18 0 1.5 0 1.5

9 rows selected.

COL1	COL2	COL3	COL4	COL5	COL6	DERIVED_FIELD
2	B	12-DEC-18	0	5.5	2	12.5
2	B	12-DEC-18	1	3.5	3	16
2	B	13-DEC-18	0	3.5	5	38.9
2	B	14-DEC-18	0	2.5	2	17
2	B	15-DEC-18	0	6.5	0	6.5
2	B	16-DEC-18	0	8	0	8
2	B	16-DEC-18	1	7.9	0	7.9
2	B	17-DEC-18	0	10.5	0	10.5
2	B	18-DEC-18	0	1.5	0	1.5

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database