I am using Ruby to communicate with the database and I have a statistics page which shows stats for devices. Unfortunately I am getting the following error when a select query is executed to show the page:

ERROR -- : StatementInvalid: Java::JavaSql::SQLSyntaxErrorException: ORA-00936: missing expression

SELECT
   COUNT(*) AS count_all,
   date(request_time) AS date_request_time,
   "REQUESTS"."DEVICE_ID" AS requests_device_id 
FROM
   "REQUESTS" 
WHERE
   (
      customer_id = 1 
      AND request_method != 'OPTIONS' 
      AND request_time BETWEEN '2021-10-27 00:00:00' AND '2021-11-03 23:59:59.000999'
   )
   AND 
   (
      device_id IS NOT NULL 
      AND request_time BETWEEN '2021-10-27 00:00:00' AND '2021-11-03 23:59:59.000999'
   )
GROUP BY
   date(request_time),
   "REQUESTS"."DEVICE_ID"

What expression is missing? I seemed to have miss something.

CodePudding user response：

The query has two issues:

• Replace date() with trunc().
• To use timestamp literals use to_date() (or timestamp 'literal').

For example, your query could run as:

SELECT
   COUNT(*) AS count_all,
   trunc(request_time) AS date_request_time,
   "REQUESTS"."DEVICE_ID" AS requests_device_id 
FROM
   "REQUESTS" 
WHERE
   (
      customer_id = 1 
      AND request_method != 'OPTIONS' 
      AND request_time BETWEEN to_date('2021-10-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
                           AND to_date('2021-11-03 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
   )
   AND 
   (
      device_id IS NOT NULL 
      AND request_time BETWEEN to_date('2021-10-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
                           AND to_date('2021-11-03 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
   )
GROUP BY
   trunc(request_time),
   "REQUESTS"."DEVICE_ID"

See running example at db<>fiddle.