Error when using CASE in INDB Formula tool

[Beth1234]

I'm trying to use CASE to fill column A based on whether or not column A is null. If it's null, I'll populate it with column B.

The error I get is: "Syntax error at or near "then""

CASE
when
"A" = ''
then
"B"
else
"A"
end

Your help and time are appreciated.

[cjaneczko]

I believe you have to use the IF format, not CASE. 

IF
"A" = ''
then
"B"
else
"A"
endif

And you need to add multiple conditions, you would use ELSEIF 

[TimN]

Try with single quotes like this:

CASE
when
'A' = ''
then
'B'
else
'A'
end

[simonaubert_bd · Accepted solution]

Hello @Beth1234 

If you're using in-db formula, you have to think it as pure sql. So it means you have to refer to your database documentation

And in SQL, like in Alteryx, you have "enclosing characters" for fields name and that depends on your db. usually, it's " (so "myfield" ) but sometimes it's ` (so `myfield` ) like in Apache Hive. You will

Also null and empty string is not the same (empty string is a value and null is a state)

You can try (note : I removed enclosing characters since I don't know your database)

CASE A
when '' then B
else A
end

But if it's really to check a null, my favorite way is to use coalesce

coalesce(A,B)

please be also careful with upper/lower case for your field name.

Best regards,

Simon

[Beth1234]

The coalesce function worked! Thank you!