Is it possible to have complex queries on flattened tables referencing dimension tables?

zun

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 my_dimension_table.main_key.

Any suggestions appreciated!

Vertica_Curtis

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.

marcothesane

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.

drop table if exists my_dimension_table CASCADE;                                                                                                                                                          
-- out DROP TABLE
create table my_dimension_table
(
    main_key           int NOT NULL,
    linked_id          int,
    CONSTRAINT C_PRIMARY PRIMARY KEY (main_key) DISABLED
 )
;
-- out CREATE TABLE

drop table if exists my_flattened_table CASCADE;
-- out DROP TABLE
create table my_flattened_table
(
    mod_date        date
  , some_value      varchar(50)
  , main_key        int NOT NULL
  , linked_key      INT 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
    )
  , linked_key_nvl   INT DEFAULT USING (
       NVL((SELECT my_dimension_table.linked_id FROM my_dimension_table WHERE my_flattened_table.main_key = my_dimension_table.main_key),my_flattened_table.main_key )
    )
 )
;
-- out CREATE TABLE

INSERT INTO my_dimension_table
          SELECT 1,11
UNION ALL SELECT 2,NULL::INT
UNION ALL SELECT 4,42
;COMMIT;
-- out  OUTPUT 
-- out --------
-- out       3
-- out COMMIT

INSERT INTO my_flattened_table(mod_date,some_value,main_key)
          SELECT DATE '2022-08-05','should have 11', 1
UNION ALL SELECT DATE '2022-08-06','should have  2', 2
UNION ALL SELECT DATE '2022-08-07','should have  3', 3
UNION ALL SELECT DATE '2022-08-08','should have 42', 4
;COMMIT;
-- out  OUTPUT 
-- out --------
-- out       4
-- out (1 row)
-- out COMMIT

SELECT * FROM my_flattened_table order by 1;
-- out   mod_date  |   some_value   | main_key | linked_key | linked_key_nvl 
-- out ------------+----------------+----------+------------+----------------
-- out  2022-08-05 | should have 11 |        1 |         11 |             11
-- out  2022-08-06 | should have  2 |        2 |          2 |              2
-- out  2022-08-07 | should have  3 |        3 |            |              3
-- out  2022-08-08 | should have 42 |        4 |         42 |             42

zun

thanks will try this out!