ORA-00936报错求助：嵌套SELECT中start_time_as引用问题排查

你的SQL触发ORA-00936错误的核心原因是：子查询的SELECT子句中，不能直接引用同层刚定义的列别名。SQL执行时会先处理FROM、WHERE子句，再解析SELECT子句里的列，所以当你在同层用EXTRACT(YEAR FROM start_time_as)时，start_time_as这个别名还未被数据库识别，导致“缺少表达式”的报错。

另外，原SQL里的UNIQUE关键字放在EXTRACT前是无效语法，需要移除。

以下是两种可行的修正方案：

方案1：重复日期转换逻辑（简单直接） #

直接在EXTRACT中重复日期转换的计算逻辑，避免引用未解析的别名：

SELECT
        YEAR,
        MONTH,
        CAST(count(*) AS INT) AS JobNbr
FROM
        (
        SELECT
                To_Char(To_Date('01.01.1970 06:00:00','DD.MM.YYYY HH24:Mi:Ss') + STARTIME / 86400,'YYYY-MM-DD') as start_time_as,
                EXTRACT(YEAR FROM (To_Date('01.01.1970 06:00:00','DD.MM.YYYY HH24:Mi:Ss') + STARTIME / 86400)) AS YEAR,
                EXTRACT(MONTH FROM (To_Date('01.01.1970 06:00:00','DD.MM.YYYY HH24:Mi:Ss') + STARTIME / 86400)) AS MONTH,
                EXTRACT(DAY FROM (To_Date('01.01.1970 06:00:00','DD.MM.YYYY HH24:Mi:Ss') + STARTIME / 86400)) AS DAY,
                JOID,
                STARTIME,
                STATUS
        FROM
                AEDBADMIN.UJO_JOB_RUNS
        WHERE
                STATUS = '5'
        ) AS derivedTable
GROUP BY
        YEAR,
        MONTH

方案2：嵌套子查询/CTE（更清晰，避免代码重复） #

先在最内层子查询中转换出日期类型的列，再在外层提取年、月信息，这样代码更简洁易维护：

嵌套子查询版本 #

SELECT
        EXTRACT(YEAR FROM start_time_date) AS YEAR,
        EXTRACT(MONTH FROM start_time_date) AS MONTH,
        CAST(count(*) AS INT) AS JobNbr
FROM
        (
        SELECT
                To_Date('01.01.1970 06:00:00','DD.MM.YYYY HH24:Mi:Ss') + STARTIME / 86400 AS start_time_date,
                JOID,
                STARTIME,
                STATUS
        FROM
                AEDBADMIN.UJO_JOB_RUNS
        WHERE
                STATUS = '5'
        ) AS derivedTable
GROUP BY
        EXTRACT(YEAR FROM start_time_date),
        EXTRACT(MONTH FROM start_time_date)

CTE版本（Oracle 11g及以上支持） #

WITH job_runs_processed AS (
    SELECT
            To_Date('01.01.1970 06:00:00','DD.MM.YYYY HH24:Mi:Ss') + STARTIME / 86400 AS start_time_date,
            JOID,
            STARTIME,
            STATUS
    FROM
            AEDBADMIN.UJO_JOB_RUNS
    WHERE
            STATUS = '5'
)
SELECT
        EXTRACT(YEAR FROM start_time_date) AS YEAR,
        EXTRACT(MONTH FROM start_time_date) AS MONTH,
        CAST(count(*) AS INT) AS JobNbr
FROM job_runs_processed
GROUP BY
        EXTRACT(YEAR FROM start_time_date),
        EXTRACT(MONTH FROM start_time_date)

内容的提问来源于stack exchange，提问作者Juscelino Candido de Lima Juni