Is there any way to set 'varchar' in number type?

Question

When I write:

SELECT 
    last_name, NVL(commission_pct, 0)
FROM 
    Hr.employees;

it works fine. When commission percentage is null, then it is set to 0.

But I want to set it to 'NO COMMISSION' instead of 0.

Answer 1

you can also try coalesce function. COALESCE is ANSI standard.

select last_name, COALESCE(to_char(commission_pct),'NO COMMISSION')
from hr.employees

Answer 2

If you return the commision value as a string, yes.

select last_name, nvl(to_char(commission_pct),'NO COMMISSION')
from hr.employees