On 10-Dec-2015 13:20 -0600, Stone, Joel wrote:
On 04-Dec-2015 18:41 -0600, CRPence wrote:
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:
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
Yes. The effect of comma-delimited table-reference specifications is
an implicit CROSS JOIN; i.e. the /cross product/ aka Cartesian Product
result of combining the sets. Every _combination_ of the base-36
/digits/ generated in the row-values clause are repeated four times for
every possible value in each digit position of places 36**3, 36**2,
36**1, and 36**0. Thus the query in the INSERT statement [by implicit
CROSS JOIN] is generating all possible base-36 numbers.
Then, indeed, the SQL query SELECT statements given, showed a couple
ways to find _the next higher increment_ from a known value that would
be supplied as the operand [likely a host variable or parameter marker]
of the greater-than predicate.
Perhaps more straightforward, would be seeing the same done with just
the decimal digits, and limited to only two digit positions 10**1 and
10**0 [and perhaps more elucidating, my having properly ordered the use
of x# as eXponent\digit-position references: x1->10**1 and x0->10**0]:
dig1b10 ( dec1Dig /* One Decimal Digit (0 to 9) */ ) as
, dig2b10 ( dec2Dig /* Two Decimal Digits (00 to 99) */ ) as
( select char( x1.dec1Dig concat x0.dec1Dig , 2 )
from dig1b10 x1 /* left-most digit; the tens aka 10**1 */
, dig1b10 x0 /* right-most digit; the ones aka 10**0 */
select dec2Dig from dig2b10
-- the above will produce decimal numbers 00 to 99 but of
-- course, as the char(2) string, per every combination of
-- all decimal digits zero to nine in both digit-positions:
-- zero||zero->nine, one||zero->nine, … nine||zero->nine