`
Java_Fan
  • 浏览: 81715 次
  • 性别: Icon_minigender_1
  • 来自: 西安
文章分类
社区版块
存档分类
最新评论

oracle union联合查询排序方式

 
阅读更多

在用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情况下利用列号排序

分享到：
| jQuery.post() 方法用法
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    数据库开发 Oracle数据库 SQL开发教程 第08章 集合运算（共20页）.pdf

    联合后的查询不能包含各自的`ORDER BY`子句，但可以在整个联合之后添加`ORDER BY`来排序结果。例如，查询员工7839的当前工资、岗位以及历史工资和岗位，可以使用如下联合查询： ```sql SELECT sal, job FROM emp ...

    sql语言手册 和联合查询

    ### 联合查询（UNION） #### 基本语法 ```sql SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2; ``` - `UNION`：用于合并两个或多个SELECT语句的结果集。 #### 示例 假设有两个表`...

    Introduction To ORACLE9i:SQL2

    - **联合查询**：使用UNION操作符可以将两个或多个SELECT语句的结果合并为一个结果集。 ```sql SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2; ``` #### 7. 视图与存储...

    oracle运维必要宝典

    该脚本通过联合`dba_tablespaces`视图、`dba_temp_files`视图和`v$temp_extent_pool`视图来获取临时表空间的信息，包括总大小、已使用大小、使用率及状态等，并按使用率降序排序。 ### 日志文件管理 日志文件管理...

    Oracle的性能优化(ppt72页).pptx

    - 排序优化涉及group by、order by、distinct和minus/union操作，选择合适的排序方式可以显著提升性能。 - 联合查询的联合次序选择也会影响性能，合理安排表的联合顺序可以减少不必要的数据处理。 3. **Oracle...

    ORACLE__SQL.zip_oracle

    - 联合查询：学习使用UNION、INTERSECT和EXCEPT操作符来合并多个查询的结果。 - 连接查询：了解INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN的不同用法。 - 子查询：理解如何在查询中嵌套SQL语句以实现更复杂的...

    Oracle报表经典语句

    本文档将深入解析几个经典的Oracle报表SQL语句，这些语句涉及到了Oracle中的高级功能，如窗口函数、联合查询等，并通过实际案例展示了如何构造报表。 #### 二、SQL语句分析 ##### 1. 分类汇总与总计 此段代码展示...

    Oracle 10g培训经典_英文版

    3. **Les04Displaying Data from Multiple Tables.ppt** - 讲解了如何通过JOIN操作从多个表中联合查询数据，包括内连接(INNER JOIN)、左连接(LEFT JOIN)、右连接(RIGHT JOIN)和全连接(FULL JOIN)。 4. **Les08...

    oracle 1z0-047 74題 考古題

    以上解析涵盖了给定文件的部分内容，深入探讨了Oracle数据库中的层次查询、联合查询的排序、相关子查询中EXISTS运算符的用法以及正则表达式的应用，这些都是Oracle 1Z0-047认证考试中可能会遇到的重要知识点。

    oracle课件pdf格式的

    5. **第5章 多表查询**：多表查询是数据库操作的关键，包括JOIN（内连接、外连接）、UNION（并集）、INTERSECT（交集）和EXCEPT（差集）等操作，以及如何使用子查询进行复杂的数据联合。 6. **第6章 分组函数**：...

    试谈Oracle的性能优化.pptx

    2. SQL语句排序优化：在SQL语句中涉及group by、order by、distinct或minus、union操作时，排序操作是不可避免的，应尽量减少不必要的排序，通过索引和预排序数据来提高性能。 3. 联合查询的联合次序：在多表联合...

    sql语法大全oracle文档

    - 联合查询：`SELECT * FROM 表1 UNION SELECT * FROM 表2;` 5. 更新与删除数据： - 更新数据：`UPDATE 表名 SET 列名 = 新值 WHERE 条件;` - 删除数据：`DELETE FROM 表名 WHERE 条件;` 二、Oracle数据库API ...

    oracle sql基础 课件

    5. 联合查询：UNION、UNION ALL用于合并多个SELECT语句的结果。 6. 子查询：嵌套在其他SQL语句中的查询，用于获取满足特定条件的数据。 四、DML语句（INSERT、UPDATE、DELETE） 1. INSERT语句：向表中插入新记录，...

    Oracle SQL学习资料大全.zip

    Oracle SQL是数据库管理员和开发人员在Oracle数据库系统中进行数据查询、管理与分析的主要工具。这份"Oracle SQL学习资料大全.zip"压缩包包含了丰富的学习资源，帮助初学者和进阶者深入理解并掌握Oracle SQL的核心...

    oracle常用sql语法集合与sql语法大全

    - 联合查询：使用UNION、UNION ALL、INTERSECT或EXCEPT操作符合并多个SELECT语句的结果。 二、高级查询 1. 子查询：嵌套SQL查询，用于在一个查询中引用另一个查询的结果。 2. 连接查询：JOIN操作连接两个或更多表...

    MLDN魔乐科技_Oracle课堂4

    此外，还可能涉及如何使用通配符（%和_）、如何联合查询（UNION）不同表的数据、如何使用JOIN连接表以及如何处理NULL值等更进阶的SQL知识。 学习这部分内容，不仅可以帮助你理解和操作Oracle数据库，而且对于其他...

    Oracle性能调优.pptx

    - SQL语句排序优化，对于包含group by、order by、distinct和minus或union操作的语句，合理规划排序能显著提升性能。 - 联合查询的联合次序也会影响性能，应根据表的大小和关联字段的索引情况来安排。 3. Oracle...

Magicbox
Global site tag (gtag.js) - Google Analytics