Using CASE to find a certain integer and return a string

Question

I'm trying to use CASE to return a text string when a cell is a certain integer, otherwise return the original integer value.

SELECT
  CASE WHEN table.column_name = 10 THEN 'yes'
       ELSE table.column_name
  END
FROM table

I'm getting an error that 'yes' is an invalid input syntax, since it's a string and column_name is stored as integers.

You cannot do that since the result must be of a particular type. In your case it's either a string or a number. Pick one. — zerkms
– zerkms, Commented Jun 26, 2016 at 22:49
The question part of your question is missing. What do you want to know? — melpomene
– melpomene, Commented Jun 26, 2016 at 23:01

Guillaume F. · Accepted Answer · 2016-06-27 01:35:10Z

3

Your CASE returns either a number or a string, but the column has to be of a precise type. In the example below, I cast the input column to a string.

SELECT
  CASE WHEN table.column_name = 10 THEN 'yes'
       ELSE to_char(table.column_name, '999')
  END AS value
FROM table

The resulting column will be a String, either 'yes' or '12', '13', ...

answered Jun 27, 2016 at 1:35

Guillaume F.

6,5332 gold badges36 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thank you! I was thinking the input (10) and the result ('yes') had to be the same type, and wasn't thinking at all about the ELSE clause.