CODEWITHSUNDEEP
sqldatabaseoracle-database
HCzar
HCzar

Reputation: 23

Using NVL to display 'none' instead of 0

I cannot figure out how to replace 0 with 'none'. What am I doing wrong here?

SELECT s.student_id, s.first_name, s.last_name, NVL(TO_CHAR(COUNT(e.student_id)), 'none')  AS enrollments
FROM student s
LEFT OUTER JOIN enrollment e
ON s.student_id = e.student_id
WHERE s.phone LIKE '%617%'
GROUP BY s.student_id, s.first_name, s.last_name
ORDER BY s.last_name, s.first_name;

Upvotes: 1

Views: 491

Answers (1)

SandPiper
SandPiper

Reputation: 2906

Replace that single statement with:

DECODE(COUNT(e.student_id), 0, 'none', COUNT(e.student_id)) AS enrollments

What it does is it checks the count first; if there are no applicable values COUNT will return 0. Then, it will decode that to your string. If there were records, it will return the COUNT function instead.

Upvotes: 1

Related Questions