Thanks Chuck. I wish I could say that I understand what you are doing here. I think that you are populating the entire 36 ** 4 table entries, and then stepping into that table to get the next increment.

Really neat use of SQL

I went with the method that increments via a table in COBOL using the INSPECT verb.

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Friday, December 04, 2015 6:42 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: How to find next number for an alphanum field? Any good and simple tricks?

On 04-Dec-2015 16:38 -0600, Stone, Joel wrote:
I have a length 4 alphanum field which I need to find the next value
(incremented by one value).


A001 => A002



And my shop would like it done in COBOL, but any algorithm will

Maybe use a base 36 table???


Such a TABLE is easily enough created:

create table an4dig
( an4d char(4) not null
/* , constraint an4dig_pk primary key ( an4d ) */
insert into an4dig
dig1b36 ( AN1D /* AlphaNumeric1Digits */ ) as
( values
, dig4b36 ( AN4D /* AlphaNumeric4Digits */ ) as
( select char( x0.AN1D concat x1.AN1D concat
x2.AN1D concat x3.AN1D /* x#... etc. */
, 4 /* adj to (#+1) of concatenated x# */ )
from dig1b36 x0 /* low-order digit base^0 */
, dig1b36 x1
, dig1b36 x2
, dig1b36 x3 /* high-order digit base^3 */
select an4d
from dig4b36
order by 1
; -- 1679616 rows inserted in AN4DIG in CurSchema
alter table an4dig
add constraint an4dig_pk primary key ( an4d )
-- Two examples follow, both using the WXYZ value
-- and both using the same Language Sort Sequence used to
-- create the primary key that could have just as well
-- have been created as a unique keyed INDEX instead.
-- 1) Find next value after 'WXYZ' using FETCH FIRST clause:
select an4d as "4DigitAlphaNumeric"
from an4dig
where an4d > 'WXYZ'
fetch first 1 row only
-- report from above query follows:
******** End of data ********
-- 2) Find next value after 'WXYZ' using MIN aggregate function:
select min(an4d) as "4DigitAlphaNumeric"
from an4dig
where an4d > 'WXYZ'
-- report from above query is same as the prior report shown

I am unsure why, but neither of those SELECT queries implemented as
Index-Only, though I presumed both would.

Note: Often such numbering schemes will omit alphabetic characters
that might be _difficult to distinguish_ from numbers in some fonts;
i.e. often the 'O', 'L', and 'I' [or even 'Q'] might be omitted, such
that a base-33 [or base-32] would be the effect. The row-values-clause
with multiple values that was used to populate the data in the table is
easily enough modified to effect that; obviously my assigned named with
'b36' naming would be debased by that revision :-)

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].