Home » SQL & PL/SQL » SQL & PL/SQL » i was unable to create a materialized view on this view (merged)
i was unable to create a materialized view on this view (merged) [message #299486] Tue, 12 February 2008 03:47 Go to next message
vulugundam
Messages: 2
Registered: February 2008
Junior Member
CREATE MATERIALIZED VIEW mv_simple
TABLESPACE uwdata
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS

select cs.CASE_NO,
cs.CLAIM_NO,
cssr.case_id,
pt.PATIENT_ID,
pt.AGE,
(select CMB_DTL_NAME
from ASRIM_COMBO
where ASRIM_COMBO.CMB_DTL_ID = pt.GENDER) GENDER_NAME,
pt.GENDER GENDRE_CD,
pt.CASTE CASTE__CD,
(select CMB_DTL_NAME
from ASRIM_COMBO
where ASRIM_COMBO.CMB_DTL_ID = pt.CASTE) CASTE_NAME,

pt.DISTRICT_CODE,
nvl((select LOC_NAME
from ASRIM_LOCATIONS
where ASRIM_LOCATIONS.LOC_ID = pt.DISTRICT_CODE),
'') DISTRICT_NAME,
nvl((select LOC_VAL
from ASRIM_LOCATIONS
where ASRIM_LOCATIONS.LOC_ID = pt.DISTRICT_CODE),
'') PHASE,
(select csp.CASE_DT_SURGERY from asrit_case_proces csp where
csp.case_id=cs.case_id) CASE_DT_SURGERY,
(select csp.CASE_DISCHARGE_DT from asrit_case_proces csp where
csp.case_id=cs.case_id) CASE_DISCHARGE_DT,
nvl((select DIS_MAIN_ID
from ASRIM_DISEASE_MAIN
where ASRIM_DISEASE_MAIN.DIS_MAIN_ID = cssr.DIS_MAIN_CODE),
'') CATEGORY_CODE,
nvl((select DIS_MAIN_NAME
from ASRIM_DISEASE_MAIN
where ASRIM_DISEASE_MAIN.DIS_MAIN_ID = cssr.DIS_MAIN_CODE),
'') CATEGORY_NAME,
(select TO_DATE(csp.case_dt_pre_auth,'DD/MM/YY')from asrit_case_proces
csp where csp.case_id=cs.case_id) CASE_SEND_AUTH_DT,
( select csinvst.case_tot_pckg_amt from asrit_case_invest_dtls
csinvst where csinvst.case_id=cs.case_id )case_tot_pckg_amt,
cs.case_status STATUS_ID,
cs.lst_upd_dt STATUS_DATE,
nvl((select CMB_DTL_NAME from ASRIM_COMBO
WHERE ASRIM_COMBO.CMB_DTL_ID = cs.case_status), '') STATUS_NAME,
nvl((select death_dt from asrit_case_proces where
asrit_case_proces.case_id=cs.case_id),'') DEATH_DT
from ASRIT_PATIENT pt, ASRIT_CASE cs,
ASRIT_CASE_SURGERY cssr
where cs.CASE_PATIENT_NO = pt.PATIENT_ID and cs.CASE_ID = cssr.CASE_ID
and cs.case_status not in ('CD73','CD75')






it is giving subquery expression not allowed here can anybody help me very urgent
is subqueries are allowed in materialized view [message #299487 is a reply to message #299486] Tue, 12 February 2008 03:49 Go to previous messageGo to next message
vulugundam
Messages: 2
Registered: February 2008
Junior Member
can any body help me
i am trying to create a materialized view but i was unable to do it
i am getting ora -22818
subquery expression not allowed here
wat to do
can anybody help me
Re: is subqueries are allowed in materialized view [message #299493 is a reply to message #299487] Tue, 12 February 2008 03:59 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

can anybody help me



Not really. The error message should have told you in which line/position the error is, but you didn't tell us, and you didn't format the query into anything remotely readable.
Re: i was unable to create a materialized view on this view (merged) [message #299497 is a reply to message #299486] Tue, 12 February 2008 04:14 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
If your query is that complex a little bit of formatting would make if far more easier to read (take this as a hint Wink )

Did you try the SELECT itself? Did that work on its own or not?

Found this link

Maybe it could be useful in your case.
Re: is subqueries are allowed in materialized view [message #299570 is a reply to message #299487] Tue, 12 February 2008 07:57 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
vulugundam wrote on Tue, 12 February 2008 04:49
can any body help me



So, you gave people a total of two minutes before you start nagging us? If it's so urgent, pay for support from Oracle and ask them.
Re: is subqueries are allowed in materialized view [message #299571 is a reply to message #299570] Tue, 12 February 2008 07:59 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
joy_division wrote on Tue, 12 February 2008 14:57
vulugundam wrote on Tue, 12 February 2008 04:49
can any body help me



So, you gave people a total of two minutes before you start nagging us? If it's so urgent, pay for support from Oracle and ask them.


Or probably didn't know he could use the EDIT-button to change his own post? (or am I a bit too naive again Wink )
Re: is subqueries are allowed in materialized view [message #299574 is a reply to message #299571] Tue, 12 February 2008 08:02 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
I also now noticed that this was a merged topic, so it probably wasn't an impatient two minutes, but more of a cross post or like you said, a second post in the same forum.
Previous Topic: Varchar2 as Primary Keys
Next Topic: Create partitioned table as select
Goto Forum:
  


Current Time: Thu Dec 08 16:25:02 CST 2016

Total time taken to generate the page: 0.17829 seconds