Forum Discussion

LimitFounder's avatar
LimitFounder
New Member
3 months ago

Redshift DirectQuery locate() error

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 fin...
Need Help
v-kathullac's avatar
v-kathullac
Icon for Community Support rankCommunity Support
3 months ago

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's avatar
LimitFounder
New Member
3 months ago

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's avatar
    v-kathullac
    Icon for Community Support rankCommunity Support
    3 months ago

    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.