英文:
ORA-00933: SQL command not properly ended.Im executing this query in JAVA
问题
出现以下错误消息，我不知道为什么：
ORA-00933: SQL命令未正确结束。我在JAVA中执行此查询：
String query2 = "SELECT dock_id " +
"FROM ( " +
"SELECT inventory_location_id AS dock_id " +
"FROM mtl_item_locations " +
"WHERE organization_id = 402 " +
"AND subinventory_code = 'me' " +
"AND inventory_location_type = 1 " +
"AND NOT EXISTS ( " +
"SELECT 1 " +
"FROM wms_dock_appointments_v " +
"WHERE dock_id = inventory_location_id " +
"AND ( start_time BETWEEN '30/05/2023' AND '05/06/2023' " +
" OR end_time BETWEEN '30/05/2023' AND '05/06/2023' " +
" OR '30/05/2023' BETWEEN start_time AND end_time " +
" OR '30/05/2023' BETWEEN start_time AND end_time " +
" )) " +
"ORDER BY inventory_location_id " +
") " +
"WHERE rownum = 1;";
PreparedStatement stmt2 = connection.prepareStatement(query2);
ResultSet rs2 = stmt2.executeQuery();
这个查询本身在SQL DEVELOPER中可以正常工作，但在我的IDE中执行时会崩溃。
问题在哪里？
已经尝试了很多次，但没有成功。
英文:
Getting this error message and i don't know why:
ORA-00933: SQL command not properly ended.Im executing this query in JAVA:
String query2 = "SELECT dock_id " +
"FROM ( " +
"SELECT inventory_location_id AS dock_id " +
"FROM mtl_item_locations " +
"WHERE organization_id = 402 " +
"AND subinventory_code = 'me' " +
"AND inventory_location_type = 1 " +
"AND NOT EXISTS ( " +
"SELECT 1 " +
"FROM wms_dock_appointments_v " +
"WHERE dock_id = inventory_location_id " +
"AND ( start_time BETWEEN '30/05/2023' AND '05/06/2023' " +
" OR end_time BETWEEN '30/05/2023' AND '05/06/2023' " +
" OR '30/05/2023' BETWEEN start_time AND end_time " +
" OR '30/05/2023' BETWEEN start_time AND end_time " +
" )) " +
"ORDER BY inventory_location_id " +
") " +
"WHERE rownum = 1;";
PreparedStatement stmt2 = connection.prepareStatement(query2);
ResultSet rs2 = stmt2.executeQuery();
The query by itself works in SQL DEVELOPER but it crashes when doin it in my IDE
Whats wrong here?
Tried so many times without success
答案1
得分: 1
重新格式化以便更好地分析：
SELECT dock_id FROM
(
SELECT inventory_location_id AS dock_id FROM mtl_item_locations
WHERE organization_id = 402 AND subinventory_code = 'me' AND inventory_location_type = 1 AND
NOT EXISTS
(
SELECT 1 FROM wms_dock_appointments_v
WHERE dock_id = inventory_location_id AND
(
start_time BETWEEN '30/05/2023' AND '05/06/2023' OR
end_time BETWEEN '30/05/2023' AND '05/06/2023' OR
'30/05/2023' BETWEEN start_time AND end_time OR
'30/05/2023' BETWEEN start_time AND end_time
)
)
ORDER BY inventory_location_id
)
WHERE rownum = 1
去掉分号是个好主意。如果你的数据库管理系统允许的话，为子查询命名可能也会有所帮助。
因为 SELECT 1 FROM wms_dock_appointments_v 的目的不太清楚，例如：
在 wms_dock_appointments_v 中可以找到哪些字段？
dock_id，inventory_location_id？
英文:
Reformat it to analyze it better:
SELECT dock_id FROM
(
SELECT inventory_location_id AS dock_id FROM mtl_item_locations
WHERE organization_id = 402 AND subinventory_code ='me' AND inventory_location_type = 1 AND
NOT EXISTS
(
SELECT 1 FROM wms_dock_appointments_v
WHERE dock_id = inventory_location_id AND
(
start_time BETWEEN '30/05/2023' AND '05/06/2023' OR
end_time BETWEEN '30/05/2023' AND '05/06/2023' OR
'30/05/2023' BETWEEN start_time AND end_time OR
'30/05/2023' BETWEEN start_time AND end_time
)
)
ORDER BY inventory_location_id
)
WHERE rownum = 1
Removing the ; is a good idea.
name the subqueries also may help you, if your dbms allow it.
Because the purpose of SELECT 1 FROM wms_dock_appointments_v isn't
clear, for example:
what fields can be found in wms_dock_appointments_v?
dock_id, inventory_location_id?
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论