Re: group by grouping set error in oracle 10g

On Jul 31, 3:22 pm, nickli <ningli2..._at_hotmail.com> 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 Received on Tue Jul 31 2007 - 15:38:41 CDT