Prejoin Projections - Subqueries not allowed

Navin_C

Hello All,

I am trying to create a prejoin projection from two table which my query can access.

I have joined two table using a inner join but my requirement is to get a unique row for every combination, for now for every combination I can see more then one value after the join statement.

I tried doing this using the row_number function with windows partition by clause as subquery and filtering the records with rank 1.

But when I use it in creating a prejoin projection it says "subqueries not allowed inside a projection definition"

What can we do here to build this projections

Besides this:

• Do we have materialized views in Vertica which can be used instead of projections.
• How can we validate a prejoin projection has only unique data for a specific combination of key columns.
• What happens to prejoin projections when the data gets updated or inserted in the base table.

Thanks

colin_loghin

Navin, The prejoin is governed by the foreign key  - primary key inner join , so multiple instances of the foreign keys are possible if the fact table is so. If using prejoin, your best bet if to order by the fields you need to dedupe and run the distinct query on top of the join which should leverage the prejoin.

The prejoin does not accept anything fancier than a inner join. If you need, you could bypass the prejoin altogether and build an aggregate table with the result you need. Of course you would need to rebuild every time underlying data change.

Materialized views ? => No, but as above, you can build your own derived aggregated table
Unique data ? => you cannot, it is based on PK- FK only
Updates/ Inserts  ? => prejoin gets updated . inserted as well. It reflects the logical changes in the relational tables. You should avoid updates in the PK table ( dimensions ) , because it can trigger a large number of updates in the prejoin ( many FK for 1 PK possibly ).

Hope this helps a bit.

Cheers,
colin