I am trying to have a result 'none' every time it gives me a null result. Right now it is giving me a 0 for a null result. How could I have a row show me 'none' instead of a 0 for a null result.
I have tried TO_CHAR and TO_NUMBER for the sum and I can't get it to display 'none'...
CASE WHEN SUM(ENROLLED) = 0 THEN 'none' ELSE SUM(ENROLLED) END AS ENROLLED
so when try the above I get SQL Error: ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
this is what I have
SELECT lt.STUDENT_ID,lt.FIRST_NAME, lt.LAST_NAME, CASE WHEN SUM(ENROLLED) = 0 THEN 'none' ELSE SUM(ENROLLED) END AS ENROLLED
FROM STUDENT lt
LEFT OUTER JOIN
(SELECT s.STUDENT_ID, e.ENROLL_DATE,COUNT(z.COURSE_NO)AS ENROLLED
FROM STUDENT s
LEFT JOIN ENROLLMENT e ON s.STUDENT_ID = e.STUDENT_ID
LEFT JOIN SECTION z ON e.SECTION_ID = z.SECTION_ID
WHERE s.PHONE LIKE '702%'
GROUP BY s.STUDENT_ID, e.ENROLL_DATE) rt
ON lt.STUDENT_ID = rt.STUDENT_ID
WHERE lt.PHONE LIKE '702%'
GROUP BY lt.STUDENT_ID,lt.FIRST_NAME, lt.LAST_NAME,ENROLLMENTS;
instead of having
STUDENT_ID FIRST_NAME LAST_NAME ENROLLED
---------- ------------------------- ------------------------- -----------
253 Walter Boremmann 1
396 James E. Norman 0
etc
I'd like to have it like this
STUDENT_ID FIRST_NAME LAST_NAME ENROLLED
---------- ------------------------- ------------------------- -----------
253 Walter Boremmann 1
396 James E. Norman none
Try using the function: COALESCE (cast(sum(expr1) as varchar), 'none')
As a side note, I question the use of DISTINCT in your query.