oracle 10g 升级到oracle 11g sql报错，跪求大神解答

Question

SELECT (SELECT shortname FROM LDCom WHERE ComCode = SUBSTR(a.appcom, 0, 4)),
       '办公室',
       CASE
         WHEN EXISTS (SELECT 1
                 FROM lzcertifyfee
                WHERE inputcom = SUBSTR(a.appcom, 0, 4)
                  AND apportionflag = '1'
                  and inputdate between to_date('2012-12-01') and
                      to_date('2013-01-31')
                  AND ROWNUM = 1) THEN
          '已分摊'
         ELSE
          '未分摊'
       END,
       sum(a.retsumcount),
       to_char(sum(a.appsumprice), '999999999990.99') sumprice,
       SUBSTR(a.appcom, 0, 4),
       '',
       '2012-12-01',
       '2013-01-31'
  FROM lzcardplan a
 WHERE 1 = 1
   AND ((A.RETTYPE = '2' AND A.RETCOM = '86') OR
       (A.Rettype = '6' AND a.standbyflag2 = '1'))
   and exists
 (select 1
          from lzcardplansub b
         where b.planid = a.planid
           and exists (select 1
                  from lmcertifydes
                 where certifycode = b.certifycode
                   and certifyversion = b.certifyversion
                   and usetype = '0'
                   and certifyclass = '1'))
   and appcom like '86%'
   and a.RetDate >= '2012-12-01'
   and a.RetDate <= '2013-01-31'
 group by SUBSTR(a.appcom, 0, 4)
在oracle 10g中能顺利执行，但是用11g就会报错，提示SUBSTR(a.appcom, 0, 4)里面的a.appcom不是GROUP BY 表达式，这是怎么回事，很奇怪的问题oraclesql不是GROUPBY表达式 升级11g报错 

Answer 1

select SUBSTR(a.appcom, 0, 4),其它不变
....
group by 1试试看

Answer 2

### Error ExeSQL at Encode2: SELECT (SELECT shortname FROM LDCom WHERE ComCode = SUBSTR(a.appcom,0,4)),'办公室', CASE WHEN EXISTS (SELECT 1 FROM lzcertifyfee WHERE inputcom=SUBSTR(a.appcom,0,4) AND apportionflag='1' and inputdate between to_date('2012-12-01') and to_date('2013-01-31') AND ROWNUM=1) THEN '已分摊' ELSE '未分摊' END, sum(a.retsumcount) , to_char(sum(a.appsumprice), '999999999990.99') sumprice, SUBSTR(a.appcom,0,4),'','2012-12-01','2013-01-31' FROM lzcardplan a  WHERE 1=1     AND ((A.RETTYPE = '2' AND A.RETCOM = '86') OR (A.Rettype = '6' AND a.standbyflag2 = '1'))  and exists (select 1 from lzcardplansub b where b.planid=a.planid and exists (select 1 from lmcertifydes where certifycode= b.certifycode and certifyversion=b.certifyversion and usetype='0' and certifyclass='1' ))  and appcom like '86%'  and a.RetDate>='2012-12-01'  and a.RetDate<='2013-01-31' group by SUBSTR(a.appcom,0,4)
java.sql.SQLException: ORA-00979: 不是 GROUP BY 表达式 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:796)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1031)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:836)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1124)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
at com.sinosoft.utility.ExeSQL.getEncodedResult(ExeSQL.java:198)
at com.sinosoft.lis.pubfun.EasyQueryBL.queryData(EasyQueryBL.java:124)
at com.sinosoft.lis.pubfun.EasyQueryBL.submitData(EasyQueryBL.java:73)
at com.sinosoft.lis.pubfun.EasyQueryUI.submitData(EasyQueryUI.java:58)
at org.apache.jsp.common.easyQueryVer3.EasyQueryXML_jsp.easyQueryKernel(EasyQueryXML_jsp.java:68)
at org.apache.jsp.common.easyQueryVer3.EasyQueryXML_jsp._jspService(EasyQueryXML_jsp.java:363)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:331)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:34)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:874)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Thread.java:595)在（ExeSQL）中抛出如下错误：java.sql.SQLException: ORA-00979: 不是 GROUP BY 表达式这就是报错信息了