NutzCN Logo
问答 sql问题
发布于 3108天前 作者 qq_405F509C60D3C6DDAC9368663FF847B5 2340 次浏览 复制 上一个帖子 下一个帖子
标签: dao

遇到一个问题我的sql语句在plsql上能执行,但是在项目中不能执行,而且将这个sql加上pager分页查询可以执行,但是全查询就是无效的列索引,这是怎么回事

18 回复

起码来个sql和代码吧?

你把错误截出来呀

at org.nutz.dao.sql.SqlImpl.execute(SqlImpl.java:127)
at org.nutz.dao.impl.DefaultDaoExecutor$1.invoke(DefaultDaoExecutor.java:33)
at com.gmcc.bi.dao.nutz.SpringDaoRunner.run(SpringDaoRunner.java:30)
at org.nutz.dao.impl.DefaultDaoExecutor.execute(DefaultDaoExecutor.java:20)
at org.nutz.dao.impl.NutDao.execute(NutDao.java:229)
at com.gmcc.bi.dao.nutz.SqlNutzDao.queryWithSql(SqlNutzDao.java:77)
at com.unicom.portal.dao.reportManage.impl.ReportDaoImpl.getProjectList(ReportDaoImpl.java:354)
at com.unicom.portal.service.reportManage.impl.ReportManageServiceImpl.getProjectList(ReportManageServiceImpl.java:77)
at com.unicom.portal.service.reportManage.impl.ReportManageServiceImpl$$FastClassByCGLIB$$c2af8c78.invoke()
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)
at com.unicom.portal.service.reportManage.impl.ReportManageServiceImpl$$EnhancerByCGLIB$$78cd1add.getProjectList()
at com.unicom.portal.controller.reportManage.ReportController.getProjectList(ReportController.java:267)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:427)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:415)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:788)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:717)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: 无效的列索引
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:207)
at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical(OraclePreparedStatement.java:4288)
at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:4280)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)
at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)
at oracle.jdbc.internal.OraclePreparedStatement$$EnhancerByProxool$$416d14d7.setNull()
at org.nutz.dao.sql.FieldAdapter$AsNull.set(FieldAdapter.java:104)
at org.nutz.dao.sql.DefaultStatementAdapter.processWithoutEntity(DefaultStatementAdapter.java:29)
at org.nutz.dao.sql.DefaultStatementAdapter.process(DefaultStatementAdapter.java:18)
at org.nutz.dao.sql.SqlImpl.execute(SqlImpl.java:74)
... 45 more

with TEMP
as--项目中费用已申请金额(不包括冲账)
(SELECT A2.project_id, SUM(A3.GOODS_APPLY_MONEY) AS SUM_FEE
FROM T_OA_PRO_FEE_APPLICATION A2, T_OA_PRO_FEE_APP_DETAILED A3
WHERE --A2.PROJECT_ID =?
A2.PAY_MODE <> 'offset'
AND A2.TYPE IN ('Y', 'T') --提交和通过的费用申请
AND A2.FEE_APPLICATION_ID = A3.FEE_APPLICATION_ID
GROUP BY A2.project_id),--项目中借款已申请金额
TEMP1
as
(SELECT B2.PROJECT_ID, SUM(B3.goods_apply_money) AS SUM_LEND
FROM T_OA_PRO_LEND_APPLICATION B2, T_OA_PRO_LEND_APP_DETAILED B3
WHERE --B2.PROJECT_ID =?
B2.TYPE IN ('Y', 'T') --提交和通过的费用申请'Y',
AND B2.LEND_APPLICATION_ID = B3.LEND_APPLICATION_ID
GROUP BY B2.PROJECT_ID),--项目中费用冲账金额
TEMP2
as
(SELECT A2.PROJECT_ID, SUM(A3.GOODS_APPLY_MONEY) AS SUM_CHONG
FROM T_OA_PRO_FEE_APPLICATION A2, T_OA_PRO_FEE_APP_DETAILED A3
WHERE --A2.PROJECT_ID =?
A2.PAY_MODE = 'offset' -- 冲账
AND A2.TYPE IN ('Y', 'T') --提交和通过的费用申请'Y',
AND A2.FEE_APPLICATION_ID = A3.FEE_APPLICATION_ID
GROUP BY A2.PROJECT_ID),--项目中费用冲账应报金额
TEMP3
as
(SELECT A2.PROJECT_ID, SUM(A3.BAO_MONEY) AS SUM_BAO
FROM T_OA_PRO_FEE_APPLICATION A2, T_OA_PRO_FEE_APP_DETAILED A3
WHERE--A2.PROJECT_ID =?
A2.PAY_MODE = 'offset' -- 冲账
AND A2.TYPE IN ('Y', 'T') --提交和通过的费用申请'Y',
AND A2.FEE_APPLICATION_ID = A3.FEE_APPLICATION_ID
GROUP BY A2.PROJECT_ID),--项目中费用冲账应退金额
TEMP4
as
(SELECT A2.PROJECT_ID, SUM(A3.TUI_LEND) AS SUM_TUI
FROM T_OA_PRO_FEE_APPLICATION A2, T_OA_PRO_FEE_APP_DETAILED A3
WHERE--A2.PROJECT_ID =?
A2.PAY_MODE = 'offset' -- 冲账
AND A2.TYPE IN ('Y', 'T') --提交和通过的费用申请'Y',
AND A2.FEE_APPLICATION_ID = A3.FEE_APPLICATION_ID
GROUP BY A2.PROJECT_ID),

