sqloraclesql-null

SQL - Adding a % sign to NVL output/result set - pictures and code included

I am having trouble adding a percent sign to the output of the NVL() function. If you look in the desired output picture you will see that the commission needs to have a % added to it. I've tried various things but have been unable to get it to output correctly. I have tried concatenating a percent sign but that messes up the No Commission text. Any help would be appreciated!

Here is my code:

SELECT first_name,
       last_name,
       job_title,
       TO_CHAR(salary, '$99,999') AS salary,
       NVL(TO_CHAR(commission_pct*100), 'No Commission') AS commision
  FROM hr.employees e 
  JOIN hr.jobs j
    ON e.job_id = j.job_id
 ORDER BY salary DESC

[My Current Output] enter image description here

Desired Output enter image description here

Solution

  • You can rather use NVL2() function such as

    NVL2(TO_CHAR(commission_pct*100), TO_CHAR(commission_pct*100)||'%','No Commission')

    the second argument stands for not null, and the third argument for null cases.