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).
Examples:
A001 => A002
BBBB => BBBC
WXYZ => WXZ0
And my shop would like it done in COBOL, but any algorithm will
suffice.
Maybe use a base 36 table???
<<SNIP>>
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
with
dig1b36 ( AN1D /* AlphaNumeric1Digits */ ) as
( values
('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')
,('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J')
,('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T')
,('U'),('V'),('W'),('X'),('Y'),('Z')
)
, 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:
"4DigitAlphaNumeric"
WXZ0
******** 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 :-)
midrange.com
