ORA-00936:在执行查询时缺少表达式

Question

我使用Ruby与数据库进行通信，我有一个统计页面，其中显示设备的统计信息。不幸的是，当执行select查询以显示页面时，我得到了以下错误：

错误- StatementInvalid: Java::JavaSql::SQLSyntaxErrorException: ORA-00936:缺失表达式

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"

少了什么表情？我好像错过了什么。

Answer 1

查询有两个问题：

• 将date()替换为trunc()。
• 要使用时间戳文字，请使用to_date() (或timestamp 'literal')。

例如，您的查询可以以以下方式运行：

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"

请参见在db<>fiddle上运行示例。