Kevin, Dynamic ResultSet metadata is not supported. There are a number of very good reasons, which are mostly performance related. In the future, I would hope to solve this by supporting SQL fragmets, so that you can maintain the SQL in one place, but have 2 statements that use it. That way you don't even need the dynamic SQL part, nor do you need to imply the count by passing a null parameter.
Cheers, Clinton On Fri, 10 Dec 2004 00:35:16 -0800 (PST), Kevin <[EMAIL PROTECTED]> wrote: > > Hi > > Thanks for answering my questions, thanks for forwding > it to the forum. > > What u are suggesting is exactly what i am doing i.e > using 2 queries 1 for count and 1 for the result set. > > But i was hoping to use 1 dynamic sql instead of 2 to > achieve the above result. > > U can look at my sql example and u will see that the > condition after the Select does not work and that is > actually my question. > > Why does not work ? > > Thanks > Navin > > > --- Brandon Goodin <[EMAIL PROTECTED]> wrote: > > > Paginated list only returns a subset of the results. > > This is > > determined by the pageSize. It does not retain a > > total count. > > > > If you perform a count using the resultset it will > > force the cursor to > > traverse the whole resultset. Most databases do not > > load up large > > results sets completely. So, when you perform your > > count using the > > resultset you are already impacting your system in a > > negative way and > > not saving yourself any performance. > > > > My suggestion would be to use two sql calls. One > > using a count (as a > > count sql statement would be more performant than a > > resultset > > traversal) and the other sql statement to retrieve > > the results you > > want from the resultset. The PaginatedList in iBatis > > will only > > traverse the rows in the resultset that it needs to > > in order to > > populate the corresponding objects with. Then when > > you call the next > > page on the paginated list it retains the > > appropriate information to > > reconnect to the database and grab the next section > > of the results > > that you need. > > > > With this strategy you save yourself on memory and > > cpu. > > > > Brandon > > > > > > On Thu, 9 Dec 2004 23:31:15 -0800 (PST), Kevin > > <[EMAIL PROTECTED]> wrote: > > > Thanks for taking time to respond to my question. > > > > > > Here is an example of what i am trying to do. > > > > > > I have a query that serves a dual purpose > > > 1> > > > getting me a count of all the records that meet a > > > certain criteria > > > 2> > > > return a subset of the records of whole list i > > used > > > for getting the count as i donot need the whole > > result > > > set for display. > > > > > > The type of query executed depends on a field in > > the > > > parameter class. > > > > > > I have attached my query below , if u look at it > > > carefully u will see that the Select clause has a > > > condition on "countQuery" and so does the where > > > clause. > > > > > > Essentially the resultset columns change on the > > fly > > > depending on the query selected. > > > > > > This as far as i am told is not supported in > > Ibatis. > > > > > > I was wondering why this is not supported and what > > are > > > the alternatives rather than having 2 queries. > > > > > > Your suggestion of doing a size on the list will > > not > > > work because the result set is only a subset. i.e > > i > > > only display 100 out lets say 10000 records on the > > web > > > layer to make it more effecient , so i set my > > where > > > clause to give me the range of records whose list > > size > > > will be 100 but the count should return 10000. The > > > reson i need 10000 is for creating a web layer > > based > > > paginated list. > > > > > > Does the paginated list in Ibatis support this? > > > > > > <select > > > id="getEmployeeAndLastCourseCount" > > > > > > > > > parameterClass="com.performixtech.emvolve.devmgr.trainingmanager.sqlmap.p > > > aram.EmployeeAndLastCourseParam" > > > > > > > > > resultClass="com.performixtech.emvolve.devmgr.trainingmanager.model.Train > > > ingRequestListModel"> > > > Select > > > > > > <isNotNull > > > property="countQuery"> > > > > > > > > count(*) > > > as count > > > > > > </isNotNull> > > > > > > <isNull > > > property="countQuery"> > > > > > > rownumber, > > > > > > employeeId, > > > > > > employeeName, > > > > > > managerId, > > > > > > managerName, > > > > > > managementUnitId, > > > > > > managementUnitName, > > > > > > courseId, > > > > > > courseTitle, > > > > > > courseDate > > > > > > </isNull> > > > > > > FROM > > > ( > > > SELECT > > > rownum as rownumber, > > > e.emp_employee_id as > > employeeId, > > > e.emp_name as employeeName, > > > e.emp_manager as managerId, > > > e.manager_name as managerName, > > > e.muid as managementUnitId, > > > e.name as managementUnitName, > > > cS.course_Id as courseId, > > > cS.course_title as courseTitle, > > > cS.start_datetime as > > courseDate, > > > > > > cS.module_status_id > > > FROM > > > (select e.emp_employee_id, > > > e.emp_name, > > > em.emp_employee_id > > > > > > as emp_manager, > > > em.emp_name as manager_name, mu.muid, mu.name > > > > > > from employee e, > > > employee em, management_unit mu, > > > > > > mgmt_unit_association ma > > > > > > where > > > e.emp_manager = > > em.emp_employee_id > > > and > > > e.emp_employee_id = > > ma.employee_id > > > and > > > mu.muid = ma.muid > > > > > > ) e > > > > > > left outer join > > > > > > (select > > emp_employee_id, > > > start_datetime, a.course_id, a.sched_id, > > > > > > module_status_id, > > > c.course_title, rank from > > > (select b.emp_employee_id, > > > a.start_datetime, > > > a.course_id, > > > > > > a.sched_id, > > > b.module_status_id, dense_rank() over > > > > > > (partition by b.emp_employee_id > > > > > > order by b.emp_employee_id, > > > a.start_datetime) rank from > > > > > > course_schedule a, > > course_assignment > === message truncated === > > > > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com >
