Jack,
Here is code I use to find out the autonum:
>>>
SELECT sys_next, sys_increment, sys_default INTO v_next, v_inc, v_format
FROM +
SYS_DEFAULTS T1, SYS_COLUMNS T2, SYS_TABLES T3 WHERE +
T3.sys_table_name = 'fwo_file' AND +
T3.sys_table_id = T2.sys_table_id AND +
T2.sys_column_name = 'fwo_no' AND +
T2.sys_column_id = T1.sys_column_id
SET VAR v_next_wono = .v_next
SET VAR v_inc_int INT = (INT(.v_inc))
SET VAR v_last = (.v_next - 1)
SET VAR v_last_text = (FORMAT(.v_last, '00000'))
SET VAR v_next_text = (FORMAT(.v_next, '00000'))
SET VAR v_last_text = (SGET(.v_last_text,5,2))
SET VAR v_next_text =(SGET(.v_next_text,5,2))
SET VAR last_wono = ((SGET(.v_format,3,2)) + .v_last_text )
SET VAR next_wono = ((SGET(.v_format,3,2)) + .v_next_text )
<<<
The table name is: fwo_file
The column name is: fwo_no
The format of the autocolumn is: xx-00000 - Where xx are the last 2 digits
of the year when the number was generated.
As you can see, I manipulate the various components of the autonum in
various formats to display on a form. The SGET commands get rid of the blank
spaces at the front.
Hopefully this info is of help.
Best regards,
Javier Valencia, PE
Principal
Valencia Technology Group, LLC
14315 S. Twilight Ln.
Suite #14
Olathe, Kansas 66062-4571
913-829-0888 Office
913-915-3137 Cell
913-649-2904 Fax
<mailto:[email protected]> [email protected]
Confidentiality Notice
This electronic mail (including any attachments) may contain information
that is privileged, confidential, and/or otherwise protected from disclosure
to anyone other than its intended recipient(s). Any dissemination or use of
this electronic email or its contents (including any attachments) by persons
other than the intended recipient(s) is strictly prohibited. If you have
received this message in error, please notify us immediately at 913-829-0888
or by reply email so that we may correct our internal records. Please then
delete the original message (including any attachments) in its entirety.
_____
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
