473,327 Members | 1,967 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

How to pad a sequence value with zeros

Hi,

I Oracle one can have :

select to_char(a_id_seq.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_seq id
from sysibm.sysdummy1 ) A

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

Any ideas or handy functions?

Thanks.

Nov 12 '05 #1
4 14553
gi*******************@yahoo.com wrote:
Hi,

I Oracle one can have :

select to_char(a_id_seq.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_seq id
from sysibm.sysdummy1 ) 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).
Nov 12 '05 #2

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

Maybe 'select right(digits(nextval for a_schema.a_id),4) from
sysibm.sysdummy1' will work.
Nov 12 '05 #3
Gert van der Kooij wrote:
select char(nextval_ for a_schema.a_id,'0000') doesn't work.


Maybe 'select right(digits(nextval for a_schema.a_id),4) from
sysibm.sysdummy1' will work.

or
db2 => values substr(char(cast(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
Nov 12 '05 #4
> 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.

Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Ken1 | last post by:
I am going to drop a primary key from one column and create a new column to be used as primary key in an existing database. The old column was a date column which someone earlier though was a good...
21
by: Allin Cottrell | last post by:
OK, I realize that what I am asking here is not likely to have a answer within the C standard. Nonetheless, it is not specific to any particular platform, so I'll hazard the question anyway. A...
53
by: Deniz Bahar | last post by:
I know the basic definition of a sequence point (point where all side effects guaranteed to be finished), but I am confused about this statement: "Between the previous and next sequence point an...
1
by: Marek Lewczuk | last post by:
Hello, I would like to ask if my problem with sequence is a proper behavior or this is a bug (probably not)... I have a table: CREATE TABLE "testtable" ( "serialfield" SERIAL,...
14
by: Anthony Liu | last post by:
I am at my wit's end. I want to generate a certain number of random numbers. This is easy, I can repeatedly do uniform(0, 1) for example. But, I want the random numbers just generated sum up...
0
by: amitsoni.1984 | last post by:
Hi, I am using the code given below where A is a matrix and row is a sequence. But it gives following error: -------- error------ A=row ValueError: setting an array element with a sequence. ...
8
by: Daneel | last post by:
Hello! I'm looking for an algorithm which finds all occurences of a bit sequence (e.g., "0001") in a file. This sequence can start at any bit in the file (it is not byte aligned). I have some...
1
davydany
by: davydany | last post by:
Hey guys...a n00b Here for this site. I'm making a sequence class for my C++ class. And The thing is in the array that I have, lets say i put in {13,17,38,18}, when i see the current values for the...
6
by: JimmyKoolPantz | last post by:
Task: Customer wants a script of the data that was processed in a "CSV" file. Problem: Zip-Code leading zeros are dropped Basically we have a client that has requested a custom script for...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.