oracle SQL 错误：ORA-00932：不一致的数据类型：预期的 CHAR 得到 NUMBER
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19884966/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Error: ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
提问by Manual
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.
每次给我一个空结果时，我都试图得到一个结果“无”。现在它给我一个 0 的空结果。对于空结果，我怎么能有一行显示“无”而不是 0。
I have tried TO_CHAR and TO_NUMBER for the sum and I can't get it to display 'none'...
我已经尝试过 TO_CHAR 和 TO_NUMBER 的总和，但我无法让它显示“无”......
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
因此，当尝试上述操作时，我得到 SQL 错误：ORA-00932：不一致的数据类型：预期的 CHAR 为 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
回答by mrkb80
Try using the function: COALESCE (cast(sum(expr1) as varchar), 'none')
尝试使用函数： COALESCE (cast(sum(expr1) as varchar), 'none')
As a side note, I question the use of DISTINCT in your query.
作为旁注，我质疑在您的查询中使用 DISTINCT。
回答by LINQ2Vodka
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)
这将返回不同的类型。使其相同（将 SUM 转换为字符串）
回答by M.Hefny
The error is normal because your command
错误是正常的，因为你的命令
CASE WHEN SUM(ENROLLED) = 0 THEN 'none' ELSE SUM(ENROLLED) END AS ENROLLED
CASE 当 SUM(ENROLLED) = 0 THEN 'none' ELSE SUM(ENROLLED) 以 ENROLLED 结束
uses the same column as string 'none' and number SUM(ENROLLED)
使用与字符串 'none' 和数字 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
CASE 当 SUM(ENROLLED) = 0 THEN 'none' ELSE TO_CHAR(SUM(ENROLLED)) END AS ENROLLED
回答by P.Kim
Try this:
尝试这个：
COALESCE(to_char(sum(expr1)), 'none')
This should work without using cast and varchar.
这应该在不使用 cast 和 varchar 的情况下工作。
