[RBASE-L] - RE: Finding the autonum

[Dennis McGrath]

Actualy, this will only work if the column is indexed.
On a very large table select max took me 2 seconds even with the index.
When I used the where clause to minimize the rows read (from the index) it was 
very quick.

Dennis McGrath

________________________________
From: rbase-l@rbase.com [mailto:rbas...@rbase.com] On Behalf Of Dennis McGrath
Sent: Tuesday, May 04, 2010 11:23 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - RE: Finding the autonum

SELECT MAX(PartRef) +
        INTO test IND v1 +
        FROM Invent

Should be fast if the column PartRef is indexed.

Alternately if you get the sys_column_ID from the SYS_COLUMNS table and Look in 
SYS_Defaults there is a SYS_Next column that contains the next number.
This won't help you directly but it can be used to speed up the MAX

Lets say the NEXT number is 1,000,000 and you have retrieved it into vInt

Then
Set var vInt = (.vInt - 1000) - if deletes seldom happen in this table, you can 
make this 100 or even 10

SELECT MAX(PartRef) +
        INTO test IND v1 +
        FROM Invent +
        WHERE PartRef > .vInt


This should speed up the command significantly on a table with lots of rows.
Adjust to you specific needs

Dennis McGrath

Dennis McGrath
________________________________
From: rbase-l@rbase.com [mailto:rbas...@rbase.com] On Behalf Of Jack Mathis
Sent: Tuesday, May 04, 2010 10:50 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Finding the autonum

Is there a way to find the autonum number or the last autonum number used. I 
tried :

      SELECT PartRef +
        INTO test IND v1 +
        FROM Invent +
       WHERE COUNT = LAST

But this gives me the last record inserted, even if it was a updated record, 
that had to insert because the notes field added more than the record pad 
allowed.

SET VAR Test TEXT = NULL
GETPROPERTY TABLE 'Invent->RECORDCOUNT' Test
  --From the R>Prompt gives me
On XP x86 gives -- NULL
On Windows 7 x64 -- Access violation at address 004047A8 in module 
'rbg8ee.exe'. Read of address 53552039

SELECT MAX(PartRef) +
  INTO test IND v1 +
  FROM Invent

This seems slow

SET ERROR MESSAGE 2441 OFF

  SELECT PartRef +
    INTO test IND v1 +
    FROM Invent +
   ORDER BY PartRef +

SET ERROR MESSAGE 2441 ON
I figured if I could get the autonum number it might be faster yet, without 
errors.

Thanks


Jack Mathis<mailto:ja...@ericksonaircrane>
Erickson Air-Crane Incorporated
Computer Software Applications Programmer