Hi

I am new to the Db2 Stored procedure so would like to get some help on it

I am writing an Outbound script to output data from my Db2 Contact table in a flat file. The Field Date value c1 is stored as 400.00

I would like to get an output value Field c2 as 00000040000 in my flat file. This field has to be of 11 characters always prefixed by zeros and therefore if the value of field c1 is 20.00 then the value of field c2 would be
00000002000 in my output file

Can some one write an Db2 SQL Statement for me...

Any help would be appreciated

Thanks

Gautam S

Here is a start:

SELECT CONCAT(REPEAT('0',11-LENGTH(CHAR(c1))),CHAR(c1)) AS c2 FROM xxx WHERE .....

Further enhancement to prior post

SELECT CONCAT(REPEAT('0',11-LENGTH(CAST(c1 as INTEGER))),CHAR(CAST(c1 as integer))) FROM xxx WHERE ....

HI,


With the EXPORT function do you create a flat file.

For example:

EXPORT TO "C:\Cotizacion.txt" OF DEL MODIFIED BY COLDEL; MESSAGES "C:\Cotizacion.txt" SELECT * FROM DB2DWAD1.A0_COTIZACION_DIA;

Hi

when i am running the query for an input value of 400.00

i am getting an output value of 0000000400

The lengh should be of 11 characters and not 10 . So when you change 11 to 12 i am getting the the value of 11 characters but the value that i intend to get is

00000040000 . The 2 zeros after the decimals are not being captured...

Any Inputs ?????????????????

regards

Gautam S

Then you must multiple c1 by 100. How does that output look?

SELECT CONCAT(REPEAT('0',11-LENGTH(CAST((c1*100) as INTEGER))),CHAR(CAST((c1*100) as integer))) FROM xxx WHERE ....

Amendment to my below post (ignore last post). With a little bit more refinement this is what you want:

SELECT
CONCAT(REPEAT('0',11-LENGTH(CAST(CAST((c1*100) as INTEGER) as char(5)))),CAST(CAST((c1*100) as integer) as char(5)))
FROM xxx WHERE .....

Hi

There are still some issues with this syntax.

When the input value is 90.00

The output value is 0000009000 which is of 10 characters and not 11 which I want. There should be an 0 in addition to the 6 zeros that we have. The final output value should be 00000009000 (Total Length of 11)

When the input is 900.00
The output value is a Perfect which is 00000090000 (Total 11 Characters)


When the input value is 9000.00

The output value is 00000090000 which is wrong as 9000.00 will have to be represented as 900000 and there will be 5 zeros in front of it meaning 00000900000 (Total length of 11)


Any Views


Thanks

Gautam S

I just tweaked the statement adding an additional DB2 function and changing the CHAR() length:

CONCAT(REPEAT('0',11-LENGTH(RTRIM(CAST(CAST((c1*100) as INTEGER) as char(11))))),RTRIM(CAST(CAST((c1*100) as integer) as char(11))))

I suggest going to the IBM web site and looking through the SQL reference manuals

http://www-3.ibm.com/cgi-bin/db2www/...bs.d2w/en_main

Another solution could be:

char(right(digits(decimal(c1,11,2)),11),11)