You can translate the question and the replies:

Collate functionality with Like operator

As denodo does not have collate functionality, i have written a custom function that delegates collate to snowflake server and I am trying to use like operator in denodo vdp client with collate as shown below, SELECT * FROM <myview> where collate(<name>,'en-ci') like '<name>%'; As per my understanding, snowflake like works with these 2 cases collate(<name>,'en-ci') and collate(<name>,''), however i am getting the following error for the above query in the vdp client, Can anyone address this issue whats wrong or difference between snowflake excecuting the same query and denodo executing the same with collate being delegated to snowflake QUERY [VIRTUAL] [ERROR] QUERY [JDBC WRAPPER] [ERROR] QUERY [JDBC ROUTE] [ERROR] Received exception with message 'SQL compilation error: error line 1 at position 5,340 Function LIKE does not support collation: 1.'
snowflake collate with like denodo like operator
user
05-05-2021 14:19:48 -0400
code

1 Answer

Hi, When I faced a similar error when working with Snowflake data source in the Virtual DataPort Administration Tool, I ensured that the option *Allow Literal as parameter is set to No* in the *Source configuration* of Snowflake JDBC data source. This would allow the Virtual DataPort to generate a delegable query with literals in it. You could refer to the section [Data Source Configuration Properties](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/creating_data_sources_and_base_views/data_source_configuration_properties/data_source_configuration_properties#data-source-configuration-properties) of Virtual DataPort Administration Guide for more information. Hope this helps.
Denodo Team
20-05-2021 07:21:40 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here