group by grouping set error in oracle 10g

From: nickli <ningli2000_at_hotmail.com>
Date: Tue, 31 Jul 2007 13:22:08 -0700
Message-ID: <1185913328.365146.138910@o61g2000hsh.googlegroups.com>

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 Received on Tue Jul 31 2007 - 15:22:08 CDT