error in creating materialized view

Hi all,
I want to create a materialized view,which uses
Having count(*)>1 it shows error in this line as

HAVING COUNT(*) > 1
*
ERROR at line 18:
ORA-30353: expression not supported for query rewrite


thanks in advance.

check this may help you.
http://forums.oracle.com/forums/thre...ssageID=855595

thanks for ur response,

but that link doesn't helps me..,

Try to change query_rewrite_enabled to FALSE and then check. Are u using Oracle 10g version? If so this note says it's a bug Note:359826.1

iam using Oracle9.2
i tried with setting query_rewrite_enables=false
now it shows error as

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Can you post your entire sql. Are the tables being used IOT or normal table.

As far as I know you can't use a ON COMMIT refersh for a MV containing a GROUP BY

here is the code:

Code:

---

create materialized view snack1
  build immediate
  refresh on commit
  enable query rewrite
as
SELECT COUNT(*), MENU_MEAL.person_nr , MENU_MEAL.consume_Date,MENU_MEAL.meal_type_code
        FROM MENU_MEAL,PERSON p
                WHERE p.person_nr=MENU_MEAL.person_nr
                        AND MENU_MEAL.is_prescribed='Y'
                        AND MENU_MEAL.is_deleted='N'
                        AND is_modified='N'
                        AND p.status_code IN('ACTV','CHNG')
                        AND MENU_MEAL.meal_type_code = 'S1'
                        AND TRUNC(MENU_MEAL.consume_date) >= TRUNC(SYSDATE)
                        AND MENU_MEAL. person_nr > 0
                        AND p.email not like '%test%'
        GROUP BY MENU_MEAL.person_nr,consume_date,meal_type_code
        HAVING COUNT(*) > 1;

---

we are using normal tables

ok, i removed refresh on commit now
it works ..
without query_rewrite_enable true how it will consider optimal query.

Thanks

i think you can now set TRUE for query_rewrite_enable and this should work.

No, i removed from the query itself (in MV) used only build immediate.
so no need to set.