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.
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Friday, December 04, 2015 6:42 PM
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
BBBB => BBBC
WXYZ => WXZ0
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:
-- with SRTSEQ(*LANGIDUNQ) or SRTSEQ(*LANGIDSHR)
create table an4dig
( an4d char(4) not null
/* , constraint an4dig_pk primary key ( an4d ) */
insert into an4dig
dig1b36 ( AN1D /* AlphaNumeric1Digits */ ) as
, 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 */
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"
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"
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 :-)