oracle union联合查询排序方式
来源:互联网 发布:网络受限 编辑:程序博客网 时间:2024/06/06 10:40
在用union联合查询排序时遇到了以下异常:
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select a.table_name as tableName,a.flow_name as flowName, a.action_name as actionName,a.field_name as fieldName,a.field_title as fieldTitle, a.field_right as fieldRight,a.action_right_guid as guid,a.table_title as tableTitle from fwt_action_right a where a.table_name=? and flow_name=? and action_name=? union select b.table_name as tableName,'' as flowName,'' as actionName, b.field_name as fieldName,b.field_title as fieldTitle,'' as fieldRight,'' as guid,'' as tableTitle from adam_field_info b where b.table_name=? and b.field_name not in (select field_name from fwt_action_right a where table_name=? and flow_name=? and action_name=? ) order by a.fieldName asc ]; nested exception is java.sql.SQLException: ORA-00904: "A"."FIELDNAME": 标识符无效at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:276)at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:607)at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:641)at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:670)at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:678)at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:710)at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:776)at adam.bp.workflow.dao.engine.FlowRightConfigDao.getFwtActionRights(FlowRightConfigDao.java:85)at adam.bp.workflow.dao.engine.FlowRightConfigDao$$FastClassByCGLIB$$431cc304.invoke(<generated>)at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:700)at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:635)at adam.bp.workflow.dao.engine.FlowRightConfigDao$$EnhancerByCGLIB$$667558de.getFwtActionRights(<generated>)at adam.bp.workflow.biz.engine.FlowRightConfigBiz.getFwtActionRights(FlowRightConfigBiz.java:38)at adam.bp.workflow.action.engine.FlowRightConfigAction.showFlowRightConfigPage(FlowRightConfigAction.java:79)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)at java.lang.reflect.Method.invoke(Method.java:324)at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:269)at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:170)at org.apache.struts.actions.MappingDispatchAction.execute(MappingDispatchAction.java:166)at org.springframework.web.struts.DelegatingActionProxy.execute(DelegatingActionProxy.java:110)at org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:58)at org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:67)at org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:51)at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:191)at org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:305)at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:191)at org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:283)at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:449)at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)at adam.bp.workflow.filter.LoginFilter.doFilter(LoginFilter.java:66)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)at com.adam.dev.utils.EncoderFilter.doFilter(EncoderFilter.java:37)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)at java.lang.Thread.run(Thread.java:534)Caused by: java.sql.SQLException: ORA-00904: "A"."FIELDNAME": 标识符无效at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:830)at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2391)at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672)at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527)at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:648)at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
解决方式:
修改sql的排序字段:
1. 建议大家都给联合查询出来相对应的列取相同的别名,这样便可以通过别名进行排序
String sql = "select a.table_name as tableName,a.flow_name as flowName," + " a.action_name as actionName,a.field_name as fieldName,a.field_title as fieldTitle," + " a.field_right as fieldRight,a.action_right_guid as guid,a.table_title as tableTitle " + " from fwt_action_right a " + " where a.table_name=? " + " and flow_name=? " + " and action_name=? " + " union " + " select b.table_name as tableName,'' as flowName,'' as actionName," + " b.field_name as fieldName,b.field_title as fieldTitle,'' as fieldRight,'' as guid,'' as tableTitle " + " from adam_field_info b " + " where b.table_name=? " + " and b.field_name not in " + " (select field_name from fwt_action_right a where table_name=? and flow_name=? and action_name=? )" +" order by fieldName asc ";//利用列别名排序
2. 通过列的序号排序
String sql = "select a.table_name as tableName,a.flow_name as flowName," + " a.action_name as actionName,a.field_name as fieldName,a.field_title as fieldTitle," + " a.field_right as fieldRight,a.action_right_guid as guid,a.table_title as tableTitle " + " from fwt_action_right a " + " where a.table_name=? " + " and flow_name=? " + " and action_name=? " + " union " + " select b.table_name as tableName,'' as flowName,'' as actionName," + " b.field_name as fieldName,b.field_title as fieldTitle,'' as fieldRight,'' as guid,'' as tableTitle " + " from adam_field_info b " + " where b.table_name=? " + " and b.field_name not in " + " (select field_name from fwt_action_right a where table_name=? and flow_name=? and action_name=? )" + " order by 5 asc ";//union情况下利用列号排序
- oracle union联合查询排序方式
- union 联合查询结果
- 联合查询Union
- mysql UNION联合查询
- sqlserver Union联合查询
- UNION 联合查询
- union(联合)合并查询结果
- Mysql之union联合查询
- 连接查询和union联合
- Oracle SQL基础之(二) [联合查询、union、子查询、外、内连接、复制表]
- 关于 联合查询 UNION 和Order by
- 查询语句之Union联合结果集
- hibernate映射文件不支持union联合查询
- 联合查询Union,Intersect,Except用法
- UNION联合多表查询代码举例
- sql联合查询 JOIN和UNION区别
- Mysql Union 联合查询的使用
- sql语句之UNION,联合查询
- 紫薇花开的季节(二)
- 让你认清楚JSP中的所有东西(java/JSP/EL/OGNL/JSTL/c标签/s标签/HTML/javascript/CSS)
- Poj 1014 Dividing
- linux su和sudo命令的区别
- 美国不道德的人体实验
- oracle union联合查询排序方式
- Debug Log没有生成?
- ASP.NET4:禁止模板控件生成外层表格元素
- Android按键消息传播流程(WindowManagerService.java)
- Linux脚本编写基础
- android之service
- 回归自然
- Android 4.0 用户输入子系统
- 升级Oracle BIEE 11.1.1.6.0到 11.1.1.6.2 BP1相关文章