3.8K Star 15.2K Fork 5K

卓源软件 / JeeSite 4.4 and 5.0

 / 详情

sqlServer 中 count 自动加上括号了,导致报错

Backlog
Opened this issue  
2022-07-09 15:24

sqlServer 中 count 自动加上括号了,导致报错
sqlServer 2014
jeesite 5.x
jdk1.8

07-09 15:20:58.689 DEBUG [c.j.c.m.m.provider.SelectSqlProvider   ] - 1毫秒: SELECT a.device_code AS "deviceCode", a.device_name AS "deviceName", a.region_code AS "region.regionCode", a.model_code AS "model.modelCode", a.leader AS "leader", a.status AS "status", a.create_by AS "createBy", a.create_date AS "createDate", a.update_by AS "updateBy", a.update_date AS "updateDate", a.remarks AS "remarks", r.region_name AS "region.regionName", m.model_name AS "model.modelName", m.advance AS "model.advance" FROM energy_device a LEFT JOIN energy_region r ON r.region_code = a.region_code LEFT JOIN energy_device_model m ON m.model_code = a.model_code WHERE a.status != #{STATUS_DELETE} AND a.region_code IN ( #{sqlMap.where.region_code#IN1.val[0]} ) ORDER BY a.device_code
07-09 15:20:58.753 DEBUG [c.j.m.energy.dao.DeviceDao.findList    ] - ==>  Preparing: select count(0) from (SELECT a.device_code AS "deviceCode", a.device_name AS "deviceName", a.region_code AS "region.regionCode", a.model_code AS "model.modelCode", a.leader AS "leader", a.status AS "status", a.create_by AS "createBy", a.create_date AS "createDate", a.update_by AS "updateBy", a.update_date AS "updateDate", a.remarks AS "remarks", r.region_name AS "region.regionName", m.model_name AS "model.modelName", m.advance AS "model.advance" FROM energy_device a LEFT JOIN energy_region r ON r.region_code = a.region_code LEFT JOIN energy_device_model m ON m.model_code = a.model_code WHERE a.status != ? AND a.region_code IN ( ? ) ORDER BY a.device_code) tmp_count
07-09 15:20:58.761 DEBUG [c.j.m.energy.dao.DeviceDao.findList    ] - ==> Parameters: 1(String), sdfsdf(String)
07-09 15:20:58.767 DEBUG [o.s.j.s.SQLErrorCodeSQLExceptionTranslator] - Unable to translate SQLException with Error code '1033', will now try the fallback translator
07-09 15:20:58.779 DEBUG [c.j.m.sys.interceptor.LogInterceptor   ] - 计时结束: 03:20:58.777  用时: 126毫秒  URI: /js/a/energy/device/listData  总内存: 1.211GB  已用内存: 480.926MB
07-09 15:20:58.781 ERROR [o.a.c.c.C.[.[.[/js].[dispatcherServlet]] - Servlet.service() for servlet [dispatcherServlet] in context with path [/js] threw exception [Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: 
### Error querying database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 除非另外还指定了 TOP、OFFSET 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
### The error may exist in com/jeesite/modules/energy/dao/DeviceDao.java (best guess)
### The error may involve com.jeesite.modules.energy.dao.DeviceDao.findList-Inline
### The error occurred while setting parameters
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 除非另外还指定了 TOP、OFFSET 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
; uncategorized SQLException; SQL state [S0001]; error code [1033]; 除非另外还指定了 TOP、OFFSET 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 除非另外还指定了 TOP、OFFSET 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。] with root cause

com.microsoft.sqlserver.jdbc.SQLServerException: 除非另外还指定了 TOP、OFFSET 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:602)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3272)

Comments (6)

Oliver created任务
Oliver changed description
Expand operation logs

count 是分页,错误信息应该是 order by的问题
你的 jdbc.type 设置的是 mssql2012 吗

好多地方都出这个问题啊

有什么办法 把order by 去掉

我看了你的sql中有关键词 “advance” 导致 SqlParser 失败,吧 advance 字段名换一个名字就可以了

的确是,感谢

Sign in to comment

Status
Assignees
Milestones
Pull Requests
Successfully merging a pull request will close this issue.
Branches
Planed to start   -   Planed to end
-
Top level
Priority
参与者(2)
6732 thinkgem 1651893329 2017721 oliver225 1652769646
Java
1
https://gitee.com/thinkgem/jeesite4.git
git@gitee.com:thinkgem/jeesite4.git
thinkgem
jeesite4
JeeSite 4.4 and 5.0

Search