• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Help!!!! URGENT

 
Ranch Hand
Posts: 56
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
author
Posts: 3252
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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).]
 
Ranch Hand
Posts: 18944
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 3252
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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]


 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic