-
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.
-
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|