Using Convert on an ELSE CASE SQL query

Question

I am performing a SQL query in Postgres, which selects a numeric field. I need to display a string value as the result of this SELECT, so I am using a CASE statement like this:

Select 
case numeric_field
when 100 then 'some string'
when 200 then 'some other string'

The problem is that if the numeric field has any other value (like 300 for example), I need to display this value (as a string of course). I try to put a CONVERT on the else like this

...
else CONVERT(varchar(10),numeric_field)

But it didn't work. How do I do this?

Answer 1

SELECT CASE numeric_field
          WHEN 100 THEN 'some string'
          WHEN 200 THEN 'some other string'
          ELSE numeric_field::text
       END AS result
       ...

Your statement was incomplete, END was missing. Read the manual here.

To output a numeric field as text, just cast it to text: numeric_field::text, which is the Postgres specific short form of the SQL standard call:

    cast (numeric_field AS text)

See:

• Cast syntax to convert a sum to float
• Postgres data type cast