How use conditional expression returning either number or string?-CodePudding

I have a query using condition where the first result is a number and the second is a string.

Example:

Select Decode(Emp_Type,1,salary , 'The Employee has no salary') 
from Emp;

It gives me this error:

ORA-01722: invalid number

When I use another statement using CASE:

Select case when  Emp_Type = 1 then salary else 'The Employee has no salary' End
from Emp;

it gives me this error:

ORA-00932: inconsistent datatypes : expected NUMBER got CHAR

What is the solution?

CodePudding user response：

You can't have one expression that returns a variable data type. The return type needs to be known and fixed when the statement is parsed and executed.

You need to convert the values so they are the same data type; as your string can't be converted to a number, you would have to convert your number to a string:

Select Decode(Emp_Type,1, to_char(salary), 'The Employee has no salary') from Emp;

Select case when Emp_Type = 1 then to_char(salary)
  else 'The Employee has no salary' End from Emp;

You can supply a format mask to get the salary in a specific format; otherwise it will use a default format. From the documentation:

If you omit fmt, then n is converted to a VARCHAR2 value exactly long enough to hold its significant digits.

and it uses the TM 'text minimum' format model to do that.

CodePudding user response：

I'd recommend to return with null in case of "no salary". You can later check whether the result has a meaningful value or not.

Select Decode(Emp_Type, 1, salary, NULL) from Emp;