Re: group by grouping set error in oracle 10g
- From: "fitzjarrell@xxxxxxx" <fitzjarrell@xxxxxxx>
- Date: Tue, 31 Jul 2007 13:38:41 -0700
On Jul 31, 3:22 pm, nickli <ningli2...@xxxxxxxxxxx> wrote:
Hi,
I had the error message"ORA-03001: unimplemented feature" when
running the following query in Oracle 10g:
SELECT /*+ USE_HASH(m,t) */
m.household_id
,t.channel_code
,m.trading_area
,SUM(t.quantity)
,case when grouping_id(-1)=0 then -1
when grouping_id(1)=0 then 1
when grouping_id(2)=0 then 2
when grouping_id(3)=0 then 3
else 99 end group_set_id
.......
FROM ( SELECT /*+ index(l) */
household_id
,min(segment_key) segment_key
,min(trading_area) trading_area
FROM promo_mailed_list partition(p3134) l
WHERE 1=1
GROUP BY household_id ) m
,transaction t
WHERE t.household_id = m.household_id
AND t.transaction_type = 1
AND t.sales_credit = 1
AND t.transaction_date BETWEEN '15-JUN-2007' AND '17-AUG-2007'
GROUP BY GROUPING SETS
((m.household_id,m.segment_key,t.channel_code,m.trading_area,t.transaction_date,-1)
,(m.household_id,m.segment_key,m.trading_area,
1)
,(m.household_id,m.segment_key,t.channel_code,m.trading_area,2)
,
(m.household_id,m.segment_key,t.channel_code,m.trading_area,3))
It seems that Oracle 10g is complaining about the GROUP BY GROUPING
SETS statement.
The same statement runs well in 9i. Could anyone tell me a workaround
for this? I tried to set the following and it is not working:
alter session set "_gby_hash_aggregation_enabled" = FALSE;
alter session set optimizer_features_enable='9.2.0';
Thanks in advance.
Nick Li
What is the full version number of your '10g' database? Which options
were installed? What release and options for the '9i' database? I
suspect there are differences between the two installations you
haven't discovered or reported.
David Fitzjarrell
.
- References:
- group by grouping set error in oracle 10g
- From: nickli
- group by grouping set error in oracle 10g
- Prev by Date: group by grouping set error in oracle 10g
- Next by Date: Re: group by grouping set error in oracle 10g
- Previous by thread: group by grouping set error in oracle 10g
- Next by thread: Re: group by grouping set error in oracle 10g
- Index(es):
Relevant Pages
|
