报错
org.springframework.jdbc.UncategorizedSQLException:
### Error querying database. Cause: java.sql.SQLException: An identifier or literal is invalid
### The error may exist in file [/app/domain/user_projects/domains/base_domain/servers/VC/stage/visionlite/visionlite/WEB-INF/classes/com/worklite/service/statedata/resource/statedata.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select NVL(SUM(answer-"start"), 0) from call_state_info where answer is not null and instid=?
### Cause: java.sql.SQLException: An identifier or literal is invalid
; uncategorized SQLException for SQL []; SQL state [99999]; error code [17133]; An identifier or literal is invalid; nested exception is java.sql.SQLException: An identifier or literal is invalid
sql语句单独拿出来执行不报错;但是项目一运行就会报错;
mybatis xml文件
<select id="getCustomWaitTime" resultType="long" databaseId="oracle">
select NVL(SUM(answer-"start"), 0) from call_state_info where answer is
not null
<if test="null!=instid and ''!=instid">and instid=#{instid}</if>
<if test="null!=custid and ''!=custid">and custid=#{custid}</if>
</select>
解决:给NVL(SUM(answer-"start"), 0)取别名;
<select id="getCustomWaitTime" resultType="long" databaseId="oracle">
select NVL(SUM(answer-"start"), 0) as waitTime from call_state_info where answer is
not null
<if test="null!=instid and ''!=instid">and instid=#{instid}</if>
<if test="null!=custid and ''!=custid">and custid=#{custid}</if>
</select>
错误原因好像是因为sum()函数计算的结果为null,但是NVL函数明明会处理这种情况;所以我也不知道为什么加了个别名就不报错了;