gpt4 book ai didi

SQL 错误 : ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

转载 作者:行者123 更新时间:2023-12-04 16:52:51 26 4
gpt4 key购买 nike

每次它给我一个空结果时,我都试图得到一个结果“无”。现在它给我一个 0 的空结果。对于空结果,我怎么能有一行显示“无”而不是 0。

我已尝试使用 TO_CHAR 和 TO_NUMBER 作为总和,但无法显示“无”...

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

所以当尝试上述操作时,我得到 SQL 错误:ORA-00932:不一致的数据类型:预期的 CHAR 得到了 NUMBER

这就是我所拥有的

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;

而不是拥有

STUDENT_ID FIRST_NAME                LAST_NAME                 ENROLLED
---------- ------------------------- ------------------------- -----------
253 Walter Boremmann 1
396 James E. Norman 0
etc

我想要这样的

STUDENT_ID FIRST_NAME                LAST_NAME                 ENROLLED
---------- ------------------------- ------------------------- -----------
253 Walter Boremmann 1
396 James E. Norman none

最佳答案

尝试使用函数:COALESCE (cast(sum(expr1) as varchar), 'none')

作为旁注,我质疑您在查询中使用 DISTINCT。

关于SQL 错误 : ORA-00932: inconsistent datatypes: expected CHAR got NUMBER,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19884966/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com