Re: materialized view. help...

From: anna <anthony_at_allette.com.au>
Date: 24 Aug 2003 17:38:08 -0700
Message-ID: <1c367d75.0308241638.6900c7f0_at_posting.google.com>

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;

danielroy10junk_at_hotmail.com (Daniel Roy) wrote in message news:<3722db.0308240953.58402f22_at_posting.google.com>...
> No one will be able to help you unless we know your version, and the
> definition behind these views and this matialized view.
>
> Daniel
Received on Mon Aug 25 2003 - 02:38:08 CEST