Oracle SQL查询报错求助：CASE语句用变量触发ORA-00900错误

核心错误原因 #

1. SELECT ... INTO语法误用：INTO HRESP_MX是PL/SQL的变量赋值语法，普通SQL查询不支持。你试图在同一条查询中引用未计算完成的变量，违反了SQL的执行顺序。
2. 小数分隔符错误：Oracle默认使用.作为小数分隔符，代码中用,会导致语法解析失败。
3. GROUP BY逻辑错误：将响应时间表达式加入GROUP BY，导致每个分组仅包含单个该值的记录，MAX函数失去统计每组最大值的作用。

修正后的SQL代码 #

WITH grouped_data AS (
    SELECT 
           TO_CHAR(CAST(ENTRY_TIM AS DATE), 'DD-MM-YYYY') AS DATET,
           TO_CHAR(CAST(ENTRY_TIM AS DATE), 'DD-MM-YYYY HH24') AS TIMET, 
           CRD_FIID,
           MAX((RE_ENTRY_TIM - EXIT_TIM)*24*60*60) AS HRESP_MX
    FROM   ORACLE.File_r6        
    WHERE DAT_TIM BETWEEN TO_DATE('05/09/2024 05:11:00', 'DD/MM/YYYY HH24:MI:SS') 
                      AND TO_DATE('05/09/2024 05:58:59', 'DD/MM/YYYY HH24:MI:SS')
      AND Resp_Cde <> '206'
      AND TYP IN ('0210', '0220')
      AND RESPONDER IN ('5')
      AND TRAN_CDE_TC <> '50'   
    GROUP BY
           TO_CHAR(CAST(ENTRY_TIM AS DATE), 'DD-MM-YYYY'),
           TO_CHAR(CAST(ENTRY_TIM AS DATE), 'DD-MM-YYYY HH24'), 
           CRD_FIID
)
SELECT 
    DATET,
    TIMET,
    CRD_FIID,
    HRESP_MX,
    CASE 
        WHEN HRESP_MX BETWEEN 0.000 AND 0.200 THEN 'R-02'
        WHEN HRESP_MX BETWEEN 0.201 AND 0.300 THEN 'R0.2'
        WHEN HRESP_MX BETWEEN 0.301 AND 0.400 THEN 'R0.3'
        WHEN HRESP_MX BETWEEN 0.401 AND 0.500 THEN 'R0.4'
        WHEN HRESP_MX BETWEEN 0.501 AND 1.000 THEN 'R0.0'
        WHEN HRESP_MX BETWEEN 1.001 AND 1.500 THEN 'R1.0'
        WHEN HRESP_MX BETWEEN 1.501 AND 2.000 THEN 'R1.5'
        WHEN HRESP_MX BETWEEN 2.001 AND 2.500 THEN 'R2.0'
        WHEN HRESP_MX BETWEEN 2.501 AND 3.000 THEN 'R2.5'
        WHEN HRESP_MX BETWEEN 3.001 AND 3.500 THEN 'R3.0'
        WHEN HRESP_MX BETWEEN 3.501 AND 4.000 THEN 'R3.5'
        WHEN HRESP_MX BETWEEN 4.001 AND 4.500 THEN 'R4.0'
        WHEN HRESP_MX BETWEEN 4.501 AND 5.500 THEN 'R4.5'
        ELSE 'R5.0' 
    END AS RESPONSE_RATING
FROM grouped_data
ORDER BY DATET, TIMET, CRD_FIID;

关键修正点 #

• 用CTE（WITH子句）先完成分组聚合计算，再在主查询中使用聚合结果进行CASE判断，避免变量引用问题。
• 将所有数字中的逗号替换为点，符合Oracle数字格式规范。
• 简化响应时间计算表达式：去掉冗余的sysdate操作，直接计算时间差的秒数。
• 用TO_DATE显式转换日期字符串，避免依赖会话日期格式导致的隐式转换错误。
• GROUP BY仅保留分组维度，确保MAX函数正确统计每组的最大响应时间。

内容的提问来源于stack exchange，提问作者Patito