Redshift DirectQuery locate() error

[LimitFounder]

I'm trying to connect to AWS Redshift, with built-in connector, in DirectQuery mode.

I'm using a custom SQL code (simple SELECT * FROM ... ) due to performance reasons.

Everything is working fine, until I would like to use a filter like this:

PowerBI ends up with the following error message:
Underlying Error: QueryUserError
More details: OLE DB or ODBC error: [DataSource.Error] ODBC: ERROR [42883] [Redshift][ODBC Driver][Server]42883:ERROR: function locate("unknown", character varying) does not exist
HINT: No function matches the given name and argument types. You may need to add explicit type casts.

Any ideas how to solve this?

Thank you in advance!

[v-kathullac]

Hi @LimitFounder   ,

Thank you for reaching out to Microsoft Fabric Community Forum,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

Regards,

Chaithanya

[v-kathullac]

Hi @LimitFounder ,

Thank you for sharing your findings and the workaround. We're glad to hear that creating the following user-defined function on the Amazon Redshift side resolved the issue:

CREATE OR REPLACE FUNCTION public.locate(varchar, varchar)
RETURNS integer
IMMUTABLE
AS $$ SELECT POSITION($1 IN $2) $$
LANGUAGE sql;

Regarding your question, this is not a documented or officially recommended Microsoft solution. Rather, it is a valid workaround that resolves the issue in your Redshift environment by providing compatibility for the LOCATE() function referenced in the generated query.

If the workaround meets your requirements, it is reasonable to continue using it. Otherwise, we recommend monitoring future Power BI Desktop and Amazon Redshift ODBC driver updates for any changes related to SQL translation or compatibility.

Thank you for sharing your solution it may also benefit other users who encounter similar behavior.

Thanks,

Chaithanya.

[LimitFounder]

Thanks for reaching out Chaithanya!

Even if this UDF is working now, I don't think that PowerBI should work like this. It is sending a MySQL function to Redshift, which should not be the case.

I've filed a ticket for MS with this, and will update this thread once I received an answer.

[v-kathullac]

Hi @LimitFounder  , 

Thank you for the update.

We're glad to know your case is now with the appropriate support team. We hope the issue will be  resolved soon.

If you need any further assistance from our side or have any additional questions, please feel free to reach out. We'll be happy to assist you.

Thanks,

Chaithanya.

[LimitFounder]

Solved with a user defined function on Redshift side:

CREATE OR REPLACE FUNCTION public.locate(varchar, varchar)
RETURNS integer
IMMUTABLE
AS $$ SELECT POSITION($1 IN $2) $$
LANGUAGE sql;

But I'm still wondering if this was the offical way to solve this... ?!