SELECT Top 1 PartRef +
INTO test IND v1 +
FROM Invent +
ORDER BY PartRef Desc
Try this. It is FAST, and reliable, and will throw no error.
Dennis McGrath
________________________________
From: [email protected] [mailto:[email protected]] On Behalf Of Jack Mathis
Sent: Tuesday, May 04, 2010 1:07 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - RE: Finding the autonum
On the original message I missed typed, should have been:
SET ERROR MESSAGE 2441 OFF
SELECT PartRef +
INTO test IND v1 +
FROM Invent +
ORDER BY PartRef Desc +
SET ERROR MESSAGE 2441 ON
This still seems the fastest
And yes it is a very large table. One table we use this routine on is over 3
million records.
Thank you for the response
Jack Mathis
From: [email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath
Sent: Tuesday, May 04, 2010 9:27 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - RE: Finding the autonum
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: [email protected] [mailto:[email protected]] 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: [email protected] [mailto:[email protected]] 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
