Oracle数据库查询表中记录为空,Empty result set fetched

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/charberming/article/details/87346833

Oracle数据库查询表中记录为空,Empty result set fetched,此时没有数据,获取不到任何值,但是此时我们需要根据结果来做计算,因此可以使用count()函数来做结果统计,将统计结果用来做判断。

场景:需要查询出结果用于求和计算,没有结果时取0。
方案:使用case...when做sql的条件判断,当有结果为1时表示有记录,此时使用查询到的记录,当结果是其他时表示没有记录,此时count()统计数据为0,用统计结果做结果返回用于求和运算;

sql如下:

select count(1) from ldsysvar where sysvar='onerow' and ((
select Mult*50 from lspol where contno='?ContNo?' and riskcode='00963000'
)+(
(
case (select count(STANDBYFLAG1*100) from lspol where contno='?ContNo?' and riskcode in ('00958100') )
when 1 then (select STANDBYFLAG1*100 from lspol where contno='?ContNo?' and riskcode in ('00958100'))
else (select count(STANDBYFLAG1*100) from lspol where contno='?ContNo?' and riskcode in ('00958100') )
end
)
))>500 and '?RiskCode?' in ('00963000','00554000');


 

展开阅读全文

帮忙看看?Illegal operation on empty result set什么错误

12-28

org.apache.jasper.JasperException: javax.servlet.ServletException: java.sql.SQLException: Illegal operation on empty result set.rn at org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:500)rn at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:410)rn at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)rn at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)rn at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)rn at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)rn at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)rn at com.wsy.Filter.ModifyCode.doFilter(ModifyCode.java:23)rn at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)rn at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)rn at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)rn at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)rn at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)rn at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)rn at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)rn at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)rn at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)rn at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)rn at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)rn at java.lang.Thread.run(Thread.java:619)rnCaused by: javax.servlet.ServletException: java.sql.SQLException: Illegal operation on empty result set.rn at org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:865)rn at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:794)rn at org.apache.jsp.index_jsp._jspService(index_jsp.java:257)rn at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)rn at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)rn at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:386)rn ... 18 morernCaused by: java.sql.SQLException: Illegal operation on empty result set.rn at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)rn at com.mysql.jdbc.ResultSet.checkRowPos(ResultSet.java:713)rn at com.mysql.jdbc.UpdatableResultSet.checkRowPos(UpdatableResultSet.java:246)rn at com.mysql.jdbc.ResultSet.getStringInternal(ResultSet.java:5624)rn at com.mysql.jdbc.ResultSet.getString(ResultSet.java:5544)rn at com.mysql.jdbc.ResultSet.getString(ResultSet.java:5584)rn at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:263)rn at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:263)rn at org.apache.jsp.index_jsp._jspService(index_jsp.java:170)rn ... 21 morern 论坛

After end of result set

10-09

搜索页面.输入一个确定存在的值,结果出现After end fo result set.找了半天也没找到哪里会有一个已经关闭的结果集.包含文件是一个全静态文件,没有动态内容.rn代码:rnrn<%@ page contentType="text/html; charset=GBK" pageEncoding="GBK" %>rn<%@ page language="java" %>rn<%@ page import="javax.naming.*" %>rn<%@ page import="javax.sql.DataSource" %>rn<%@ page import="java.sql.*" %>rnrnrn rn 搜索商品rnrnrn<%@ include file="includetop.jsp" %>rn<%rnString pname="";rnString sql="";rnrnif(request.getParameter("pname")!=null)rnrn pname=request.getParameter("pname");rn pname=new String(pname.getBytes("ISO-8859-1"));rn sql="SELECT * FROM product WHERE pname REGEXP '"+pname+"'";rn rn if(pname.equals(""))rn rn sql="SELECT * FROM product WHERE pname REGEXP'_*'";rn rn if(request.getParameter("stock")!=null)rn rn String ss=request.getParameter("stock");rn sql="SELECT * FROM product WHERE stock<="+ss;rn rn out.print(sql+" ");rn tryrn Context initCtx = new InitialContext();rn Context ctx =(Context)initCtx.lookup("java:comp/env");rn DataSource ds =(DataSource)ctx.lookup("jdbc/myweb");rn Connection conn = ds.getConnection();rn Statement stmt = conn.createStatement();rn ResultSet rs = stmt.executeQuery(sql);rn rn out.print("搜索结果如下: ");rn out.print(" ");rn out.print(" 序号");rn out.print(" 代码");rn out.print(" 商品名称");rn out.print(" 品牌");rn out.print(" 产品简介");rn out.print(" 存货数量");rn out.print(" 价格");rn out.print(" 更改");rn out.print(" 删除");rn rn while(rs.next());rn rn out.print(" ");rn out.print(" "+rs.getInt(1)+"");rn out.print(" "+rs.getString("pdm")+"");rn out.print(" "+rs.getString("pname")+"");rn out.print(" "+rs.getString("provider")+"");rn out.print(" "+rs.getString("info")+"");rn out.print(" "+rs.getString("stock")+"");rn out.print(" "+rs.getString("price")+"");rn out.print(" ");rn out.print("");rn out.print(" ");rn out.print("");rn out.print(" ");rn out.print("");rn out.print(" ");rn out.print("");rn out.print("");rn rn out.print("");rn rs.close();rn stmt.close();rn conn.close();rn catch(SQLException e)rn out.print(e.toString());rn rn%>rnrn 论坛

没有更多推荐了,返回首页