SQL & PL/SQL

I have a lengthy query running on Oracle10g and some detail is omitted

with base01 as (
...
), base02 as (
...
), base03 as (
...
), base04 as (
select b2.emp_id, b2.emp_nm, b1.is_face_customer, b1.act_typ_id, b1.act_typ_nm, act_duration_hour, cust_nbr,
nvl(sum(act_duration_hour) over (),0) as act_duration_subttl,
nvl(count(distinct cust_nbr) over (),0) as act_count_subttl
from base01 b1
left join base02 b2 on (1=1)
left join base03 b3 on (b2.emp_id= b3.emp_id and b1.act_typ_id= b3.act_typ_id)
)
select grouping(emp_id) as grp_emp_id, emp_id, emp_nm,
grouping(is_face_customer) as grp_is_face_customer, is_face_customer,
grouping(act_typ_id) as grp_act_typ_id, act_typ_id, act_typ_nm,
nvl(sum(act_duration_hour),0) as act_duration,
act_duration_subttl,
nvl(count(distinct cust_nbr),0) as act_count,
act_count_subttl
from base04
group by act_duration_subttl, act_count_subttl, emp_id, emp_nm,
rollup (is_face_customer, (act_typ_id, act_typ_nm)), rollup((emp_id, emp_nm))
-- I tested on several combination of grouping sets and cube instead of two rollup, but the results is the same


This statement results in error ORA-00907 in a line of with clause base01. That error message is misleading because I have test above SQL by replacing select clause as "select * from base04". Do anyone encounter similar weird error message?

To workaround, I have to replace the select clause by something which look silly:

select 0 as grp_emp_id, emp_id, emp_nm,
grouping(is_face_customer) as grp_is_face_customer, is_face_customer,
grouping(act_typ_id) as grp_act_typ_id, act_typ_id, act_typ_nm,
nvl(sum(act_duration_hour),0) as act_duration,
act_duration_subttl,
nvl(count(distinct cust_nbr),0) as act_count,
act_count_subttl
from base04
group by act_duration_subttl, act_count_subttl, emp_id, emp_nm,
rollup (is_face_customer, (act_typ_id, act_typ_nm))
union
select 1 as grp_emp_id, null as emp_id, null as emp_nm,
grouping(is_face_customer) as grp_is_face_customer, is_face_customer,
grouping(act_typ_id) as grp_act_typ_id, act_typ_id, act_typ_nm,
nvl(sum(act_duration_hour),0) as act_duration,
act_duration_subttl,
nvl(count(distinct cust_nbr),0) as act_count,
act_count_subttl
from base04
group by act_duration_subttl, act_count_subttl,
rollup (is_face_customer, (act_typ_id, act_typ_nm))


However, this kind of union is usually tedious and misleading to causal developer. It look silly to mimic everything combination of grouping sets as union of rollups. Is there any better way?

Message was edited by:
csmth96