Oracle Live SQL - Script: How to Write Powerful SQL That’s Easy to Maintain

Statement 1

create table countries as  
  select * from hr.countries

Table created.

Statement 2

create table locations as  
  select * from hr.locations

Table created.

Statement 3

create table regions as  
  select * from hr.regions

Table created.

Statement 4

create table departments as  
  select * from hr.departments

Table created.

Statement 5

create table employees as  
  select * from hr.employees

Table created.

Statement 6

select department_id, salary, 
       count(*) over ( partition by department_id ) emp# 
from   employees e


DEPARTMENT_ID SALARY EMP#
10 4400 1
20 13000 2
20 6000 2
30 11000 6
30 3100 6

Rows 1 - 5. More rows exist.

DEPARTMENT_ID	SALARY	EMP#
10	4400	1
20	13000	2
20	6000	2
30	11000	6
30	3100	6

Statement 7

select * from ( 
  select department_id, salary, 
         count(*) over ( partition by department_id ) emp# 
  from   employees e 
) 
where  emp# > 3


DEPARTMENT_ID SALARY EMP#
30 11000 6
30 3100 6
30 2900 6
30 2800 6
30 2600 6

Rows 1 - 5. More rows exist.

DEPARTMENT_ID	SALARY	EMP#
30	11000	6
30	3100	6
30	2900	6
30	2800	6
30	2600	6

Statement 8

with employees_with_dept_counts as ( 
  select e.*, 
         count(*) over ( partition by department_id ) emp# 
  from   employees e 
) 
select * 
from   employees_with_dept_counts e 
where  emp# > 3


EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID EMP#
114 Den Raphaely DRAPHEAL 515.127.4561 07-DEC-02 PU_MAN 11000  - 100 30 6
115 Alexander Khoo AKHOO 515.127.4562 18-MAY-03 PU_CLERK 3100  - 114 30 6
116 Shelli Baida SBAIDA 515.127.4563 24-DEC-05 PU_CLERK 2900  - 114 30 6
117 Sigal Tobias STOBIAS 515.127.4564 24-JUL-05 PU_CLERK 2800  - 114 30 6
118 Guy Himuro GHIMURO 515.127.4565 15-NOV-06 PU_CLERK 2600  - 114 30 6

Rows 1 - 5. More rows exist.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID	EMP#
114	Den	Raphaely	DRAPHEAL	515.127.4561	07-DEC-02	PU_MAN	11000	-	100	30	6
115	Alexander	Khoo	AKHOO	515.127.4562	18-MAY-03	PU_CLERK	3100	-	114	30	6
116	Shelli	Baida	SBAIDA	515.127.4563	24-DEC-05	PU_CLERK	2900	-	114	30	6
117	Sigal	Tobias	STOBIAS	515.127.4564	24-JUL-05	PU_CLERK	2800	-	114	30	6
118	Guy	Himuro	GHIMURO	515.127.4565	15-NOV-06	PU_CLERK	2600	-	114	30	6

Statement 9

select * from hr.departments d
join   hr.locations l
  using  ( location_id )
join   hr.countries c
  using  ( country_id )
join   hr.regions r
  using  ( region_id );


REGION_NAME TOTAL_SALARY MEAN_SALARY
Europe 304500 8956
Americas 261708 4221

2 rows selected.

REGION_NAME	TOTAL_SALARY	MEAN_SALARY
Europe	304500	8956
Americas	261708	4221

Statement 10

with employees_with_dept_counts as (
  select e.*,
         count(*) over ( partition by department_id ) emp#
  from   hr.employees e
), department_country_details as (
  select * from hr.departments d
  join   hr.locations l
    using  ( location_id )
  join   hr.countries c
    using  ( country_id )
  join   hr.regions r
    using  ( region_id )
)
select *
from   employees_with_dept_counts e
join   department_country_details d
using  ( department_id )
where  e.emp# > 3;

Statement 11

create or replace view department_country_details as  
  select * from departments d 
  join   locations l 
  using  ( location_id ) 
  join   countries c 
  using  ( country_id ) 
  join   regions r 
  using  ( region_id )

View created.

Statement 12

select * from department_country_details


