我正在使用 Ruby 與資料庫進行通信，并且我有一個顯示設備統計資訊的統計資訊頁面。不幸的是，當執行選擇查詢以顯示頁面時，我收到以下錯誤：
錯誤 -- : 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"
缺少什么表達？我好像錯過了什么。
uj5u.com熱心網友回復：
查詢有兩個問題：
- 替換
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 上的運行示例。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/349019.html
下一篇：如何在PLSQL中進行條件檢查