Group By Failing on coalesce with String from different Columns

Follow

Gopinath Jaganmohan

• February 23, 2022 02:04

We got into another issue. The below query is failing with “‘Cannot group by string columns which are not dictionary encoded.’”

“”“SELECT coalesce (sales1.m_unique_id , sales2.m_customer_name) as d, count(*) from #sales_orders as sales1 LEFT JOIN (select * from #sales_orders) as sales2 on sales1.m_unique_id = sales2.m_unique_id group by d”""

Can you please help to resolve this issue.

--Gopi

0

• 

  Todd Mostak

  • February 23, 2022 03:02

  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
• 

  Gopinath Jaganmohan

  • March 04, 2022 19:47

  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.