> Which fetches a sequence value and pads it with zeros
on the left. When the sequence value is more than
4 digits '####' is returned.
------------------------- Commands Entered -------------------------
ALTER SEQUENCE a_id RESTART WITH 1;
--------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------- Commands Entered -------------------------
SELECT NEXT VALUE FOR a_id
, SUBSTR(DIGITS(NEXT VALUE FOR a_id)||'####'
,7+SIGN(SIGN(NEXT VALUE FOR a_id - 10000)+1)*4,4)
FROM SYSIBM.SYSDUMMY1;
--------------------------------------------------------------------
1 2
----------- ----
1 0001
1 record(s) selected.
------------------------- Commands Entered -------------------------
ALTER SEQUENCE a_id RESTART WITH 9999;
--------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------- Commands Entered -------------------------
SELECT NEXT VALUE FOR a_id
, SUBSTR(DIGITS(NEXT VALUE FOR a_id)||'####'
,7+SIGN(SIGN(NEXT VALUE FOR a_id - 10000)+1)*4,4)
FROM SYSIBM.SYSDUMMY1;
--------------------------------------------------------------------
1 2
----------- ----
9999 9999
1 record(s) selected.
------------------------- Commands Entered -------------------------
SELECT NEXT VALUE FOR a_id
, SUBSTR(DIGITS(NEXT VALUE FOR a_id)||'####'
,7+SIGN(SIGN(NEXT VALUE FOR a_id - 10000)+1)*4,4)
FROM SYSIBM.SYSDUMMY1;
--------------------------------------------------------------------
1 2
----------- ----
10000 ####
1 record(s) selected.
------------------------- Commands Entered -------------------------
SELECT NEXT VALUE FOR a_id
, SUBSTR(DIGITS(NEXT VALUE FOR a_id)||'####'
,7+SIGN(SIGN(NEXT VALUE FOR a_id - 10000)+1)*4,4)
FROM SYSIBM.SYSDUMMY1;
--------------------------------------------------------------------
1 2
----------- ----
10001 ####
1 record(s) selected.