471,090 Members | 1,357 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,090 software developers and data experts.

materialized view. help...

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
Jul 19 '05 #1
2 4850
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

Jul 19 '05 #2
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
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Matthieu Exbrayat | last post: by
3 posts views Thread by M. Mehta | last post: by
1 post views Thread by Hans-Jürgen Schönig | last post: by
7 posts views Thread by Esteban Kemp | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.