I am attempting to create a materialized view that is to be made up of all columns of an existing table, whilst adding any missing values in a date column based on the dates in a different table that is joined by value in a separate (firstname) column. So both tables have date and matching firstname columns but in the main table some of the date fields are missing and need to be sourced from the other table.
I am doing this in a SELECT statement, but am unsure whether or not this is the best way to do it. Any thoughts appreciated.
So far I have this:
CREATE materialized view mviewname as (
SELECT a.col1, a.col2, a.firstname, NVL(a.dateofbirth, (
SELECT b.dateofbirth from FillTable b JOIN MainTable c on b.firstname=c.firstname)) from MainTable a);
Clearly this is incorrect and I am getting the "single-row subquery returns more than one row" error for obvious reasons. I'm also unsure whether or not MAINTABLE should be aliased twice, or whether "c" should just be "a" as well.
Any help is greatly appreciated.
In case it matters re: question asking, I did do a similar first post (referenced below) but only asked half the question and hence should have worded my first post better, sorry!
Create Materialized View using SELECT and adding new values where NULL
A left outer join should be able to satisfy your requirement.
SELECT a.col1, a.col2, a.firstname, NVL(a.dateofbirth, b.dateofbirth) FROM maintable a LEFT OUTER JOIN filltable b ON a.firstname = b.firstname;
However, the b table should contain only one record for each firstname, else you might get multiple records for one first name.
Also, why are you creating a materialized view. A simple view should serve your purpose if the volumes are not huge.
Please go through the following Oracle documentation for information on joins.
If you are insistent on creating a materialized view then you might want to consider defining a refresh strategy for the same.
Here are some helpful links.