473,327 Members | 1,936 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 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 4949
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Prashant | last post by:
Hi I have a problem with refreshing of Mviews , I will narrate every thing step by step: 1.create table a (a number); 2.insert into table a values(&a); (after inserting 2 rows and commiting)...
1
by: Matthieu Exbrayat | last post by:
Hie all, I am currently using materialized views within Oracle 9i. For instance I create mv1 on DB bddist2, as a snapshot of sur1 on bddist1 : SQL> create materialized view mv1 2 refresh...
3
by: M. Mehta | last post by:
It seems that you can not create a materialized view if you are using outer joins...can someone please verify this? Thanks M. Mehta Please follow my example below: created 2 tables:
6
by: Vincent LIDOU | last post by:
Do not trust values returned by materialized views under SQL Server without frequently checking underlying tables!!! I already posted this message under microsoft.public.sqlserver.server and I'm...
1
by: Hans-Jürgen Schönig | last post by:
hello ... i am running into a couple of questions regarding materialized view logs. my goal is to take data from an oracle database and replicate it to some remote data store (maybe some other...
7
by: Esteban Kemp | last post by:
PostgreSql support materialized views ??? if not, there is something similar?? Thanks Esteban kemp
1
by: rumasinha | last post by:
Hi, I first created a materialized view that was based on the org striped views like po_headers hence the automatic refresh was not happening. After automatic complete refresh, the materialized...
2
by: Preeti Bajaj | last post by:
I created a materialized view log on table DUMMY_TAB as shown below: create materialized view log on DUMMY_TAB with primary key including new values; Primary key for the table DUMMY_TAB is...
3
by: kewldotnet | last post by:
I have a query thats taking long time to execute. So i have created a Materialized view to refresh it every hour. But when the Materialized view is being refreshed, there is no data in the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.