I am in the process of writing an interface to an application running on sybase. What I need to do, is to select an entire table, but only recieve 1 row at a time. I have done this in Oracle using the following statement :-
SELECT * FROM (SELECT F1, F2, F3, ROW_NUMBER() OVER (ORDER BY F1) x FROM TABLE1) WHERE x=number
and this query gets called with number incrementing till it reaches the end.
I have searched, and been unable to find a function that resembles ROW_NUMBER() in sybase. Does such a function exist?
Sybase does not have the concept of the row number, and so doesn't have anything like a ROW_NUMBER() function.
If you need to fetch one row at a time you could open a cursor.
Otherwise you need to figure out how to identify the next row in the sequence in some way and fetch it with the appropriate WHERE clause.
Note that the "next" row really depends on the ORDER BY clause - there is no guarantee that the rows will come back in any particular order if you don't specify an ORDER BY.
Actually, Sybase did try to imlpement the ROW_ID in a really crude fashion.
sp_dboption 'db name', 'auto identiry', 'true
This is usually done at the initial database creation time and all the tables created after thsi setting would automatically have a SYB_IDENTITY_COL column, which pretty much behaves like an normal identity column.
This wasn't terribly sucessful because of the identity gaps that can occur, for exxample the row with SYB_IDENTITY_COL= 200000 may in fact be the 100000th record