Zeina- Sounds like you need to group your query.
I have done this by setting a placeholder variable, such as currentProjectID, then checking for equivalency. If the current ProjectID from the ResultSet is not equal, I output the group - say a table head with the ProjectName. Then I set currentProjectID equal to the ProjectID whose name I just output. The rest of the output then will output the data - maybe tasks within the project and the loop begins again. The next time through, however, the ProjectID is equal, so the table header is not output but the task is. It works as long as your query is grouped and ordered properly. -Drew -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 26, 2002 12:01 PM To: JRun-Talk Subject: RE: Searching Resultset - my solution Celeste, I am using MS SQL Server and am using inner/outer joins. The reason why I am looping through two resultsets and not joining them, is: While I need one row for each projectid from the first resultset, I need to get all the rows with the same project id from the second resultset. When I do inner join, the resultset includes the duplicate data. Then instead of having multiple duplicate rows in the HTML table, with the same info except for few columns for each project x, having two resulset allows me to populate the first row with the first resulset row and then I am adding columns for the additional info for each projectid x from the second resultset. Thanks, Zeina "Haseltine, Celeste" To: Jrun-Talk <[EMAIL PROTECTED]> <CHaseltine@magt cc: icket.com> Subject: RE: Searching Resultset - my solution 02/26/2002 02:19 PM Please respond to jrun-talk Zafif, I have not really been following this discussion, so the suggestion I am about to make may have already been made. But it sounds to me like you are returning two result sets, and are looping through the second result set to find the records that relate back to a particular record in the first result set. Why are you doing this? Besides taking up a LOT of memory on your server, it's an inefficient way to code. A couple of suggestions here. 1. If you need the first result set to display a list for user selection, then do so. After the user selects the item he is interested in via your list, store the primary key value for the record your user selects in a session object. On the next "page", re-query your database using either an inner or outer join to return ONLY the primary record and its related records back to you, and then proceed on. 2. If you are NOT using a relational database on the back end that allows you to perform inner or outer join query's, consider using an XML file to initially store your data. You can query your database to obtain your records, write them out to an XML file onto your server, and then use that XML file as a "read only" version of your data for the duration of the session, making sure that you destroy it when your user logs out. Then you can then traverse the XML "tree" to find your primary record, and all it's related "child" records, IF you define your XML "tree" in this manner. In the event you are not familiar with inner/outer SQL joins, I have provided an example for you. In the below example I have a parent table called Orders and a child table called OrderDetails. The relationship between the two tables is one Order record to one or many OrderDetails records. In order to return a particular order record which contains ALL the related detail records for an Order with the primary key value of 2, I can query the db as follows: SELECT OrderDetails.OrderID, Orders.DateOrderPlaced, Orders.ConfirmationNumber, OrderDetails.TicketID, OrderDetails.Quantity, Orders.OrderID FROM OrderDetails INNER JOIN Orders ON OrderDetails.OrderID = Orders.OrderID WHERE (OrderDetails.OrderID = 2) I don't know if this helps you any, but please re-evaluate your need to maintain two large result sets in memory. That's generally not a good way to code JSP's. Celeste -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 26, 2002 12:45 PM To: JRun-Talk Subject: RE: Searching Resultset - my solution Nikhil, tried to do what U suggested, but then for each entry in the Hashtable, I would have a vector, and since there are over 100 records...I will end up with over 100 vectors. I wasn't sure if that was efficient. What I am really trying to do is loop through one resultset A and in it, loop through another resultset B, and get the matching rows. So I tried searching through the resultset B by using the while(rsB.next()) and then finding the rows that I need, but the problem I had, jrun would not accept rsB.beforeFirst(), so I got data for the first row and not the rest of resultset A. Finally, I created 4 vectors, that each stored one of the columns of the resultset B, and if the column had a null value, I replaced it with '-'. Then searched through one of the vectors for the parameter and Vector vProj = new Vector(); Vector vODS_TYPE = new Vector(); Vector vPrevious_ODS = new Vector(); Vector vODS_Phased_Out = new Vector(); while (rsB.next()){ vProj.addElement(rsB.getString("SUB_PROD_ID") == null?"-":rsB.getString ("SUB_PROD_ID")); vPrevious_ODS.addElement(rsB.getString("Previous_ODS_to_be_Phased_Out") ==null?"-":rsB.getString("Previous_ODS_to_be_Phased_Out")); vODS_TYPE.addElement(rsB.getString("ODS_TYPE") == null?"-":sB.getString ("ODS_TYPE")); vPrevious_ODS.addElement(rsB.getString("Previous_ODS_to_be_Phased_Out") ==null?"-":rsB.getString("Previous_ODS_to_be_Phased_Out")) ; vODS_Phased_Out.addElement(rsB.getString("ODS_Phased_Out") ==null?" -":rsB.getString("ODS_Phased_Out"));} // I loop through the Resultset A, and populate a row of the table, then search for the parameter in one of the vectors (vProj). ------------------ ------------------- // for(int x = 0; x < vProj.size(); x++){ if((vProj.elementAt(x)).equals(Sub_Project_ID)){ counto++; %> <td class="ReportTableCellLabel"> <%=vODS_TYPE.elementAt(x)%> </td> <td class="ReportTableCellLabel"> <%=vPrevious_ODS.elementAt(x)%> </td> <td class="ReportTableCellLabel"> <%=vODS_Phased_Out.elementAt(x)%> </td> <% } } I don't know if this is the best solution, but that's the only way I could get it to work. When I first learned about the QueryTable Object, I thought it would include a method to search through the QueryTable obj. Thanks, Zeina Nikhil Silsarma <Nikhil.Silsarma@digit To: Jrun-Talk <[EMAIL PROTECTED]> alrum.com> cc: Subject: RE: Searching Resultset 02/26/2002 10:58 AM Please respond to jrun-talk Why don't you use Hashtable( key=parameter, value=another hashtable/or Vector). In that case, based on the key it will return the data). Nikhil R Silsarma Developer DigitalRUM UK Ltd. Tel: 020 7604 2049 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 26 February 2002 15:20 To: JRun-Talk Subject: Searching Resultset Drew, Great book! Went through it over the weekend! Wish I got it 6 months ago, when I started to develop in JSP! Could have saved me lot of time!! Anyway, my q for the day. I need to be able to search a resultset. The parameter that I need to use to search, is not unique in the Resultset and I need to be able to get all the entries with that key. I thought of using hashatable, but since the key is not unique, it won't work, I thought of using vectors of arrays. I looked into QueryTable and didn't find any methods to get by key. Is there an easy way to search the resultset based on a value? Thanks, Zeina ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
