General information
Title:
Simulating Next and Previous in Oracle
I have an application that is currently using VFP tables. I am in the process of porting this application to use Oracle on the backend. The current application allows navigating through the data on as many as 12 different orders by selecting top, next, previous and last. The users would like to retain this functionality when the data is moved into oracle.
I am presently using SQL pass through due to the large number of orders that are possible. The following code works for small tables all the time but on the primary table that is approx. 100,000 rows returns an error sometimes. Is there a better way to provide this functionality?
*----- Example retrieves next row
*----- Previous is the same except uses < and DESC
*----- Top and Last does not use value, Last uses DESC
CURSORSETPROP('MaxRecords',1,0) && Set the number of rows to
&& return to 1
cTable=THIS.sql_table
cOrder=THIS.sql_order
cCursor=THIS.sql_cursor
cValue=THIS.sql_parse(THIS.sql_order)
nHandle=THIS.sql_handle
sql_trans=[SELECT * FROM ]+cTable+ ;
[ WHERE ]+cOrder+[ > ]+cValue+ ;
[ ORDER BY ]+cOrder+[ ASC]
SQLPREPARE(nHandle,sql_trans,cCursor)
SQLEXEC(nHandle)
CURSORSETPROP('MaxRecords',-1,0) && Set the number of rows to
&& return to unlimited
Thanks,
Greg
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only