REGION_ID COUNTRY_ID LOCATION_ID DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID STREET_ADDRESS POSTAL_CODE CITY STATE_PROVINCE COUNTRY_NAME REGION_NAME
2 US 1700 10 Administration 200 2004 Charade Rd 98199 Seattle Washington United States of America Americas
2 CA 1800 20 Marketing 201 147 Spadina Ave M5V 2L7 Toronto Ontario Canada Americas
2 US 1700 30 Purchasing 114 2004 Charade Rd 98199 Seattle Washington United States of America Americas
1 UK 2400 40 Human Resources 203 8204 Arthur St  - London  - United Kingdom Europe
2 US 1500 50 Shipping 121 2011 Interiors Blvd 99236 South San Francisco California United States of America Americas

Rows 1 - 5. More rows exist.

REGION_ID	COUNTRY_ID	LOCATION_ID	DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	STREET_ADDRESS	POSTAL_CODE	CITY	STATE_PROVINCE	COUNTRY_NAME	REGION_NAME
2	US	1700	10	Administration	200	2004 Charade Rd	98199	Seattle	Washington	United States of America	Americas
2	CA	1800	20	Marketing	201	147 Spadina Ave	M5V 2L7	Toronto	Ontario	Canada	Americas
2	US	1700	30	Purchasing	114	2004 Charade Rd	98199	Seattle	Washington	United States of America	Americas
1	UK	2400	40	Human Resources	203	8204 Arthur St	-	London	-	United Kingdom	Europe
2	US	1500	50	Shipping	121	2011 Interiors Blvd	99236	South San Francisco	California	United States of America	Americas

Statement 13

with employees_with_dept_counts as (
  select e.*,
         count(*) over ( partition by department_id ) emp#
  from   hr.employees e
)
  select region_name, salary
  from   employees_with_dept_counts e
  join   hr.department_country_details d
  using  ( department_id )
  where  e.emp# > 3;

Statement 14

with employees_with_dept_counts as (
  select e.*,
         count(*) over ( partition by department_id ) emp#
  from   hr.employees e
), filtered_employee_regions as (
  select region_name, salary
  from   employees_with_dept_counts e
  join   hr.department_country_details d
  using  ( department_id )
  where  e.emp# > 3
)
  select region_name, 
         sum ( salary ) total_salary, 
         round ( avg ( salary ) ) mean_salary
  from   filtered_employee_regions
  group  by region_name;

Statement 15

create or replace view region_summaries as  
with employees_with_dept_counts as (
  select e.*,
         count(*) over ( partition by department_id ) emp#
  from   hr.employees e
), filtered_employee_regions as (
  select region_name, salary
  from   employees_with_dept_counts e
  join   hr.department_country_details d
  using  ( department_id )
  where  e.emp# > 3
)
  select region_name, 
         sum ( salary ) total_salary, 
         round ( avg ( salary ) ) mean_salary
  from   filtered_employee_regions
  group  by region_name;

View created.

Statement 16

select * from region_summaries


REGION_NAME TOTAL_SALARY MEAN_SALARY
Americas 261708 4221
Europe 304500 8956

2 rows selected.

REGION_NAME	TOTAL_SALARY	MEAN_SALARY
Americas	261708	4221
Europe	304500	8956

Statement 17

create or replace view region_summaries ( 
  min_emps integer 
) as  
with employees_with_dept_counts as (
  select e.*,
         count(*) over ( partition by department_id ) emp#
  from   hr.employees e
), filtered_employee_regions as (
  select region_name, salary
  from   employees_with_dept_counts e
  join   hr.department_country_details d
  using  ( department_id )
  where  e.emp# > min_emps
)
  select region_name, 
         sum ( salary ) total_salary, 
         round ( avg ( salary ) ) mean_salary
  from   filtered_employee_regions
  group  by region_name;

ORA-00907: missing right parenthesis

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

Statement 18

create or replace function employees_filtered_by_dept_size ( 
  min_emps integer 
) return clob sql_macro as 
  stmt clob; 
begin 
  stmt := ' 
with employees_with_dept_counts as ( 
  select e.*, 
         count(*) over ( partition by department_id ) emp# 
  from   employees e 
) 
select  *  
from    employees_with_dept_counts 
where   emp# >= min_emps'; 
   
  return stmt; 
end employees_filtered_by_dept_size;

