Case Statement with multiple conditions and excluding null values

user028686 · 2022-04-29T19:12:07+00:00

There was an error rendering this rich post.

I am trying to create a case statement where I have a new column that replaces a value in column1 with a value from column 2 but only if column 2 is not null. If it is null, then the value in column1 needs to remain the same.

Sample table:

I need it to look like this:

So "Account A" is replaced with "name" from column 2 when it is available.

I have tried this but it is not working:

(CASE

when

`Column2`is not null

and `Column1`= 'Account A'

then `Column2`

else `Column1`

End)

What am I doing wrong?

Find more posts tagged with

Accepted answers

MarkSnodgrass

If you're only wanting this to process for Account A, I would a nested case statement like this:

(CASE WHEN TRIM(`column1`) = 'Account A' THEN
(CASE WHEN TRIM(`column2`) = '' THEN `column1`
ELSE `column2`
END)
ELSE `column1`
END)

All comments

MarkSnodgrass

Try just doing this:

IFNULL(column2,column1)

user028686

Thank you. That does not seem to give me the correct result because if column 2 is blank, then it is showing blank in the new column.

MarkSnodgrass

Blanks and nulls are not the same, so the ifnull function would not help with this data. I would try this to look for blanks:

(CASE WHEN TRIM(`column2`) = '' THEN `column1`
ELSE `column2`
END)

The trim function removes any spaces that may be in the data and then you can evaluate it to see if it is blank.

Hope this helps.

user028686

Thank you! I am getting closer...the first two rows are displaying correctly now, but now Account B is also being replaced with the name field, so how do I prevent that from happening?

MarkSnodgrass

If you're only wanting this to process for Account A, I would a nested case statement like this:

(CASE WHEN TRIM(`column1`) = 'Account A' THEN
(CASE WHEN TRIM(`column2`) = '' THEN `column1`
ELSE `column2`
END)
ELSE `column1`
END)

user028686

That worked, thank you!