Re: group by grouping set error in oracle 10g

On Jul 31, 1: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

Search for ora-030001 on metalink. You may find things like bug 5847881, no workaround, fixed in 11.1.0.5. You should probably open an SR and kick Oracle's butt if they can't fix it right away.

jg

--
@home.com is bogus.  "Screening ideas are indeed thought up by the
Office for Annoying Air Travelers and vetted through the Directorate
for Confusion and Complexity, and then we review them to insure that
there are sufficient unintended irritating consequences so that the
blogosphere is constantly fueled." - Kip Hawley