Re: Jumping To a record in SQL

Paul Austin (usfmdb7j_at_ibmmail.com) wrote:
> Hi,
 

> I am developing a web application using the oraPerl library to access
> an ORACLE 7 database. As part of the application we need to be able to
> jump to a particular record. We cannot use the ROWNUM pseudo column in
> the WHERE clause as this is calculated before an order by is issued.
> I have come up with the following solution and was wondering if anyone
> can think of a better solution (without using PL*SQL) to the solution
> in normal SQL.
 

> Assume we have a table BUYERS with three fields CD_BUYER, NA_BUYER and
> we want to look at record 10. The solution I came up with is.

[SQL snipped]

I assume you are selecting the Nth value from a list box and want to fetch more data on the user choosing an item. Have you considered selecting the CD_BUYER and NA_BUYER data when you populate the web page, and storing it in an oraperl list or associative array. That would save a hit on the database, and simplify the lookup question.

If the data is too large for this approach, you might also consider joining the BUYERS table when populating the web page, and selecting the rowid. This could be stored for use in a fetch by rowid instead of the numeric order based SQL algorithm.

You should also consider the problem of another process inserting or deleteing a row into the BUYERS table between populating your web page, and your user selecting a value. Your assumption that the 10th item is still the same as when you presented the data may no longer be true.

--

Greg.
---------------------------------------------------
All hail the three great virtues of the programmer:
Laziness, impatience and hubris.

echo '[q]sa[ln0=aln256%Pln256/snlbx]sb3135071790101768542287578439snlbxq'|dc