I have a Centura SQLBase (version 7, so it is over 10 years old; now apparently they changed the name back to Gupta) where I need to put a running number into a column while copying data into a temporary table.
The running number has to restart when a grouping code changes (so each group has its own counter), the data has to be ordered by a date column.
This is a conversion task. For testing (UAT) I only have to convert parts of the database (though it isn't really big, so I just want to convert all). For go-live, I will do a final conversion and the database will be disabled. So to be exact: it is a two-times task. Though not mission critical any outage is annoying for the users, as they have to switch to papers and then later key-in (instead of immediately key-in)
In essence I have this data:
employee_no,date,group_code
and I have to generate:
employee_no,date,group_code,group_running_number
The employee_no + date + group_code is the PK.
In Oracle, I would use an analytic function and get it done in one step, but in SQLBase I even cannot find something like "rownum".
I considered using the SYSDBSequence.nextval
sequence, but it stayed the same for a single insert (which probably makes sense).
I considered using their ROWID
(which contains a unique row number and I know how to pick that out), but I cannot use the ORDER BY
in the INSERT-SELECT
combo. (When creating views ORDER BY
isn't allowed either)
One last resort, if I cannot solve this with SQL, would be using a spreadsheet. There are only about 400,000 rows in total, but it certainly would be rather slow and error prone. On the final conversion day I want to have as few steps as possible.
If I cannot find a rownum
equivalent I might try writing a procedure in sqltalk doing RBAR processing.
Best Answer
One way to emulate row numbers in DBMS that do not have window function is using a triangular self join. It's usually not very efficient though.
Given that the primary key is
(employee_no, date, group_code)
:The order used for the assigning of row numbers above is:
(date ASC, employee_no ASC)
An index on
(group_code, date, employee_no)
will improve efficiency I think.