Who is part of the window

View All Public Scripts Login to Run Script

Script Name Who is part of the window
Visibility Unlisted - anyone with the share link can access
Description Testing methods of finding out which rows are part of the result of an analytic windowing clause
Area SQL General / Analytics
Contributor Kim Berg Hansen
Created Thursday November 12, 2015

Statement 1
Create a collection type to hold employee ids
```
create type employees_table_type as table of number
```
```
ORA-00955: name is already used by an existing object
```
More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 2

Where clause just to give me a small resultset of 9 rows

The base statement using analytic window clause

select department_id, employee_id, last_name, salary
     , sum(salary) over (
          partition by department_id
          order by salary
          range between unbounded preceding and current row
       ) range_sum
  from hr.employees
 where department_id in (30,90)
 order by department_id, salary

 
DEPARTMENT_ID EMPLOYEE_ID LAST_NAME SALARY RANGE_SUM
30 119 Colmenares 2500 2500
30 118 Himuro 2600 5100
30 117 Tobias 2800 7900
30 116 Baida 2900 10800
30 115 Khoo 3100 13900
30 114 Raphaely 11000 24900
90 102 De Haan 17000 34000
90 101 Kochhar 17000 34000
90 100 King 24000 58000

9 rows selected.

DEPARTMENT_ID	EMPLOYEE_ID	LAST_NAME	SALARY	RANGE_SUM
30	119	Colmenares	2500	2500
30	118	Himuro	2600	5100
30	117	Tobias	2800	7900
30	116	Baida	2900	10800
30	115	Khoo	3100	13900
30	114	Raphaely	11000	24900
90	102	De Haan	17000	34000
90	101	Kochhar	17000	34000
90	100	King	24000	58000

Statement 3

This raises exception ORA-06553: PLS-306: wrong number or types of arguments in call to 'SYS_NT_COLLECT'

Would love for this to work, but no :-(

select department_id, employee_id, last_name, salary
     , sum(salary) over (
          partition by department_id
          order by salary
          range between unbounded preceding and current row
       ) range_sum
     , cast(collect(employee_id order by salary) over (
          partition by department_id
          order by salary
          range between unbounded preceding and current row
       ) as employees_table_type) who_in_range
  from hr.employees
 where department_id in (30,90)
 order by department_id, salary

ORA-06553: PLS-306: wrong number or types of arguments in call to 'SYS_NT_COLLECT'

More Details: https://docs.oracle.com/error-help/db/ora-06553

Statement 4

This raises exception ORA-30487: ORDER BY not allowed here

Or I could work with this, but also no go :-(

select department_id, employee_id, last_name, salary
     , sum(salary) over (
          partition by department_id
          order by salary
          range between unbounded preceding and current row
       ) range_sum
     , listagg(employee_id,',') within group (
          order by salary
       ) over (
          partition by department_id
          order by salary
          range between unbounded preceding and current row
       ) who_in_range
  from hr.employees
 where department_id in (30,90)
 order by department_id, salary

ORA-30487: ORDER BY not allowed here

More Details: https://docs.oracle.com/error-help/db/ora-30487

Statement 5

Unfortunately this does not show good output here on livesql, it does not support output of collection types :-(

This works, but only because I know there are max 9 rows in each window

select department_id, employee_id, last_name, salary
     , sum(salary) over (
          partition by department_id
          order by salary
          range between unbounded preceding and current row
       ) range_sum
     , employees_table_type(
          nth_value(employee_id,1) over (
             partition by department_id
             order by salary
             range between unbounded preceding and current row
          )
        , nth_value(employee_id,2) over (
             partition by department_id
             order by salary
             range between unbounded preceding and current row
          )
        , nth_value(employee_id,3) over (
             partition by department_id
             order by salary
             range between unbounded preceding and current row
          )
        , nth_value(employee_id,4) over (
             partition by department_id
             order by salary
             range between unbounded preceding and current row
          )
        , nth_value(employee_id,5) over (
             partition by department_id
             order by salary
             range between unbounded preceding and current row
          )
        , nth_value(employee_id,6) over (
             partition by department_id
             order by salary
             range between unbounded preceding and current row
          )
        , nth_value(employee_id,7) over (
             partition by department_id
             order by salary
             range between unbounded preceding and current row
          )
        , nth_value(employee_id,8) over (
             partition by department_id
             order by salary
             range between unbounded preceding and current row
          )
        , nth_value(employee_id,9) over (
             partition by department_id
             order by salary
             range between unbounded preceding and current row
          )
       ) who_in_range
  from hr.employees
 where department_id in (30,90)
 order by department_id, salary

 
DEPARTMENT_ID EMPLOYEE_ID LAST_NAME SALARY RANGE_SUM WHO_IN_RANGE
30 119 Colmenares 2500 2500 [unsupported data type]
30 118 Himuro 2600 5100 [unsupported data type]
30 117 Tobias 2800 7900 [unsupported data type]
30 116 Baida 2900 10800 [unsupported data type]
30 115 Khoo 3100 13900 [unsupported data type]
30 114 Raphaely 11000 24900 [unsupported data type]
90 101 Kochhar 17000 34000 [unsupported data type]
90 102 De Haan 17000 34000 [unsupported data type]
90 100 King 24000 58000 [unsupported data type]

9 rows selected.

DEPARTMENT_ID	EMPLOYEE_ID	LAST_NAME	SALARY	RANGE_SUM	WHO_IN_RANGE
30	119	Colmenares	2500	2500	[unsupported data type]
30	118	Himuro	2600	5100	[unsupported data type]
30	117	Tobias	2800	7900	[unsupported data type]
30	116	Baida	2900	10800	[unsupported data type]
30	115	Khoo	3100	13900	[unsupported data type]
30	114	Raphaely	11000	24900	[unsupported data type]
90	101	Kochhar	17000	34000	[unsupported data type]
90	102	De Haan	17000	34000	[unsupported data type]
90	100	King	24000	58000	[unsupported data type]

Additional Information

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