Left Outer Join & Union Operators

[jaypn007]

Hi,
I have Materialized view queries in Oracle which uses complex left outer joins and some union operators. When I want to convert them in Vertica projections, it fails with error messages.
Can anyone please shed lights on how do we get around this problems? Thanks in Advance.

Regards,
Jay

[nnani]

Hello jaypn007

Welcome to Vertica Forums.

First of all you need to understand there is no difference between Oracle's Materialized views and projections in Vertica

So Projections are nothing but materialized views in Vertica.

So, In order to migrate materialized view in vertica. you need to create a table with all columns you need in your projections and projection will be created for this table automatically.
In your scenario, you can create a pre-join projection directly using the CREATE PROJECTION statement,
but it has some limitations
1. It can only have inner joins
2. No where clause allowed in select statement

Sample prejoin projection

Code: Select all

CREATE PROJECTION prejoin_p (fk, pk, x) AS SELECT * FROM fact, dim WHERE pk=fk ORDER BY x;

You need to migrate your materialized view into tables in Vertica
It will be better, if you can paste some sample code, so that we can give you clear idea on how to convert.

Hope this helps