Group By Failing on coalesce with String from different Columns

Comments

2 comments

  • Avatar
    Todd Mostak

    Hi Gopi, thanks for reaching out.

    The reason why that currently doesn't work is requires translation between multiple string dictionaries (one for each column), but it should work if you set each of the columns up with shared dictionaries (described here: https://docs-new.omnisci.com/sql/data-definition-ddl/datatypes-and-fixed-encoding#shared-dictionaries).

    That said, in our next release there will be a number of new capabilities allowing such translations, including case statements involving multiple string-dictionary encoded outputs (not sharing dictionaries), UNION ALL, and tests for string equality. With the new string translation framework something like COALESCE should be doable as well, although we haven't started work on that yet.

    We can provide more detail after evaluating what this would require, but until then your best bet would likely be the shared dictionary approach, i.e.

    create table flights (...
    SHARED DICTIONARY (origin_city) REFERENCES us_geography(city),
    SHARED DICTIONARY (origin_state) REFERENCES us_geography(state),
    SHARED DICTIONARY (origin_country) REFERENCES us_geography(country),
    SHARED DICTIONARY (dest_city) REFERENCES us_geography(city),
    SHARED DICTIONARY (dest_state) REFERENCES us_geography(state),
    SHARED DICTIONARY (dest_country) REFERENCES us_geography(country),
    

    Let us know if this helps!

    1
    Comment actions Permalink
  • Avatar
    Gopinath Jaganmohan

    Thanks Todd. We have the setup without Shared dictionary need to check how to make this work.

    0
    Comment actions Permalink

Please sign in to leave a comment.