CODEWITHSUNDEEP
sqloracle-databasesumto-char
Manual
Manual

Reputation: 1687

SQL Error: ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

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

Upvotes: 3

Views: 17113

Answers (4)

P.Kim
P.Kim

Reputation: 1

Try this:

COALESCE(to_char(sum(expr1)), 'none')

This should work without using cast and varchar.

Upvotes: 0

M.Hefny
M.Hefny

Reputation: 2743

The error is normal because your command

CASE WHEN SUM(ENROLLED) = 0 THEN 'none' ELSE SUM(ENROLLED) END AS ENROLLED

uses the same column as string 'none' and number SUM(ENROLLED)

you can use the column as string and display the number in the same time using

CASE WHEN SUM(ENROLLED) = 0 THEN 'none' ELSE TO_CHAR(SUM(ENROLLED)) END AS ENROLLED

Upvotes: 1

LINQ2Vodka
LINQ2Vodka

Reputation: 3036

CASE WHEN SUM(ENROLLED) = 0 THEN 'none' ELSE SUM(ENROLLED) END AS ENROLLED

this returns different types. Make it the same (cast SUM to string)

Upvotes: 3

mrkb80
mrkb80

Reputation: 591

Try using the function: COALESCE (cast(sum(expr1) as varchar), 'none')

As a side note, I question the use of DISTINCT in your query.

Upvotes: 5

Related Questions