Case statement in AWS Redshift

Question

I have a table in redshift in which there are some of the fields which are having wrong values for a specific column. I am using the dbeaver tool to run queries on tables in redshift and trying to execute the below query to convert those values to the correct ones.

The query is not throwing either logical or syntactical errors but somehow not getting the correct output yet.

select 
CASE
    WHEN (profile$branch$state) IN ('Jaipur') THEN 'rajasthan'
    ELSE profile$branch$state
    END AS state
 FROM profile_detials
WHERE state ILIKE 'jaipur%'

I was expecting the output to get 'rajasthan' for the fields have the values as 'Jaipur' but I am still getting the same field values as 'Jaipur'. Is there anything wrong in the query or is there any other syntax for case statements in redshift using dbeaver tool?

The sample input would be as shown below:

profile$branch$state
JHARKHAND
Jaipur
Jammu & Kashmir
KARNATAKA
Jaipur
Orissa
Haryana
Andhraprasdesh
Assam
Jaipur
Jaipur

The output that is coming is as shown below:

state
rajasthan
rajasthan
rajasthan
rajasthan
rajasthan
rajasthan
rajasthan
rajasthan
rajasthan

The sample output I wanted is as shown below:

state
JHARKHAND
rajasthan
Jammu & Kashmir
KARNATAKA
rajasthan
Orissa
Haryana
Andhraprasdesh
Assam
rajasthan
rajasthan

Please help.

Answer 1

The problem appears to be with this line:

WHERE state ILIKE 'jaipur%'

It is saying that only rows with a State of Jaipur will be included in the output results. Not that this is referring to the state column in the profile_details table, not the state column being generated in the SELECT section.

You have not shown us the values in the state column, but I suspect whenever state = 'Jaipur' then profile$branch$state also equals Jaipur. Therefore, the output is showing rajistan for every row, since it is only showing rows where the state is Jaipur.

Try removing the WHERE and you will see a different result.