ORA-00936 on query

Question

I am facing issue ORA-00936 using the bellow query, I believe is because of the select inside of sleect using the start_time_as, but have no clue on how to solve.

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,
                UNIQUE EXTRACT(YEAR FROM start_time_as) AS YEAR,
                EXTRACT(MONTH FROM start_time_as) AS MONTH,
                EXTRACT(DAY FROM start_time_as) AS DAY,
                JOID,
                STARTIME,
                STATUS
        FROM
                AEDBADMIN.UJO_JOB_RUNS
        WHERE
                STATUS = '5' ) AS derivedTable
GROUP BY
        YEAR,
        MONTH

The bellow query works great, bellow is the query with the results.

Answer 1

First, remove the AS keyword for your derivedTable. AS is used for columns (optionally) not for tables.

Second, you are defining start_time_as in the same query block you are using it in. You will have to wait until the outer parent query block to use that reference. Nest one level deeper so you can do those EXTRACT operations on an already-defined start_time_as column.

Thirdly, you can only use DISTINCT/UNIQUE at the start of a column list, not after your first column.

Lastly, you can't TO_CHAR your date formula and then use it with EXTRACT. Leave it as a date:

Also, though it works, there is no need to cast the count(*) as an integer. It already is.

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