I have a Progress database that I'm performing an ETL from. One of the tables that I'm reading from does not have a unique key on it, so I need to access the ROWID to be able to uniquely identify the row. What is the syntax for accessing the ROWID in Progress?
I understand there are problems with using ROWID for row identification, but it's all I have right now.
A quick google search turns up this: http://bytes.com/forum/thread174440.html
Read the message towards the bottom by [email protected] (you either want oid or ctid depending on what guarantees you want re persistence and uniqueness)
A quick caveat for my answer - it's nearly 10 years since I worked with Progress so my knowledge is probably more than a little out of date.
Checking the Progress Language Reference [PDF] seems to show the two functions I remember are still there: ROWID and RECID. The ROWID function is newer and is preferred.
In Progress 4GL you'd use it something like this:
FIND customer WHERE cust-num = 123.
crowid = ROWID(customer).
or:
FIND customer WHERE ROWID(customer) = crowid EXCLUSIVE-LOCK.
Checking the Progress SQL Reference [PDF] shows ROWID is also available in SQL as a Progress extension. You'd use it like so:
SELECT ROWID, FirstName, LastName FROM customer WHERE cust-num = 123
Edit: Edited following Stefan's feedback.
Just to add a little to Dave Webb's answers. I had tried ROWID in the select statement but was given a syntax error. ROWID only works if you specify the rest of the columns to select, you cannot use *.
This does NOT work:
SELECT ROWID, * FROM customer WHERE cust-num = 123
This does work:
SELECT ROWID, FirstName, LastName FROM customer WHERE cust-num = 123
Depending on your situation and the behavior of the application this may or may not matter but you should be aware that ROWIDs & RECIDs are reused and that they may change.
1) If a record is deleted it's ROWID will eventually be reused.
2) If the table is reorganized via a dump & load or a tablemove to a new storage area then the ROWIDs will change.