Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Is it possible to have complex queries on flattened tables referencing dimension tables?
So I have a dimension table like this:
create table my_dimension_table ( main_key int NOT NULL, linked_id int, CONSTRAINT C_PRIMARY PRIMARY KEY (main_key) ENABLED )
and my flattened table is like so
create table my_flattened_table ( mod_date date, some_value varchar(50), main_key int NOT NULL, linked_key. DEFAULT USING (SELECT CASE WHEN (my_dimension_table.linked_id IS NULL) THEN my_dimension_table.main_key ELSE my_dimension_table.linked_id END FROM my_dimension_table WHERE (my_flattened_table.main_key = my_dimension_table.main_key)), )
This doesn't seem to work. Is there something similar I can do to achieve this?
So background on what I'm trying to do here:
Basically we perform complex queries on
my_flattened_table.linked_key and do many group by. This field needs to be equal to
my_dimension_table.linked_id when its not null, otherwise equal to
Any suggestions appreciated!
Leave a Comment
Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.
That's not supported, but you could try creating two column references in your flattened table to both of the columns in the dimension. A flattened table reference has to return only 1 record, but it will just return null if it joins on nothing, which would still be OK.
I think you can achieve it using a NVL() function. If a flattened column is NULL because the matching - found - column of the joined row in the dimension table is NULL, then your expression works.
If it is NULL because the join fails, the flattened column remains NULL.
We are in the lucky situation where the replacement of the flattened column is the join column, so we can use the fact table's join column as the second parameter of an NVL() function. See the whole scenario here.
thanks will try this out!