Oracle中含NVL与CASE WHEN的SQL执行报错ORA-00909求助

错误原因分析 #

• NVL函数参数缺失：Oracle的NVL函数要求传入两个参数（NVL(表达式, 替代值)），原SQL错误地将比较表达式SUBSTR(FROM_DATE,1,10) = 'NULL'直接放入NVL，既缺少第二个参数，逻辑也不符合预期。
• CASE WHEN语法不完整：原CASE语句存在括号不闭合问题（else (SUBSTR(FROM_DATE,1,10)缺少闭合)），且整体结构不符合CASE WHEN 条件 THEN 结果 ELSE 结果 END的规范。
• 子查询返回多列：CASE表达式的每个分支必须返回单一值，但原SQL中THEN后的子查询返回max(TARIKHIJAD)和ESHTERAKID两列，违反语法规则；同时该子查询未关联主表的ESHTERAKID，会返回多行结果，进一步引发错误。

修正方案 #

1. 移除错误的NVL用法，直接判断SUBSTR(FROM_DATE,1,10)是否为'NULL'字符串或NULL值；
2. 补全CASE WHEN的语法结构，确保括号闭合；
3. 修改子查询，使其仅返回单个值，并关联主表的ESHTERAKID（确保子查询返回唯一结果）。

修正后的完整SQL #

SELECT 
    CAST(ID AS NUMBER) AS ID,
    CAST(MARKETER_ID AS NUMBER) AS MARKETER_ID,
    CAST(NEW_MARKETING_BANKS_ID AS NUMBER) AS NEW_MARKETING_BANKS_ID_ASSIGNE,
    TO_CHAR(TO_DATE(REQUEST_DATE,'YYYY/MM/DD','NLS_CALENDAR=''Gregorian''')) AS TARIKH_DARKHAST,
    NEW_MARKETING_BANKS_NAME,
    STATE,
    VIN AS SHASI,
    CAST(SUBSCRIPTION_CODE AS NUMBER) AS ESHTERAKID,
    PRODUCT_NAME,
    MARKETING_GROUP,
    PERSON_TYPE,
    TO_DATE_STR,
    CASE 
        WHEN SUBSTR(FROM_DATE, 1, 10) = 'NULL' OR SUBSTR(FROM_DATE, 1, 10) IS NULL 
        THEN (SELECT MAX(TARIKHIJAD) FROM QV_JOZEAT_RIALI_FROSH WHERE ESHTERAKID = CAST(SUBSCRIPTION_CODE AS NUMBER))
        ELSE SUBSTR(FROM_DATE, 1, 10) 
    END AS TARIKH_ENGHEZA,
    IS_IKE,
    CANCELABLE,
    CAST(MARKETING_GROUP_ID AS NUMBER) AS MARKETING_GROUP_ID
FROM WH.VIW_WH_MARKETING_ASSIGNEE 
WHERE PERSON_ID IS NOT NULL 
  AND MARKETER_ID IS NOT NULL 
  AND SUBSCRIPTION_CODE IS NOT NULL 
  AND REQUEST_DATE IS NOT NULL;

说明：如果FROM_DATE中的'NULL'是实际的字符串值则保留= 'NULL'判断，若只是字段为NULL则仅保留IS NULL即可；子查询中添加关联条件是为了确保只返回当前ESHTERAKID对应的最大TARIKHIJAD，避免返回多行导致错误。

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