0

The JDBC driver does not support ROW_NUMBER function. How can I get the row id from 1 to n when reading data from a table?

If I have a table with 2 columns as firstname and lastname, then I run a query like "SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rowid, * FROM tableName" in SQL Server. That will give me the rowid from 1 to n along with the contents of firstname and lastname in the ResultSet.

So I will get a result like:

1, Felix, Cao

2, Jack, Chan

3, Nick, Ho

I want to do a similar query for the OpenEdge database so I can get the rowid in the order like above.

Community
  • 1
  • 1
Cao Felix
  • 329
  • 2
  • 6
  • 24
  • No, you don't really seem to understand what a ROWID is. I think you need to explain the problem you want to solve! – Jensd Nov 03 '16 at 12:13
  • Okay, I will just ask the question again. – Cao Felix Nov 03 '16 at 13:16
  • It would appear that your real requirement has nothing to do with ROWIDs and that the actual requirement is that you want your result set to be sequentially enumerated? – Tom Bascom Nov 03 '16 at 17:46
  • Right, I think all I want to find out is to have a similar method like ROW_NUMBER that can assign the rowid for each row from 1 to n. So I can read that from a resultset object. – Cao Felix Nov 03 '16 at 19:51
  • Now that your requirement is clear I have updated my answer. You won't like it but it is what it is. – Tom Bascom Nov 04 '16 at 12:51

1 Answers1

3

No, there is no equivalent to ROW_NUMBER in an OpenEdge database.

http://knowledgebase.progress.com/articles/Article/Is-ORACLE-row-number-function-supported-in-OpenEdge

Regarding ROWID:

Setting aside the discussion of whether or not it is a good idea to think of ROWIDs as integers... (you should re-read the linked kbase)

There is no guarantee that any particular ROWID value will ever exist and there is no reason why ROWIDs should start with zero or one or any other value.

If your thought is that ROWIDs and RECIDs are sort of the same and RECIDs are integers consisting of a block# and a position within that block, ROWID 0 is still never going to exist because the first portion of a storage area is control data -- no rows are stored there.

You can successfully scan a table using guesses for ROWIDs or RECIDs (or by enumerating every possible value) but you have to be prepared to deal with gaps. This can be useful, for instance, when trying to recover data from a corrupted db. But it isn't something that one would normally do.

I think you have decided that ROWID is the solution to a problem that you are having but that the real problem is not "how to get ROWID 0". If I had to guess I would guess you are perhaps trying to get a result set in batches of N. In that case you should look at this: How to mimic SELECT ... LIMIT, OFFSET in OpenEdge SQL?

Community
  • 1
  • 1
Tom Bascom
  • 13,405
  • 2
  • 27
  • 33
  • sorry, I should not say 0. You are right, the 0 is never going to exist. ROWID gives unique number and I know it is increasing by 1. I just want it to start from 1 to n instead of some random number. Thanks for your answer – Cao Felix Nov 02 '16 at 17:20
  • You should not think of ROWIDs as integers but, setting that aside, ROWIDs do NOT increase by 1 or any other value. The value of a ROWID is not predictable. There will be gaps between the highest and the lowest values and your code must accept that fact or it will not work. – Tom Bascom Nov 03 '16 at 16:25
  • I see, I only test some number of records and I thought the number would be increased by 1. If there is going to be gaps, then I can't use ROWIDs in my application at all. – Cao Felix Nov 03 '16 at 19:49