GetNextNumberWithGaps function

Syntax

GetNextNumberWithGaps(record.field, max_number, increment [, WHERE_Clause, paramlist])

Where paramlist is an arbitrary-length list of values in the form: 

var1 [, var2] ...

Description

Use the GetNextNumberWithGaps function to determine the highest value in a table for the field you specify, and return that value plus increment.

Note:

This function has been desupported and remains for backward compatibility only. Use the GetNextNumberWithGapsCommit function instead.

This function also enables you to specify a SQL WHERE clause as part of the function for maintaining multiple sequence numbers in a single record.

Note:

GetNextNumberWithGaps also issues a COMMIT after incrementing the sequence number if no other database updates have occurred since the last COMMIT. This limits the time a database lock is held on the row and so may improve performance.

Parameters

Parameter Description

record.field

Specify the record and field identifiers for the field for which you want the new number. This is the recommended way to identify the field.

max_number

Specify the highest allowed value for the field you're incrementing. You can specify up to 31 digits for this value.

increment

Specify the value you want the numbers incremented by. You can specify up to 31 digits for this value.

WHERE_Clause

Specify a WHERE clause for maintaining multiple sequence numbers.

paramlist

Parameters for the WHERE clause.

Returns

A Number value equal to the highest value of the field specified plus one.

GetNextNumberWithGaps returns an error if the value to be returned would be greater than max_number. The function returns one of the following:

Numeric Value Constant Value Description

Number

N/A 

The new number

-1

%GetNextNumber_SQLFailure

SQL failure

-2

%GetNextNumber_TooBig

Number too large, beyond max_number

-3

%GetNextNumber_NotFound

No number found, invalid data format

Example

The following PeopleCode:

&greg = GetNextNumberWithGaps(GREG.DURATION_DAYS, 999999, 50, 
"where emplid = :1", 8001);

results in the following:

2-942   21.53.09    0.000 Cur#4.PTTST81B RC=0 Dur=0.000 Connect=PTTST81B/sa/ 
2-943   21.53.09    0.000 Cur#4.PTTST81B RC=0 Dur=0.000 COM Stmt=UPDATE PS_GREG
 SET DURATION_DAYS = DURATION_DAYS + 50 where emplid = 8001 
2-944   21.53.09    0.000 Cur#4.PTTST81B RC=0 Dur=0.000 EXE 
2-945   21.53.09    0.000 Cur#4.PTTST81B RC=0 Dur=0.000 COM Stmt=SELECT DURATION_
DAYS FROM PS_GREG where emplid = 8001 
2-946   21.53.09    0.000 Cur#4.PTTST81B RC=0 Dur=0.000 EXE 
2-947   21.53.09    0.000 Cur#4.PTTST81B RC=0 Dur=0.000 Fetch 
2-948   21.53.09    0.010 Cur#4.PTTST81B RC=0 Dur=0.010 Commit 
2-949   21.53.09    0.010 Cur#4.PTTST81B RC=0 Dur=0.010 Disconnect

PeopleCode Event Considerations

Because this function results in a database update (specifically, UPDATE, INSERT, and DELETE) it should only be issued in the following events:

  • SavePreChange

  • WorkFlow

If you use this function in an event other than these, you need to ensure that the dataflow is correct and that you do not receive unexpected results.

Related Topics