Function created.

Statement 19

select department_id, emp#  
from   employees_filtered_by_dept_size ( 3 ) 
order  by emp#


DEPARTMENT_ID EMP#
90 3
90 3
90 3
60 5
60 5

Rows 1 - 5. More rows exist.

DEPARTMENT_ID	EMP#
90	3
90	3
90	3
60	5
60	5

Statement 20

select department_id, emp#  
from   employees_filtered_by_dept_size ( 10 ) 
order  by emp#


DEPARTMENT_ID EMP#
80 34
80 34
80 34
80 34
80 34

Rows 1 - 5. More rows exist.

DEPARTMENT_ID	EMP#
80	34
80	34
80	34
80	34
80	34

Statement 21

select r.region_name,  
       sum ( salary ) total_salary,  
       round ( avg ( salary ) ) mean_salary 
from   employees_filtered_by_dept_size ( :min_emps ) e 
join   department_country_details r 
using  ( department_id ) 
group  by r.region_name

ORA-01008: not all variables bound

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

Statement 22

with tab_with_col_counts as ( 
  select t.*, 
         count(*) over ( partition by ##COLUMN## ) grp# 
  from   ##TABLE## t 
) 
select  *  
from    tab_with_col_counts 
where   grp# >= :min_emps

ORA-00911: invalid character

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

Statement 23

create or replace function filter_by_column_count ( 
  source_table dbms_tf.table_t, 
  group_column dbms_tf.columns_t, 
  min_count    integer 
) return clob sql_macro as 
  stmt clob; 
begin 
  stmt := ' 
with tab_with_group_counts as ( 
  select t.*, 
         count(*) over ( partition by ' || group_column (1) || ' ) grp# 
  from   source_table t 
) 
select  *  
from    tab_with_group_counts 
where   grp# >= min_count'; 
   
  return stmt; 
end filter_by_column_count;

Function created.

Statement 24

select department_id, grp#  
from   filter_by_column_count (  
  source_table => employees,  
  group_column => columns ( department_id ),  
  min_count    => 3  
)


DEPARTMENT_ID GRP#
30 6
30 6
30 6
30 6
30 6

Rows 1 - 5. More rows exist.

DEPARTMENT_ID	GRP#
30	6
30	6
30	6
30	6
30	6

Statement 25

select job_id, grp#  
from   filter_by_column_count (  
  source_table => employees,  
  group_column => columns ( job_id ),  
  min_count    => 3  
)


JOB_ID GRP#
FI_ACCOUNT 5
FI_ACCOUNT 5
FI_ACCOUNT 5
FI_ACCOUNT 5
FI_ACCOUNT 5

Rows 1 - 5. More rows exist.

JOB_ID	GRP#
FI_ACCOUNT	5
FI_ACCOUNT	5
FI_ACCOUNT	5
FI_ACCOUNT	5
FI_ACCOUNT	5

Statement 26

select country_id, grp#  
from   filter_by_column_count (  
  source_table => locations,  
  group_column => columns ( country_id ),  
  min_count    => :country_count 
)

ORA-01008: not all variables bound

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

Statement 27

select r.region_name,  
       sum ( salary ) total_salary,  
       round ( avg ( salary ) ) mean_salary 
from   filter_by_column_count (  
  employees, columns ( department_id ), :min_emps 
)  
join   department_country_details r 
using  ( department_id ) 
group  by r.region_name

ORA-01008: not all variables bound

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

Statement 28

create or replace function region_summaries_macro ( min_emps integer )  
  return clob sql_macro as 
  stmt clob; 
begin 
  stmt := ' 
select r.region_name,  
       sum ( salary ) total_salary,  
       round ( avg ( salary ) ) mean_salary 
from   filter_by_column_count (  
  employees, columns ( department_id ), min_emps 
)  
join   department_country_details r 
using  ( department_id ) 
group  by r.region_name'; 
 
  return stmt; 
   
end region_summaries_macro;

Function created.

Statement 29

select * from region_summaries_macro ( 1 )


REGION_NAME TOTAL_SALARY MEAN_SALARY
Americas 363416 5192
Europe 321000 8917

2 rows selected.

REGION_NAME	TOTAL_SALARY	MEAN_SALARY
Americas	363416	5192
Europe	321000	8917

Statement 30

select * from region_summaries_macro ( 4 )


REGION_NAME TOTAL_SALARY MEAN_SALARY
Americas 261708 4221
Europe 304500 8956

2 rows selected.

REGION_NAME	TOTAL_SALARY	MEAN_SALARY
Americas	261708	4221
Europe	304500	8956

Statement 31

select * from region_summaries_macro ( 40 )


REGION_NAME TOTAL_SALARY MEAN_SALARY
Americas 156400 3476

REGION_NAME	TOTAL_SALARY	MEAN_SALARY
Americas	156400	3476

Statement 32

select * from region_summaries


REGION_NAME TOTAL_SALARY MEAN_SALARY
Americas 261708 4221
Europe 304500 8956

2 rows selected.

REGION_NAME	TOTAL_SALARY	MEAN_SALARY
Americas	261708	4221
Europe	304500	8956

Statement 33

select * from ( 
  select s.* from region_summaries_macro ( 3 ) s 
  minus 
  select s.* from region_summaries s 
) union all ( 
  select s.* from region_summaries s 
  minus  
  select s.* from region_summaries_macro ( 3 ) s  
)


REGION_NAME TOTAL_SALARY MEAN_SALARY
Americas 319708 4919
Americas 261708 4221

2 rows selected.

REGION_NAME	TOTAL_SALARY	MEAN_SALARY
Americas	319708	4919
Americas	261708	4221

Statement 34

declare 
  l_clob clob; 
begin 
  dbms_utility.expand_sql_text ( 
    input_sql_text  =>  
      q'!select * from region_summaries_macro ( :min_emps )!', 
    output_sql_text => l_clob  ); 
  dbms_output.put_line(l_clob); 
end;

Statement processed.
SELECT "A1"."REGION_NAME" "REGION_NAME","A1"."TOTAL_SALARY" "TOTAL_SALARY","A1"."MEAN_SALARY" "MEAN_SALARY" FROM  (SELECT "A4"."REGION_NAME" "REGION_NAME","A4"."TOTAL_SALARY" "TOTAL_SALARY","A4"."MEAN_SALARY" "MEAN_SALARY" FROM  (SELECT "A5"."REGION_NAME_2" "REGION_NAME",SUM("A5"."SALARY_1") "TOTAL_SALARY",ROUND(AVG("A5"."SALARY_1")) "MEAN_SALARY" FROM  (SELECT "A6"."DEPARTMENT_ID" "DEPARTMENT_ID","A7"."SALARY" "SALARY_1","A6"."REGION_NAME" "REGION_NAME_2" FROM  (SELECT "A8"."EMPLOYEE_ID" "EMPLOYEE_ID","A8"."FIRST_NAME" "FIRST_NAME","A8"."LAST_NAME" "LAST_NAME","A8"."EMAIL" "EMAIL","A8"."PHONE_NUMBER" "PHONE_NUMBER","A8"."HIRE_DATE" "HIRE_DATE","A8"."JOB_ID" "JOB_ID","A8"."SALARY" "SALARY","A8"."COMMISSION_PCT" "COMMISSION_PCT","A8"."MANAGER_ID" "MANAGER_ID","A8"."DEPARTMENT_ID" "DEPARTMENT_ID","A8"."GRP#" "GRP#" FROM  (SELECT "A9"."EMPLOYEE_ID" "EMPLOYEE_ID","A9"."FIRST_NAME" "FIRST_NAME","A9"."LAST_NAME" "LAST_NAME","A9"."EMAIL" "EMAIL","A9"."PHONE_NUMBER" "PHONE_NUMBER","A9"."HIRE_DATE" "HIRE_DATE","A9"."JOB_ID" "JOB_ID","A9"."SALARY" "SALARY","A9"."COMMISSION_PCT" "COMMISSION_PCT","A9"."MANAGER_ID" "MANAGER_ID","A9"."DEPARTMENT_ID" "DEPARTMENT_ID","A9"."GRP#" "GRP#" FROM  (SELECT "A2"."EMPLOYEE_ID" "EMPLOYEE_ID","A2"."FIRST_NAME" "FIRST_NAME","A2"."LAST_NAME" "LAST_NAME","A2"."EMAIL" "EMAIL","A2"."PHONE_NUMBER" "PHONE_NUMBER","A2"."HIRE_DATE" "HIRE_DATE","A2"."JOB_ID" "JOB_ID","A2"."SALARY" "SALARY","A2"."COMMISSION_PCT" "COMMISSION_PCT","A2"."MANAGER_ID" "MANAGER_ID","A2"."DEPARTMENT_ID" "DEPARTMENT_ID",COUNT(*) OVER ( PARTITION BY "A2"."DEPARTMENT_ID") "GRP#" FROM  (SELECT "A3"."EMPLOYEE_ID" "EMPLOYEE_ID","A3"."FIRST_NAME" "FIRST_NAME","A3"."LAST_NAME" "LAST_NAME","A3"."EMAIL" "EMAIL","A3"."PHONE_NUMBER" "PHONE_NUMBER","A3"."HIRE_DATE" "HIRE_DATE","A3"."JOB_ID" "JOB_ID","A3"."SALARY" "SALARY","A3"."COMMISSION_PCT" "COMMISSION_PCT","A3"."MANAGER_ID" "MANAGER_ID","A3"."DEPARTMENT_ID" "DEPARTMENT_ID" FROM "SQL_HNOGOSZNBZRXFTHQZQYGMUCVZ"."EMPLOYEES" "A3") "A2") "A9" WHERE "A9"."GRP#">=:B1) "A8") "A7", (SELECT "A10"."REGION_ID_0" "REGION_ID","A10"."COUNTRY_ID_1" "COUNTRY_ID","A10"."LOCATION_ID_2" "LOCATION_ID","A10"."DEPARTMENT_ID_3" "DEPARTMENT_ID","A10"."DEPARTMENT_NAME_4" "DEPARTMENT_NAME","A10"."MANAGER_ID_5" "MANAGER_ID","A10"."STREET_ADDRESS_6" "STREET_ADDRESS","A10"."POSTAL_CODE_7" "POSTAL_CODE","A10"."CITY_8" "CITY","A10"."STATE_PROVINCE_9" "STATE_PROVINCE","A10"."COUNTRY_NAME_10" "COUNTRY_NAME","A10"."REGION_NAME_11" "REGION_NAME" FROM  (SELECT "A11"."REGION_ID" "REGION_ID_0","A12"."COUNTRY_ID_0" "COUNTRY_ID_1","A12"."LOCATION_ID_1" "LOCATION_ID_2","A12"."DEPARTMENT_ID_2" "DEPARTMENT_ID_3","A12"."DEPARTMENT_NAME_3" "DEPARTMENT_NAME_4","A12"."MANAGER_ID_4" "MANAGER_ID_5","A12"."STREET_ADDRESS_5" "STREET_ADDRESS_6","A12"."POSTAL_CODE_6" "POSTAL_CODE_7","A12"."CITY_7" "CITY_8","A12"."STATE_PROVINCE_8" "STATE_PROVINCE_9","A12"."COUNTRY_NAME_9" "COUNTRY_NAME_10","A11"."REGION_NAME" "REGION_NAME_11" FROM  (SELECT "A13"."COUNTRY_ID" "COUNTRY_ID_0","A14"."LOCATION_ID_0" "LOCATION_ID_1","A14"."DEPARTMENT_ID_1" "DEPARTMENT_ID_2","A14"."DEPARTMENT_NAME_2" "DEPARTMENT_NAME_3","A14"."MANAGER_ID_3" "MANAGER_ID_4","A14"."STREET_ADDRESS_4" "STREET_ADDRESS_5","A14"."POSTAL_CODE_5" "POSTAL_CODE_6","A14"."CITY_6" "CITY_7","A14"."STATE_PROVINCE_7" "STATE_PROVINCE_8","A13"."COUNTRY_NAME" "COUNTRY_NAME_9","A13"."REGION_ID" "REGION_ID_10" FROM  (SELECT "A15"."LOCATION_ID" "LOCATION_ID_0","A16"."DEPARTMENT_ID" "DEPARTMENT_ID_1","A16"."DEPARTMENT_NAME" "DEPARTMENT_NAME_2","A16"."MANAGER_ID" "MANAGER_ID_3","A15"."STREET_ADDRESS" "STREET_ADDRESS_4","A15"."POSTAL_CODE" "POSTAL_CODE_5","A15"."CITY" "CITY_6","A15"."STATE_PROVINCE" "STATE_PROVINCE_7","A15"."COUNTRY_ID" "COUNTRY_ID_8" FROM "SQL_HNOGOSZNBZRXFTHQZQYGMUCVZ"."DEPARTMENTS" "A16","SQL_HNOGOSZNBZRXFTHQZQYGMUCVZ"."LOCATIONS" "A15" WHERE "A16"."LOCATION_ID"="A15"."LOCATION_ID") "A14","SQL_HNOGOSZNBZRXFTHQZQYGMUCVZ"."COUNTRIES" "A13" WHERE "A14"."COUNTRY_ID_8"="A13"."COUNTRY_ID") "A12","SQL_HNOGOSZNBZRXFTHQZQYGMUCVZ"."REGIONS" "A11" WHERE "A12"."REGION_ID_10"="A11"."REGION_ID") "A10") "A6" WHERE "A7"."DEPARTMENT_ID"="A6"."DEPARTMENT_ID") "A5" GROUP BY "A5"."REGION_NAME_2") "A4") "A1"

Statement 35

select * from ( 
  select e.*, 
         count(*) over ( partition by department_id ) emp# 
  from   employees e 
) 
where  emp# > 3


EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID EMP#
114 Den Raphaely DRAPHEAL 515.127.4561 07-DEC-02 PU_MAN 11000  - 100 30 6
115 Alexander Khoo AKHOO 515.127.4562 18-MAY-03 PU_CLERK 3100  - 114 30 6
116 Shelli Baida SBAIDA 515.127.4563 24-DEC-05 PU_CLERK 2900  - 114 30 6
117 Sigal Tobias STOBIAS 515.127.4564 24-JUL-05 PU_CLERK 2800  - 114 30 6
118 Guy Himuro GHIMURO 515.127.4565 15-NOV-06 PU_CLERK 2600  - 114 30 6

Rows 1 - 5. More rows exist.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID	EMP#
114	Den	Raphaely	DRAPHEAL	515.127.4561	07-DEC-02	PU_MAN	11000	-	100	30	6
115	Alexander	Khoo	AKHOO	515.127.4562	18-MAY-03	PU_CLERK	3100	-	114	30	6
116	Shelli	Baida	SBAIDA	515.127.4563	24-DEC-05	PU_CLERK	2900	-	114	30	6
117	Sigal	Tobias	STOBIAS	515.127.4564	24-JUL-05	PU_CLERK	2800	-	114	30	6
118	Guy	Himuro	GHIMURO	515.127.4565	15-NOV-06	PU_CLERK	2600	-	114	30	6

Statement 36

create or replace function filter_by_column_count ( 
  source_table dbms_tf.table_t, 
  group_column dbms_tf.columns_t, 
  min_count    integer 
) return clob sql_macro as 
  stmt clob; 
begin 
  stmt := ' 
with tab_with_group_counts as ( 
  select t.*, 
         count(*) over ( partition by ' || group_column (1) || ' ) grp# 
  from   source_table t 
) 
select  *  
from    tab_with_group_counts 
where   grp# >= min_count'; /* Ooops! Was strictly gt */ 
   
  return stmt; 
end filter_by_column_count;

Function created.

Statement 37

create or replace function filter_by_column_count ( 
  source_table dbms_tf.table_t, 
  group_column dbms_tf.columns_t, 
  min_count    integer 
) return clob sql_macro as 
  stmt clob; 
begin 
  stmt := ' 
with tab_with_group_counts as ( 
  select t.*, 
         count(*) over ( partition by ' || group_column (1) || ' ) grp# 
  from   source_table t 
) 
select  *  
from    tab_with_group_counts 
where   grp# > min_count'; /* Fixed! */ 
   
  return stmt; 
end filter_by_column_count;

Function created.

Statement 38

select * from ( 
  select s.* from region_summaries_macro ( 3 ) s 
  minus  
  select s.* from region_summaries s 
) union all (  
  select s.* from region_summaries s 
  minus  
  select s.* from region_summaries_macro ( 3 ) s 
)

no data found

How to Write Powerful SQL That’s Easy to Maintain

Additional Information