1

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

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
fice
  • 23
  • 4

1 Answers1

2

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.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55