How to pad a sequence value with zeros

[gimme_this_gimme_that]

Hi,

I Oracle one can have :

select to_char(a_id_se q.nextval,'0000 ') from dual

Which fetches a sequence value and pads it with zeros
on the left. When the sequence value is more than
4 digits '####' is returned.

How do I do about the same thing in DB2 ?

select char(nextval_ for a_schema.a_id,' 0000') doesn't work.

I've tried :

select replicate('0',4-len(A.id)),A.id from (
select nextval for a_schema.a_id_s eq id
from sysibm.sysdummy 1 ) A

But this returns a message saying q_id_seq can not
be specified in this context.

Any ideas or handy functions?

Thanks.

[Bob Stearns]

gi************* ******@yahoo.co m wrote:

Hi,

I Oracle one can have :

select to_char(a_id_se q.nextval,'0000 ') from dual

Which fetches a sequence value and pads it with zeros
on the left. When the sequence value is more than
4 digits '####' is returned.

How do I do about the same thing in DB2 ?

select char(nextval_ for a_schema.a_id,' 0000') doesn't work.

I've tried :

select replicate('0',4-len(A.id)),A.id from (
select nextval for a_schema.a_id_s eq id
from sysibm.sysdummy 1 ) A

But this returns a message saying q_id_seq can not
be specified in this context.

Any ideas or handy functions?

Thanks.

A language independent trick (modulo various levels of syntactic sugar) is:
substr(char(mod (x, 10000)+10000),2 ,4)
A source of problem is negative x (and how mod treats that).

[Gert van der Kooij]

select char(nextval_ for a_schema.a_id,' 0000') doesn't work.

Maybe 'select right(digits(ne xtval for a_schema.a_id), 4) from
sysibm.sysdummy 1' will work.

[Serge Rielau]

Gert van der Kooij wrote:

select char(nextval_ for a_schema.a_id,' 0000') doesn't work.

Maybe 'select right(digits(ne xtval for a_schema.a_id), 4) from
sysibm.sysdummy 1' will work.

or
db2 => values substr(char(cas t(next value for s as decimal(4, 0))), 1, 4)
db2 (cont.) => ;

1
----
0003

Note that for general to_char() support take a look at the function
library of the migration tool kit.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Tonkuma]

> 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(N EXT VALUE FOR a_id)||'####'
,7+SIGN(SIGN(NE XT VALUE FOR a_id - 10000)+1)*4,4)
FROM SYSIBM.SYSDUMMY 1;
--------------------------------------------------------------------

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(N EXT VALUE FOR a_id)||'####'
,7+SIGN(SIGN(NE XT VALUE FOR a_id - 10000)+1)*4,4)
FROM SYSIBM.SYSDUMMY 1;
--------------------------------------------------------------------

1 2
----------- ----
9999 9999

1 record(s) selected.
------------------------- Commands Entered -------------------------
SELECT NEXT VALUE FOR a_id
, SUBSTR(DIGITS(N EXT VALUE FOR a_id)||'####'
,7+SIGN(SIGN(NE XT VALUE FOR a_id - 10000)+1)*4,4)
FROM SYSIBM.SYSDUMMY 1;
--------------------------------------------------------------------
1 2
----------- ----
10000 ####

1 record(s) selected.
------------------------- Commands Entered -------------------------
SELECT NEXT VALUE FOR a_id
, SUBSTR(DIGITS(N EXT VALUE FOR a_id)||'####'
,7+SIGN(SIGN(NE XT VALUE FOR a_id - 10000)+1)*4,4)
FROM SYSIBM.SYSDUMMY 1;
--------------------------------------------------------------------
1 2
----------- ----
10001 ####

1 record(s) selected.