• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

ResultSet within Resultset

I have mutiple results from the 1st query. I have to base on one of another fields from the 1st query to get the result set from the 2nd query. When I try to do it this way, only 1 resultset is return.

Can we do a resultset within a resultset?

String sql_GetPlanBundle = "SELECT PLAN_BUNDLE_ID, PROD_CODE, PLAN_CODE, PLAN_STATUS, PLAN_NETWORK_CODE FROM PLAN_BUNDLE WHERE PLAN_BUNDLE_ID IN (SELECT PLAN_BUNDLE_ID FROM CORP_PLAN WHERE BRN ='"+BRN_No+"')";
                   rs_GetPlanBundle = stmt.executeQuery(sql_GetPlanBundle);
                         
                         flag =0;
                   while (rs_GetPlanBundle.next())
                         {
                              out.println("");
                              out.println("<tr><td>" + rs_GetPlanBundle.getString("PLAN_CODE") + "</td>");
                              out.println("<td>" + rs_GetPlanBundle.getString("PLAN_NETWORK_CODE") +"</td>");
                              out.println("<td>" + rs_GetPlanBundle.getString("PROD_CODE") + "</td>");
                              out.println("<td>" + rs_GetPlanBundle.getString("PLAN_STATUS"));                        
                                   out.println("</td>");
                              String BUNDLE_ID = rs_GetPlanBundle.getString("PLAN_BUNDLE_ID");
                              out.println("<td>" + BUNDLE_ID + "</td>");
                             
                              out.println("<td>");
                              String sql_GetPlanBundleVAS = "SELECT DESCRIPTION FROM VAS WHERE PROD_CODE IN (SELECT PROD_CODE FROM PLAN_BUNDLE_VAS WHERE PLAN_BUNDLE_ID ='"+BUNDLE_ID+"')";
                              rs_GetPlanBundleVAS = stmt.executeQuery(sql_GetPlanBundleVAS);
                              while(rs_GetPlanBundleVAS.next())
                                        out.println(rs_GetPlanBundleVAS.getString("DESCRIPTION") + ",");
                              rs_GetPlanBundle.close();    
                              out.println("</td></tr>");
                              flag =1;
                         }
0
rac13
Asked:
rac13
  • 6
  • 5
  • 5
1 Solution
 
iboCommented:
u close the wrong resultset dude:

String sql_GetPlanBundleVAS = "SELECT DESCRIPTION FROM VAS WHERE PROD_CODE IN (SELECT PROD_CODE FROM PLAN_BUNDLE_VAS WHERE PLAN_BUNDLE_ID ='"+BUNDLE_ID+"')";

rs_GetPlanBundleVAS = stmt.executeQuery(sql_GetPlanBundleVAS);
                             
while(rs_GetPlanBundleVAS.next())
out.println(rs_GetPlanBundleVAS.getString("DESCRIPTION") + ",");

rs_GetPlanBundle.close();     <<-------

this one should be rs_GetPlanBundleVAS.close() right?
0
 
iboCommented:
therefore, immediately after the first iteration, the loop stops.. printing only one record.
0
 
iboCommented:
dont forget to close the rs_GetPlanBundle resultset outside the where.. loop
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
rac13Author Commented:
I have changed the closing of the correct recordset, but there is still only 1 data been display.
0
 
iboCommented:
which resultset? the descriptions? how many records are u expecting?
its probably ur sql statement.
0
 
iboCommented:
which resultset? the descriptions? how many records are u expecting?
its probably ur sql statement.
0
 
rac13Author Commented:
I expect 3 recordset from the 1st query:
eg:
Plan Bundle ID | Plan Bundle VAS
114523653B        Free A, Free B
111111225A        Free C, Free H

Plan bundle is from the 1st query, but the VAS is from the 2nd query
0
 
objectsCommented:
I have a recollection of having a similiar problem in the past. I think it ended up being that only one ResultSet could exist at one time, so I had to read all the data from the first query, storing what I needed. Then once all rows were read then do the second query using the stored values.

I'll see if I can confirm this and get back to u.
 
0
 
rac13Author Commented:
I expect 3 recordset from the 1st query:
eg:
Plan Bundle ID | Plan Bundle VAS
114523653B        Free A, Free B
111111225A        Free C, Free H

Plan bundle is from the 1st query, but the VAS is from the 2nd query
0
 
objectsCommented:
Try using a seperate Statement for each query, I think you can only have a single open ResultSet per Statement.
0
 
objectsCommented:
I depends on your JDBC driver and whether it supports multiple open Stements and/or ResultSets, but try using a seperate statement object per query and if this doesn't work the you'll have to read all the first query, close it and then run your second one.

Or possibly see if you can do what you need using a database join, or a stored procedure.
0
 
rac13Author Commented:
How can we check if it allows mutiple open statement?
0
 
objectsCommented:
Try it and see if it works :)
0
 
rac13Author Commented:
Yes.. after I created 2 statment, I can get it out to what I want. Thanks a lot.
0
 
objectsCommented:
Only a B :-)
Thanks for the points.
0
 
rac13Author Commented:
Grade low ponits high... :)
hee.. hee....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 6
  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now