Oracle BIP报表WHERE子句用CASE语句触发ORA-00905缺失关键字错误

错误原因 #

ORA-00905错误源于WHERE子句中CASE表达式的使用方式不符合Oracle语法：Oracle的CASE表达式无法直接返回布尔判断结果（如>30），必须返回一个可比较的标量值（如数字、字符串），再与固定值匹配。此外原查询中还有一个逻辑错误：between 30 and 1是无效范围，因为BETWEEN的下限必须小于等于上限，应改为between 1 and 30。

解决方案一：修正CASE表达式逻辑 #

将CASE表达式返回1（符合条件）或0（不符合条件），再判断结果等于1，同时修正日期差的范围逻辑：

SELECT
    GMS_AWDINFO.CONTRACT_NUMBER  AWARD_NUMBER
    ,GMS_AWDINFO.CONTRACT_NAME  AWARD_NAME
    ,GMS_AWDINFO.ATTRIBUTE2 BILL_TYPE
    ,GMS_AWDINFO.STS_CODE AWARD_STATUS
    ,TO_CHAR(GMS_AWDINFO.END_DATE,'MM/DD/YYYY') AS AWARD_END_DATE
    ,(CASE 
     WHEN GMS_AWDINFO.END_DATE - TRUNC(SYSDATE) > 30 
       THEN 'Over 30 Days'
     WHEN GMS_AWDINFO.END_DATE - TRUNC(SYSDATE) BETWEEN 1 AND 30 THEN 'Within 30 Days'
     WHEN GMS_AWDINFO.END_DATE - TRUNC(SYSDATE) < 1 THEN 'Expired'
     END) AS AWARD_EXPIRATION_STATUS  
FROM 
    GMS_AWARD_HEADERS_INFO_V GMS_AWDINFO   
WHERE   
    GMS_AWDINFO.CONTRACT_NUMBER IS NOT NULL
    AND (GMS_AWDINFO.CONTRACT_NUMBER IN (:P_AWARD_NUMBER) OR 'ALL' IN (:P_AWARD_NUMBER||'ALL'))
    AND (GMS_AWDINFO.STS_CODE IN (:P_AWARD_STATUS) OR 'ALL' IN (:P_AWARD_STATUS||'ALL'))
    AND (
        CASE 
            WHEN :P_EXPIRATION_STATUS = 'Over 30 Days' THEN 
                CASE WHEN GMS_AWDINFO.END_DATE - TRUNC(SYSDATE) > 30 THEN 1 ELSE 0 END
            WHEN :P_EXPIRATION_STATUS = 'Within 30 Days' THEN 
                CASE WHEN GMS_AWDINFO.END_DATE - TRUNC(SYSDATE) BETWEEN 1 AND 30 THEN 1 ELSE 0 END
            ELSE 
                CASE WHEN GMS_AWDINFO.END_DATE - TRUNC(SYSDATE) < 1 THEN 1 ELSE 0 END
        END = 1
    )
GROUP BY 
     GMS_AWDINFO.CONTRACT_NUMBER 
    ,GMS_AWDINFO.CONTRACT_NAME  
    ,GMS_AWDINFO.ATTRIBUTE2 
    ,GMS_AWDINFO.STS_CODE 
    ,GMS_AWDINFO.END_DATE

注：如果END_DATE字段本身是DATE类型，无需用TO_DATE转换，直接计算日期差即可，避免隐式转换带来的潜在错误。

解决方案二：用逻辑表达式替代CASE（更简洁） #

将参数判断与对应条件通过AND/OR组合，可读性更强，也避免CASE的语法限制：

SELECT
    GMS_AWDINFO.CONTRACT_NUMBER  AWARD_NUMBER
    ,GMS_AWDINFO.CONTRACT_NAME  AWARD_NAME
    ,GMS_AWDINFO.ATTRIBUTE2 BILL_TYPE
    ,GMS_AWDINFO.STS_CODE AWARD_STATUS
    ,TO_CHAR(GMS_AWDINFO.END_DATE,'MM/DD/YYYY') AS AWARD_END_DATE
    ,(CASE 
     WHEN GMS_AWDINFO.END_DATE - TRUNC(SYSDATE) > 30 
       THEN 'Over 30 Days'
     WHEN GMS_AWDINFO.END_DATE - TRUNC(SYSDATE) BETWEEN 1 AND 30 THEN 'Within 30 Days'
     WHEN GMS_AWDINFO.END_DATE - TRUNC(SYSDATE) < 1 THEN 'Expired'
     END) AS AWARD_EXPIRATION_STATUS  
FROM 
    GMS_AWARD_HEADERS_INFO_V GMS_AWDINFO   
WHERE   
    GMS_AWDINFO.CONTRACT_NUMBER IS NOT NULL
    AND (GMS_AWDINFO.CONTRACT_NUMBER IN (:P_AWARD_NUMBER) OR 'ALL' IN (:P_AWARD_NUMBER||'ALL'))
    AND (GMS_AWDINFO.STS_CODE IN (:P_AWARD_STATUS) OR 'ALL' IN (:P_AWARD_STATUS||'ALL'))
    AND (
        (:P_EXPIRATION_STATUS = 'Over 30 Days' AND GMS_AWDINFO.END_DATE - TRUNC(SYSDATE) > 30)
        OR (:P_EXPIRATION_STATUS = 'Within 30 Days' AND GMS_AWDINFO.END_DATE - TRUNC(SYSDATE) BETWEEN 1 AND 30)
        OR (:P_EXPIRATION_STATUS NOT IN ('Over 30 Days','Within 30 Days') AND GMS_AWDINFO.END_DATE - TRUNC(SYSDATE) < 1)
    )
GROUP BY 
     GMS_AWDINFO.CONTRACT_NUMBER 
    ,GMS_AWDINFO.CONTRACT_NAME  
    ,GMS_AWDINFO.ATTRIBUTE2 
    ,GMS_AWDINFO.STS_CODE 
    ,GMS_AWDINFO.END_DATE

额外优化建议 #

• 如果参数:P_EXPIRATION_STATUS支持ALL选项，可以在逻辑中添加对应的判断，比如：

  AND (
      :P_EXPIRATION_STATUS = 'ALL'
      OR (:P_EXPIRATION_STATUS = 'Over 30 Days' AND GMS_AWDINFO.END_DATE - TRUNC(SYSDATE) > 30)
      OR (:P_EXPIRATION_STATUS = 'Within 30 Days' AND GMS_AWDINFO.END_DATE - TRUNC(SYSDATE) BETWEEN 1 AND 30)
      OR (:P_EXPIRATION_STATUS = 'Expired' AND GMS_AWDINFO.END_DATE - TRUNC(SYSDATE) < 1)
  )
  

• 日期差计算时，TRUNC(SYSDATE)可以确保只计算天数差，忽略时分秒部分。

内容的提问来源于stack exchange，提问作者Mr.DevEng