Re: group by grouping set error in oracle 10g

nickli 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

SELECT m.household_id, t.channel_code, m.trading_area

GROUP BY GROUPING SETS ((m.household_id, m.segment_key, t.channel_code

Where's segment_key in your SELECT?

Also:

AND t.transaction_date BETWEEN '15-JUN-2007' AND '17-AUG-2007'

Dates are dates not strings.

WHERE 1=1 Usually but what's the point?

index(l)

any particular index name in mind?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org