ERROR: Duplicate tables in projection not allowed

[Josh]

Hi,

I am trying to create a projection but am receiving the error "ERROR: Duplicate tables in projection not allowed"

Code: Select all

dbadmin=> create projection test_pr as
dbadmin-> select store_billing_line_item.*
dbadmin->   from Vertica.billing_system.store_billing_line_item store_billing_line_item
dbadmin->   join Vertica.billing_system.date_dim date_dim_presale
dbadmin->     on date_dim_presale.date_key = store_billing_line_item.presale_date_key
dbadmin->   join Vertica.billing_system.date_dim date_dim_sale
dbadmin->     on date_dim_sale.date_key = store_billing_line_item.sale_date_key
dbadmin->   join Vertica.billing_system.date_dim date_dim_post
dbadmin->     on date_dim_post.date_key = store_billing_line_item.post_date_key
dbadmin->   join Vertica.billing_system.date_dim date_dim_service
dbadmin->     on date_dim_service.date_key = store_billing_line_item.service_date_key;
ERROR:  Duplicate tables in projection not allowed

Why can't we join to the same table more than once in a projection? Should I just do a cross join?

Thanks!

[id10t]

Hi, Josh!

And if:

Code: Select all

create projection test_pr as
select *
  from Vertica.billing_system.store_billing_line_item store_billing_line_item
  join Vertica.billing_system.date_dim date_dim_presale
    on date_dim_presale.date_key = store_billing_line_item.presale_date_key
where date_dim_presale.date_key = store_billing_line_item.sale_date_key
   and date_dim_presale.date_key = store_billing_line_item.post_date_key
   and date_dim_presale.date_key = store_billing_line_item.service_date_key;

[rajasekhart]

Hi,

I heard that vertica does not allow self joins.

Thanks,
Raj

[Josh]

Thanks, guys! But I'm not trying to do a self join. We have a date dimension table that I'm trying to join to from a single fact table by the way of four foreign keys relationships. The date columns in the fact table each have a different key that should join to three different rows in the date dimension. Vertica allows me to run a query with the joins but I can't create a projection based on that query. This must be a limitation in Vertica.