CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-redshift
curiosityrock
curiosityrock

Reputation: 213

AWS redshift join with a function returns an error

I am trying to join two different tables by using a left function - for reference (https://docs.aws.amazon.com/redshift/latest/dg/r_LEFT.html) And it throws an error saying "invalid operation" i wish the error message was a little more helpful. If i remove the left function then it works but i need to make it work with a function as i didn't see any limitations on docs.. I am not sure why it doesn't work with a function as Any advice on how to solve it?

select id from state
LEFT JOIN city c ON lower(left(c.segmentation_name::text, '-4'::integer)) = lower(state.name::text)

Upvotes: 1

Views: 435

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you want all but the last four characters, then use:

from state s left join
     city c 
     on substring(lower(c.segmentation_name::text), 1, len(c.segmentation_name::text) - 4) = lower(state.name::text)

The conversions to text look really awkward. Perhaps this is sufficient:

from state s left join
     city c 
     on substring(lower(c.segmentation_name), 1, len(c.segmentation_name) - 4) = lower(state.name)

Upvotes: 2

Sergey Geron
Sergey Geron

Reputation: 10172

left supports only positive integers as a second argument. Try right(c.segmentation_name::text, 4) instead.

Upvotes: 1

Related Questions