Using a case expression on a column of type number to output it as string-CodePudding

IN Oracle DB, how can I use a case expression on a column of type number to output it as string if the column value equal a certain number?

For example:

SELECT name,
       address
       CASE
          WHEN my_num = 5 THEN 'some_string_one'
          WHEN my_num = 10 THEN 'some_string_two'
          WHEN my_num = 20 THEN 'some_string_three'
          ELSE my_num
       END AS my_num_in_string
FROM foo

Problem with what I have above is that I get the error

ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

I need a column where the numerical values are mapped to a string. Is that possible?

CodePudding user response：

A case expression's different return values must have compatible data types.

SELECT name,
       address
       CASE
          WHEN my_num = 5 THEN 'some_string_one'
          WHEN my_num = 10 THEN 'some_string_two'
          WHEN my_num = 20 THEN 'some_string_three'
          ELSE to_char(my_num)
       END AS my_num_in_string
FROM foo