ORA-00904: "YEAR"标识符无效问题排查及替代方案咨询

错误原因 #

Oracle数据库没有YEAR()和MONTH()函数，这两个是MySQL等其他数据库的专属语法，因此Oracle无法识别这两个标识符，触发ORA-00904错误。

替代实现方案 #

可以使用Oracle原生的EXTRACT()函数或TO_CHAR()函数来提取日期的年、月信息，以下是两种修改后的SQL版本：

方案1：使用EXTRACT()函数（推荐，更直观） #

SELECT 
    P.EVT_PRGM_EDITION,
    SUM(P.EVT_NBSESSIONS) AS SESSIONS
FROM 
    PLANNING P, FACULTY F, PROGRAM PGR
WHERE F.FAC_NUM=P.EVT_FAC1
AND PGR.PRGM_NUM=P.EVT_PRGM
AND P.EVT_STAT='ST001'
AND (PGR.PRGM_CMAJ IS NULL OR PGR.PRGM_CMAJ <> 'S')
AND CASE  
    WHEN EXTRACT(MONTH FROM P.EVT_DATE) < 09 
        THEN (EXTRACT(YEAR FROM P.EVT_DATE)-1) || '-' || EXTRACT(YEAR FROM P.EVT_DATE) 
    ELSE EXTRACT(YEAR FROM P.EVT_DATE) || '-' || (EXTRACT(YEAR FROM P.EVT_DATE)+1) 
    END = :ACAD_YEAR
AND F.FAC_NUM = :PROFESSOR_ID
GROUP BY P.EVT_PRGM_EDITION -- 注意：原SQL中SELECT列是EVT_PRGM_EDITION，但GROUP BY是EVT_PRGM_EDITION_IESE，需保持一致，否则会触发ORA-00979错误

方案2：使用TO_CHAR()函数 #

SELECT 
    P.EVT_PRGM_EDITION,
    SUM(P.EVT_NBSESSIONS) AS SESSIONS
FROM 
    PLANNING P, FACULTY F, PROGRAM PGR
WHERE F.FAC_NUM=P.EVT_FAC1
AND PGR.PRGM_NUM=P.EVT_PRGM
AND P.EVT_STAT='ST001'
AND (PGR.PRGM_CMAJ IS NULL OR PGR.PRGM_CMAJ <> 'S')
AND CASE  
    WHEN TO_NUMBER(TO_CHAR(P.EVT_DATE, 'MM')) < 09 
        THEN (TO_NUMBER(TO_CHAR(P.EVT_DATE, 'YYYY'))-1) || '-' || TO_CHAR(P.EVT_DATE, 'YYYY') 
    ELSE TO_CHAR(P.EVT_DATE, 'YYYY') || '-' || (TO_NUMBER(TO_CHAR(P.EVT_DATE, 'YYYY'))+1) 
    END = :ACAD_YEAR
AND F.FAC_NUM = :PROFESSOR_ID
GROUP BY P.EVT_PRGM_EDITION -- 修正GROUP BY列与SELECT列一致

额外注意事项 #

原SQL中SELECT子句的列是P.EVT_PRGM_EDITION，但GROUP BY子句写的是P.EVT_PRGM_EDITION_IESE，这会触发ORA-00979错误（不是GROUP BY表达式），需要将两者保持一致。

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