TEMP5
as
(SELECT --预算报表信息
TPG.PROJECT_CARD_ID,TBP.PROJECT_NAME,
TBP.CLIENT_NAME, TBP.EXECUTION_CITY,
TGT.type_name,TBP.belong_to_company,
TPG.goods_name,TPG.goods_desc,
TBG.price_balance_desc,TS.sup_name,
TBG.budget_price,--预算单价
TBG.budget_count,
TBG.budget_cc,TBG.budget_amount,
--预算决算的信息
TPFG.goods_count,--决算数量
TPFG.unit_price,--决算单价
TPFG.screenings,TPFG.goods_apply_money,--决算商品的总价
--报价信息
TBP.project_budget --temp中项目预算决算
from t_oa_budget_goods TBG,t_oa_project_of_goods TPG,
t_oa_build_project TBP,t_oa_goods TG,
t_oa_goods_type TGT,t_oa_supplier TS,
t_oa_pro_final_goods TPFG,t_oa_pro_final_accounts TPFA
where tbg.g_id = tpg.g_id(+) and ts.sup_no(+) = tbg.sup_no
and tbg.g_id(+) = tpfg.goods_id --项目预算决算 右边包括全部完成的 ,左边是包括没完成和完成的
and tpg.goods_id = tg.g_no(+) and tg.g_type_id = tgt.id(+)
and tpfg.final_accounts_id(+) = tpfa.final_accounts_id
and tpg.project_card_id = tbp.project_card_id(+) and tpg.project_card_id is not null
and tpfa.final_type ='1' and tbp.pro_state='T'
order by tpg.project_card_id)

select
TEMP5.project_card_id as projectCardId,
TEMP5.project_name as projectName,
TEMP5.client_name as clientName,
TEMP5.execution_city as city,
TEMP5.type_name as goodsType,
TEMP5.belong_to_company as belongCompany,
TEMP5.goods_name as goodsName,
TEMP5.goods_desc as budgetDesc,
TEMP5.price_balance_desc as balanceDesc,
TEMP5.sup_name as supName,
TEMP5.budget_price as budgetPrice,--预算单价
TEMP5.budget_count as budgetCount,
TEMP5.budget_cc as budgetCC,
TEMP5.budget_amount as budgetAmount,
--预算决算的信息
TEMP5.goods_count as goodsCount,--决算数量
TEMP5.unit_price as unitPrice,--决算单价
TEMP5.screenings as screenings,
TEMP5.goods_apply_money as goodsApplyMoney,--决算商品的总价
--报价信息
TEMP5.project_budget as projectBudget,--temp中项目预算决算
case when TEMP.SUM_FEE is null then 0 when TEMP.SUM_FEE='' then 0 else TEMP.SUM_FEE end as sum_FEE,
case when TEMP1.SUM_LEND is null then 0 when TEMP1.SUM_LEND='' then 0 else TEMP1.SUM_LEND end as projectLend,
case when TEMP2.SUM_CHONG is null then 0 when TEMP2.SUM_CHONG='' then 0 else TEMP2.SUM_CHONG end as projectOffset,
case when TEMP3.SUM_BAO is null then 0 when TEMP3.SUM_BAO='' then 0 else TEMP3.SUM_BAO end as sum_BAO,
case when TEMP4.SUM_TUI is null then 0 when TEMP4.SUM_TUI='' then 0 else TEMP4.SUM_TUI end as sum_TUI,
case when sum_FEE + sum_BAO - sum_TUI is null then 0 when SUM_FEE + SUM_BAO - SUM_TUI='' then 0 else sum_FEE + sum_BAO - sum_TUI end as projectFee
from temp,temp1,temp2,temp3,temp4,temp5
WHERE TEMP.PROJECT_ID = TEMP1.PROJECT_ID(+)
AND TEMP1.PROJECT_ID = TEMP2.PROJECT_ID(+)
AND TEMP2.PROJECT_ID = TEMP3.PROJECT_ID(+)
AND TEMP3.PROJECT_ID = TEMP4.PROJECT_ID(+)
AND TEMP.PROJECT_ID(+) = TEMP5.PROJECT_CARD_ID

你贴的sql是日志里面打印出来的吧?原始sql 是啥?

另外,这么复杂的sql,得自行删减,逐渐缩小范围

没办法,这些数据都得一次性读出来,不然就更麻烦了

另外, 分页的话,会在最外层加select ,看这sql是with 开头,所以需要调用一下sql.forceQuery,也许这就是原因

这是查sql怎么报错,跟业务无关,要逐渐缩小问题的范围和可能性啊

额,之前的count能执行?那看来在外面加一个select * 应该就好了

分页是可以查询出来的,但是不加分页就完蛋了

所以我觉得就是缺个select *

把优化后的sql贴一下呗

添加回复
请先登陆
回到顶部