Re: Setting maximum rows to be selected

In article <32rjfn$nhm_at_ccnet.ccnet.com> tulcoc_at_ccnet.com (Ajoy Cherian) writes:
>
>In article <32h6ip$1rn_at_ionews.io.org> you wrote:
>: I'm running PowerBuilder Enterprise with an Oracle 7.0 back end. What I
>: want to know is if it's possible to limit the number of rows a select
>: statement returns. For example, a fairly complex select returns order
>: information based on several tables, a sub-select, and use of the
>: group by statement. Out of the 100 or so rows returned, I would like to
>: display the top 20 orders based on the revenue field (which is derived
>: from a group sum expression).
 

>: Neil Negandhi
>: negandhi_at_io.org
>
>As far as I know there is no way of doing it considering the fact that
>ur query is based on several tables.
>
>A 'rownum <=20' condition will not produce the correct result as this
>rownum <=20 is evaluated before ur group by or order by clause.
>usually in a join between two tables the rownum<=20 condition is apllied
>on the "driving" table along with the other where clauses before a group
>by or order by.
>
>I am not sure about the retriverow event or dbcancel( on a query based
>on multiple tables). Becuase in ur kind of requirement u need to
>retrieve all the rows satidfying the where condition and then select
>the top 20 or so. If u do a dbcancel after 20 rows.. well i am not
>sure whether this is what u want.
>
>
>If the query was based on a single table and without any group by
>etc then there is a way of getting only the top 20 records in a order by
>using the hints in v7. But ofcourse such simplisistic requirements
>doesnot happen in real life.
>
>Or if u can use a cursor and get all the records in the order u
>want and then display only the first 20. I don't know how/whether it
>can be done in PB as my PB knowledge is practicaly ZERO . But using oracle
>pre compilers these kind of things can be done.
>
>

What about setting "BLOCK=20" in your dbParm? I read from documentation the parameter indicates the number of rows retrieved from the database everytime. Therefore if you have 130 records to retrieve, it'll take 7 'reads'.

BTW, the default is 100.

King Received on Wed Aug 17 1994 - 03:12:58 CEST