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