17,086
社区成员
发帖
与我相关
我的任务
分享SQL执行时报标识符无效
21-Sep-2016 10:33:08.782 SEVERE [http-nio-80-exec-10] org.apache.catalina.core.StandardWrapperValve.invoke Servlet.service() for servlet [spring] in context with path [] threw exception [Request processing failed; nested exception is com.github.ltsopensource.store.jdbc.exception.JdbcException: Select SQL Error:
SELECT TIMESTAMP ,AVG(DAEMON_THREAD_COUNT) AS DAEMON_THREAD_COUNT ,AVG(THREAD_COUNT) AS THREAD_COUNT ,AVG(TOTAL_STARTED_THREAD_COUNT) AS TOTAL_STARTED_THREAD_COUNT ,AVG(DEAD_LOCKED_THREAD_COUNT) AS DEAD_LOCKED_THREAD_COUNT ,AVG(PROCESS_CPU_TIME_RATE) AS PROCESS_CPU_TIME_RATE FROM LTS_ADMIN_JVM_THREAD WHERE (TIMESTAMP BETWEEN ? AND ? ) GROUP BY TIMESTAMP ] with root cause
java.sql.SQLException: ORA-00904: "TIMESTAMPETWEEN": 标识符无效
Query: SELECT TIMESTAMP ,AVG(DAEMON_THREAD_COUNT) AS DAEMON_THREAD_COUNT ,AVG(THREAD_COUNT) AS THREAD_COUNT ,AVG(TOTAL_STARTED_THREAD_COUNT) AS TOTAL_STARTED_THREAD_COUNT ,AVG(DEAD_LOCKED_THREAD_COUNT) AS DEAD_LOCKED_THREAD_COUNT ,AVG(PROCESS_CPU_TIME_RATE) AS PROCESS_CPU_TIME_RATE FROM LTS_ADMIN_JVM_THREAD WHERE (TIMESTAMP BETWEEN ? AND ? ) GROUP BY TIMESTAMP Parameters: [1474414388000, 1474425188000]
打印出来的SQL是正确的，但不知道怎么报 "TIMESTAMPETWEEN": 标识符无效，我看全部SQL也只有 (TIMESTAMP BETWEEN ? AND ? 才有可能拼接成上面错误的SQL样子，但明明是有空格的 打了3-4个空格。 求解啊，调试到执行时 SQL都是正确的。 但报错却拼接在一起了
SELECT TIMESTAMP ,AVG(DAEMON_THREAD_COUNT) AS DAEMON_THREAD_COUNT ,AVG(THREAD_COUNT) AS THREAD_COUNT ,AVG(TOTAL_STARTED_THREAD_COUNT) AS TOTAL_STARTED_THREAD_COUNT ,AVG(DEAD_LOCKED_THREAD_COUNT) AS DEAD_LOCKED_THREAD_COUNT ,AVG(PROCESS_CPU_TIME_RATE) AS PROCESS_CPU_TIME_RATE FROM LTS_ADMIN_JVM_THREAD WHERE (TIMESTAMP BETWEEN ? AND ? ) GROUP BY TIMESTAMP ] with root cause
java.sql.SQLException: ORA-00904: "TIMESTAMPETWEEN": 标识符无效
Query: SELECT TIMESTAMP ,AVG(DAEMON_THREAD_COUNT) AS DAEMON_THREAD_COUNT ,AVG(THREAD_COUNT) AS THREAD_COUNT ,AVG(TOTAL_STARTED_THREAD_COUNT) AS TOTAL_STARTED_THREAD_COUNT ,AVG(DEAD_LOCKED_THREAD_COUNT) AS DEAD_LOCKED_THREAD_COUNT ,AVG(PROCESS_CPU_TIME_RATE) AS PROCESS_CPU_TIME_RATE FROM LTS_ADMIN_JVM_THREAD WHERE (TIMESTAMP BETWEEN ? AND ? ) GROUP BY TIMESTAMP Parameters: [1474414388000, 1474425188000]
打印出来的SQL是正确的，但不知道怎么报 "TIMESTAMPETWEEN": 标识符无效，我看全部SQL也只有 (TIMESTAMP BETWEEN ? AND ? 才有可能拼接成上面错误的SQL样子，但明明是有空格的 打了3-4个空格。 求解啊，调试到执行时 SQL都是正确的。 但报错却拼接在一起了
...全文
请发表友善的回复…
发表回复
清山云上 2016-09-21
- 打赏
- 举报
已解决
oracle函数中有不足位数补空格的函数
LPAD和RPAD分别是左补位和右补位
repalce(lpad(字段名，共几位)，‘ ’，‘0’)
如运行
select replace(lpad('123',5),' ','0') from dual
结果是00123
即补足五位
清山云上 2016-09-21
- 打赏
- 举报
你是用 mybatis 这类框架拼接出来的 SQL 吗？[/quote] 不是，我调试到执行SQL底层时候 SQL语句都是对的，没有空格
卖水果的net 2016-09-21
- 打赏
- 举报
你是用 mybatis 这类框架拼接出来的 SQL 吗？
清山云上 2016-09-21
- 打赏
- 举报
我把表名加上 就报 java.sql.SQLException: ORA-00904: "LTS_ADMIN_JVM_THREAD"."TIMESTAMPETWEEN": 标识符无效 ， 就是WHERE (TIMESTAMP BETWEEN 两个单词拼接上了BETWEEN B都没有了
清山云上 2016-09-21
- 打赏
- 举报
我把表名加上 就报 java.sql.SQLException: ORA-00904: "LTS_ADMIN_JVM_THREAD"."TIMESTAMPETWEEN": 标识符无效 ， 就是WHERE (TIMESTAMP BETWEEN 两个单词拼接上了BETWEEN B都没有了
卖水果的net 2016-09-21
- 打赏
- 举报
TIMESTAMP 是系统 保留的关键字，你就不要用这个做列名了。
不过，你这里先在这个列名加表名（或表别名）
ghx287524027 2016-09-21
- 打赏
- 举报
贴一下你的源代码
