What happened to my CASE?

Question

CASE WHEN table1.text IS NULL THEN table2.numbers ELSE table1.text END AS newcolumn

When I run this code, I always get this error: ERROR: Unable to match CASE type character and integer

You think this will not cause problems since I am creating a new column in my query. I would also like to note that I am using an old version of PostgreSQL if this helps.

Answer 1

 CASE WHEN table1.text IS NULL THEN table2.numbers::text ELSE table1.text END AS newcolumn 

Answer 2

The problem is that you are trying to add table1.text and table2.numbers to the same column. These two columns are two types of diff data. try to follow

 CASE WHEN table1.text IS NULL THEN CAST(table2.numbers AS VARCHAR(50)) ELSE table1.text END AS newcolumn 

Answer 3

Try ISNULL

 CASE WHEN ISNULL(table1.text) = 1 THEN table2.numbers ELSE table1.text END AS newcolumn 

Answer 4

The data type represented by table2.numbers and table1.text should be the same, so in this case you need the CAST value of table2.numbers

Answer 5

I think the error is pretty clear - the types of the two columns that can be used by the CASE statement are incompatible.

Why do you think that trying to use values ​​from two columns of different types will not cause problems? You can create a new column in the result set, but it must still be of type and must match all potential values.

In many cases, in many cases it is possible to conclude about the type, but this is risky and may not necessarily be the choice you want to make, so it’s better to make it make a decision. You will need to change the type of one or the other column in the CASE statement.