ORA-00905: missing keyword on ORACLE-CodePudding

I have this SQL request, works fine in PostgreSQL, but throws an error "Java::JavaSql::SQLSyntaxErrorException: ORA-00905: missing keyword" in Oracle SQL. I assume, something here is an Oracle keyword, but can't find it. Tried changing "max" and "master" - same error.

  SELECT
    id AS "ID",
    imitation_mode AS "IMITATION_MODE",
    time_begin AS "TIME_BEGIN",
    time_end AS "TIME_END",
    max(time_begin) AS "MAX_TIME_BEGIN"
  FROM ssp_car_flow_master_plan master
  INNER JOIN (SELECT max(time_begin) FROM ssp_car_flow_master_plan) AS max
  ON master.time_begin = max
  GROUP BY imitation_mode, time_begin, time_end, id

CodePudding user response：

Use the MAX analytic function to avoid querying the table twice:

SELECT id,
       imitation_mode,
       time_begin,
       time_end
FROM   (
  SELECT id,
         imitation_mode,
         time_begin,
         time_end,
         MAX(time_begin) OVER () AS max_time_begin
  FROM   ssp_car_flow_master_plan
)
WHERE time_begin = max_time_begin;

Or, from Oracle 12, you can use FETCH FIRST ROW WITH TIES:

SELECT id,
       imitation_mode,
       time_begin,
       time_end,
FROM   ssp_car_flow_master_plan
ORDER BY time_begin DESC
FETCH FIRST ROW WITH TIES;

Your query fails because:

In Oracle it is invalid syntax to use AS before a table/sub-query alias.
MAX is a keyword and can be confusing if you use it as an alias.

If you want to fix your query then:

SELECT s.id,
       s.imitation_mode,
       s.time_begin,
       s.time_end,
       m.max_time_begin
  FROM ssp_car_flow_master_plan s
       INNER JOIN (
         SELECT max(time_begin) AS max_time_begin
         FROM   ssp_car_flow_master_plan
       ) m
       ON s.time_begin = m.max_time_begin

CodePudding user response：

Try and void using keywords as aliases. Your query logic seems slightly odd; youseem to want only the distinct records whose time_begin is equal to the latest time_begin in the table?

  SELECT DISTINCT
    master.id AS "ID",
    master.imitation_mode AS "IMITATION_MODE",
    master.time_begin AS "TIME_BEGIN",
    master.time_end AS "TIME_END",
    master.time_begin AS "MAX_TIME_BEGIN"
  FROM ssp_car_flow_master_plan master
  WHERE master.time_begin = (SELECT max(time_begin) FROM ssp_car_flow_master_plan)

To fix the query as presented:

  SELECT
    master.id AS "ID",
    master.imitation_mode AS "IMITATION_MODE",
    master.time_begin AS "TIME_BEGIN",
    master.time_end AS "TIME_END",
    max(master.time_begin) AS "MAX_TIME_BEGIN"
  FROM ssp_car_flow_master_plan master
  INNER JOIN (SELECT max(time_begin) AS mt FROM ssp_car_flow_master_plan) maxtime
  ON master.time_begin = maxtime.mt
  GROUP BY imitation_mode, time_begin, time_end, id

but as noted, it's a bit of a nonsense to inner join to a single value, then group everything else and aggregate the thing that can only be a single value