• Post Reply Bookmark Topic Watch Topic
  • New Topic

Help!!!! URGENT

 
pavi kavi
Ranch Hand
Posts: 56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
I need to retrive only 20 datas everytime whenever i execute a querry even though the result set may contain 100 of rows i wnated this dynamically to happen.
For Example when i press the NEXT button it should take only from 21-40 & display it is it possible in EJB.
If YES please tell me how in EJB.
I am using JOIN statement
pavithra
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is not an EJB question, this is really an SQL question. Because, although you could filter out the unwanted records at any level in your application, there is only one "right" place to do it: the database query itself. I am assuming that you're either writing your own finder methods, or your application server allows you to put the following SQL in its generated finder methods.
Retrieving part of a larger result set, surely not an outlandish requirement, is suprisingly hard in standard SQL-92. If you have a query
SELECT a FROM b WHERE c ORDER BY d ASC
and you want only records n to m, you can use a nested select to count all the records that come before your current record, and impose that this count must be between m and n, like so:
SELECT outer.a FROM b outer WHERE outer.c AND
(SELECT COUNT(*) FROM b inner WHERE inner.c AND inner.d <= outer.d)) BETWEEN n AND m<br /> ORDER BY outer.d ASC<br /> Obviously, this is pseudocode, where a is a list of fields, b a list of tables, c your where clause, and d your order by which must specify a unique order. With a nested query like this, a database without a good optimiser will not perform very well. In fact, in such cases you may be better off running the entire query and throwing away the records you don't need.<br /> Fortunately, databases' proprietary SQL extensions usually address this problem. Oracle is probably the simplest because it has a ROWNUM pseudo-column which you can use directly:<br /> SELECT a FROM b WHERE c AND ROWNUM BETWEEN n AND m ORDER BY d ASC<br /> That's it. Microsoft SQL Server has a far cruder TOP modifier to the select, which means it is only useful if you already know the first record you want to show (denoted by first_d):<br /> SELECT top (m-n) a FROM b WHERE c AND d>=first_d ORDER BY d ASC
This is OK if you're going forward (just do d > last_d), but poses a problem when you want to browse backwards. Ok, you could change the sorting order I guess. I'm not familiar enough with other databases to comment on MySQL, Sybase and whatnot.
- Peter

[This message has been edited by Peter den Haan (edited May 03, 2001).]
 
Anonymous
Ranch Hand
Posts: 18944
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
try this out.
SELECT Top 100 FROM EMP WHERE EMpNo > 1
 
pavi kavi
Ranch Hand
Posts: 56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi peter
Thanks for help.
but is it possible with this querry
SELECT c.EDITION,c.PUBLICATIONDATE,d.NAME,d.SHIPPINGNOTES,d.PUBLISHINGIDENTIFIERCODE,d.RETAILPRICE,e.NAME,g.LASTNAME,h.NAME
FROM KEYWORD a,PRODUCTKEYWORDREL b,BOOK c,PRODUCT d,FORMAT e,AUTHORPRODUCTREL f,AUTHOR g,PUBLISHER h
WHERE a.keyword LIKE '%java%'
and a.keywordid=b.keywordid
and b.productid=c.productid
and b.productid=d.productid
and d.formatid=e.formatid
and b.productid=f.productid
and f.authorid=g.authorid
and c.publisherid=h.publisherid
can i use u r funda here .
please help me out .
Ihope so
Thanks once again
pavithra
Originally posted by pavi kavi:
Hi
I need to retrive only 20 datas everytime whenever i execute a querry even though the result set may contain 100 of rows i wnated this dynamically to happen.
For Example when i press the NEXT button it should take only from 21-40 & display it is it possible in EJB.
If YES please tell me how in EJB.
I am using JOIN statement
pavithra

 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
To retrieve records 10 to 19:

If you know (as in "database constraints") that every PRODUCTKEYWORDREL record has a BOOK, PRODUCT, FORMAT, AUTHORPRODUCTREL, AUTHOR, and PUBLISHER, you can optimise this by dropping everything from BOOK onwards from the nested query.
Again, if you've got Oracle you would not do it this way at all; you'd just add "AND ROWNUM BETWEEN 10 AND 19". If you've got SQL Server you would try to use TOP if possible.
- Peter

[This message has been edited by Peter den Haan (edited May 04, 2001).]
 
pavi kavi
Ranch Hand
Posts: 56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi peter
Thanks for u r help but its not working it is showing me all the values instead of only between 10 to 19
CAn u please help me out
pavithra
Originally posted by Peter den Haan:
[B]To retrieve records 10 to 19:

If you know (as in "database constraints") that every PRODUCTKEYWORDREL record has a BOOK, PRODUCT, FORMAT, AUTHORPRODUCTREL, AUTHOR, and PUBLISHER, you can optimise this by dropping everything from BOOK onwards from the nested query.
Again, if you've got Oracle you would not do it this way at all; you'd just add "AND ROWNUM BETWEEN 10 AND 19". If you've got SQL Server you would try to use TOP if possible.
- Peter

[This message has been edited by Peter den Haan (edited May 04, 2001).][/B]

 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!