Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Clarification: This is NOT MS SQL server.

[As there is no suitable tag for centura-database and I am not allowed creating tags I had to use this generic one - sorry about that]

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)

In essence I have this data: employee_no,date,group_code

and I have to generate: employee_no,date,group_code,group_running_number

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.

share|improve this question
1  
use an INSERT trigger and sequence ? probably also slow. –  r.m Sep 7 '13 at 0:44
    
Another interesting idea I didn't think about! Thanks a lot! Please make this an answer in case I can get it to work this way. –  w2013 Sep 9 '13 at 13:02
    
Another thing that is not clear is whether this is an one-time operation to convert the whole dataset into a new table or you'll need to run it again later (say once per week or month.) –  ypercube Sep 9 '13 at 13:11
    
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) –  w2013 Sep 10 '13 at 2:21
add comment

2 Answers 2

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:

Edit, after the OP commented that the primary key is (employee_no, date, group_code)

SELECT
    a.employee_no, a.date, a.group_code,
    COUNT(*) AS group_running_number
FROM
    tableX AS a
  JOIN
    tableX AS b
      ON  a.group_code = b.group_code
      AND ( a.date > b.date
         OR a.date = b.date AND a.employee_no >= b.employee_no
          )
GROUP BY
    a.group_code, a.date, a.employee_no ;

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.

share|improve this answer
    
Interesting idea! Your estimate about poor efficiency is correct; extrapolating several increasing sample sets shows that this solution in impractical for my dataset, but would work in principle (unfortunately I cannot upvote your suggestion but would like to thank you for this suggestion) –  w2013 Sep 9 '13 at 6:24
1  
An index on (group_code, date, pk, employee_no) may help (no idea about Gupta optimizer, but judging from other DBMS). –  ypercube Sep 9 '13 at 6:27
    
Yes, there already is an index (the employee_no + date + group_code are the PK) with 22,000 rows it took less than 10 min. but 42,000 rows took over 4 hours on my local test database, which is BTW faster then the current production database. As the whole set has 400,000 rows I stopped testing there (I did "update statistics on database" but it still used an INDEX LOOP; AFAIK the database does not offer hints) Thanks for your input. Clarification: I cannot upvote your answer because of my low reputation, which will hopefully change one day ;-) –  w2013 Sep 9 '13 at 12:58
1  
An index on (employee_no, date, group_code) is different than an index on (group_code, date, employee_no) –  ypercube Sep 9 '13 at 12:59
    
Silly me, how could I miss that! It does improve runtime significantly although it still uses the INDEX LOOP: the 42,000 rows now took just 6 min.; 86,000 rows took 28 min. (so for the 400,000 rows it probably will still be a bit too slow; but I learned from your comments - thanks a lot!) –  w2013 Sep 10 '13 at 1:45
add comment

I checked the documentation once more and conclude that there is no rownum equivalent in Centura SQLBase (at least not in that old version; they have a version 11.7 now).

So I bit the bullet and wrote a procedure to process the data RBAR-style (Row-By-Agonizing-Row), which turned out not so bad. It took 3 minutes to process the 300,000 rows test data in my local test database. The procedure isn't saved anywhere, I just ran it in "sqltalk", the SQL client that came with the database. Maybe it can be useful to someone in the future to see how to do that: (Long lines can be broken up with a backslash, but that messes up the syntax highlighting in here. FYI: I committed manually.)

procedure: createRunningNum
Parameters
    receive number: nTotal
    receive string: sMessage
Local Variables
    sql handle: hSql1
    sql handle: hSql2
    Number: nInd
    Number: nCount
    String: sROWID
    String: sGC
    String: sGCold
Actions
    call SqlConnect(hSql1)
    call SqlConnect(hSql2)
    call SqlPrepareAndExecute(hSql1,'select rowid,group_code from TMP_TEST order by group_code,date_ into :sROWID, :sGC ')
    call SqlPrepare(hSql2,'update TMP_TEST set RunningNumber=:nCount where rowid=:sROWID')
!   Note: if we set sGCold to a null-string, the condition "sGCold!=sGC" is always false!
    set sGCold='q#q@q! anything we can be sure not to encounter by chance'
    set nTotal=0
    while SqlFetchNext(hSql1,nInd)
        if sGCold!=sGC
            set nCount=0
            set sGCold=sGC
        set nCount=nCount+1
        call SqlExecute(hSql2)
        set nTotal=nTotal+1
    call SqlDisconnect(hSql1)
    call SqlDisconnect(hSql2)
    set sMessage='rows updated.'
\
42,anything
/

To compare my version with ypercube's version I also ordered by employee_no and indeed received an exactly identical result. So I am confident that ypercube's version would theoretically work, it just isn't practical for large sets (but nevertheless a very creative solution given the limited abilities of that database).

The trigger suggestion is another interesting idea, but as I have a fast solution already I doubt I will ever test it.

FYI: Centura-SQLBase (version 7) has only ONE global sequence, and it might jump. I never investigated if it would avoid holes as single user, but I can confirm that it wraps to zero after 2^32 increases.

So I envisioned having an extra table that tracks the maximum running number for each group. I would be the only one inserting into the temporary table, so there would be no concurrency issues. With only about 30 groups I would expect a roughly constant time spent for each insert and no significant degrading processing speed per line as the table grows. (The conversion would be one single insert into the temporary table with the trigger taking care of the running number.)

I would expect the trigger version to be faster than the self-join above a certain size, but not as fast as the specialized "one-time" function I wrote.

I want to thank both contributors for their creative suggestions.

share|improve this answer
1  
Nice. Is that similar to cursors (in SQL-Server)? I run through the Centura SQL pdf and it mentions cursors (available in SQLTalk) but not much details. Perhaps you can try that road, too. (Just run a simple SELECT * FROM table ORDER BY group_code, date, employee_no through the cursor and let it assign the row numbers) –  ypercube Sep 10 '13 at 9:53
    
I don't know much about SQL server; sqltalk can connect to several databases (each database connection gets a unique number) then you can do e.g. "copy 1.tmp_view to 2.final_table;" but you have to make sure the column order is correct. It is sqltalk that processes the copy. (The procedure I wrote is processed by the database; the docu says about SqlConnect: Connects to the currently active database). But your suggestion is a 3rd possibility namely I copy the data over to an Oracle database and process over there. (sqltalk comes with connection DLLs for Oracle and other databases) –  w2013 Sep 13 '13 at 11:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.