materialized view. help...

[anna]

Hi all,

I've been digging this problem almost 3 days now.

I created some 'normal' views and then join them all together to
create a materialized view but I got this error message:

"Subquery expressions not allowed here"

I use subqueries *only* in 'normal' views.

any comments, advice really appreciated.

many thanks
anthony

[anna]

thanks Daniel

oracle9i - 9.2.0.3.0

materialize view - I just use default option - I'm a newbie in this
area:

create materialized view test_mv as
select * from security1_view

==============
one of my view is like this - it's about a loan - one policy can have
many securities, and one security can be valuated many times:

CREATE OR REPLACE FORCE VIEW WHSEOWNER.SECURITY1_VIEW
(SEC1_POL_NO, PP_PC_EV1, NO_VAL_DECISION1, MORTGAGE_RANK1, SEC_TYPE1,
SEC_TYPE_STATUS1, OCC_TYPE1, ZONING1, TITLE_TYPE1, SECURITY_ACC1,
THIRD_PARTY1, VALUATION_PROGRAM1, VAL_AMT1, VAL_DATE1, VALUER_NO1,
LAND_AREA1, NO_BEDROOMS1, SECURITY_ADD1, SECURITY_STATE1,
SECURITY_POSTCODE1,
SEC_CREATED_DT)
AS
select
dwh_policies.pol_policy_no as sec1_pol_no,
dwh_securities.sec_pp_pc_ev as pp_pc_ev1,
dwh_securities.sec_no_val_decision as no_val_decision1,
dwh_securities.sec_mortgage_rank as mortgage_rank1,
dwh_securities.sec_type as sec_type1,
dwh_securities.sec_type_status as sec_type_status1,
dwh_securities.sec_occupancy_type as occ_type1,
dwh_securities.sec_zoning as zoning1,
dwh_securities.sec_title_type as title_type1,
dwh_securities.sec_acceptable_ind as security_acc1,
dwh_securities.sec_third_party_ind as third_party1,

(select val_program from dwh_valuations where val_id = (select
max(val1.val_id) from dwh_valuations val1 where
val1.val_sec_id = dwh_securities.sec_id)) as valuation_program1,

(select val_amount from dwh_valuations where val_id = (select
max(val1.val_id) from dwh_valuations val1 where
val1.val_sec_id = dwh_securities.sec_id)) as val_amt1,

(select val_date from dwh_valuations where val_id = (select
max(val1.val_id) from dwh_valuations val1 where
val1.val_sec_id = dwh_securities.sec_id)) as val_date1,

(select val_valuer_no from dwh_valuations where val_id = (select
max(val1.val_id) from dwh_valuations val1 where
val1.val_sec_id = dwh_securities.sec_id)) as valuer_no1,

(select val_land_area from dwh_valuations where val_id = (select
max(val1.val_id) from dwh_valuations val1 where
val1.val_sec_id = dwh_securities.sec_id)) as land_area1,

(select val_no_bedrooms from dwh_valuations where val_id = (select
max(val1.val_id) from dwh_valuations val1 where
val1.val_sec_id = dwh_securities.sec_id)) as no_bedrooms1,

dwh_addresses.add_address as security_add1,
dwh_addresses.add_state as security_state1,
dwh_addresses.add_postcode as security_postcode1,
sec_created_dt
from dwh_policies
left outer join dwh_applications on dwh_policies.pol_id =
dwh_applications.app_pol_id
left outer join dwh_securities on dwh_securities.sec_app_id =
dwh_applications.app_id
left outer join dwh_addresses on dwh_addresses.add_id =
dwh_securities.sec_add_id
where dwh_securities.sec_sequence= 1;


da*************@hotmail.com (Daniel Roy) wrote in message news:<37************************@posting.google.co m>...

No one will be able to help you unless we know your version, and the
definition behind these views and this matialized view.

Daniel

[Daniel Roy]

According to what I see in the Data Warehouse manual
(http://download-west.oracle.com/docs...20/mv.htm#721),
inline views (i.e. what you try) is only possible in the FROM clause
of the MV definition, and not in the SELECT clause, as you are trying.

Daniel