Data type convert issue

[James]

See the following example.

db2 =create table aaa (c1 decimal(10,3));
DB20000I The SQL command completed successfully.

db2 =insert into aaa values (200.03);
DB20000I The SQL command completed successfully.
db2 =select * from aaa;

C1
------------
200.030

1 record(s) selected.

db2 =select char(c1) from aaa;

1
------------
0000200.030

1 record(s) selected.

The result is not what I want. I want to the result to be 200.03 as
CHAR type. How can I do this convert?

Thanks in advance.

James

[Konstantin Andreev]

James wrote:

db2 =select char(c1) from aaa;

1
------------
0000200.030

The result is not what I want. I want to the result to be 200.03 as CHAR type. How can I do this convert?

Maybe there are the better ways, but, if nothing else, you can write UDF in C language, and make any sofisticated 'printf' formatting...
--
Konstantin Andreev.

[Serge Rielau]

James wrote:

See the following example.

db2 =create table aaa (c1 decimal(10,3));
DB20000I The SQL command completed successfully.

db2 =insert into aaa values (200.03);
DB20000I The SQL command completed successfully.
db2 =select * from aaa;

C1
------------
200.030

1 record(s) selected.

db2 =select char(c1) from aaa;

1
------------
0000200.030

1 record(s) selected.

The result is not what I want. I want to the result to be 200.03 as
CHAR type. How can I do this convert?

Check if STRIP is available on your system.
Also check TRIM which is very similar on new DB2's
http://publib.boulder.ibm.com/infoce...c/r0023197.htm

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Tonkuma]

On Jun 2, 8:11 pm, Serge Rielau <[e-mail]:

James wrote:

See the following example.

db2 =create table aaa (c1 decimal(10,3));
DB20000I The SQL command completed successfully.

db2 =insert into aaa values (200.03);
DB20000I The SQL command completed successfully.
db2 =select * from aaa;

C1
------------
200.030

1 record(s) selected.

db2 =select char(c1) from aaa;

1
------------
0000200.030

1 record(s) selected.

The result is not what I want. I want to the result to be 200.03 as
CHAR type. How can I do this convert?

Check if STRIP is available on your system.
Also check TRIM which is very similar on new DB2'shttp://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2....

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -

It may be depend on client program whether leading and trailing ZEROs
are displayed.

But, wheather client program returns what format, if you are using DB2
9, you can use strip function to get the format you want.
Like this:
STRIP(CHAR(200.03), B, '0')
Otherwise, you can use LTRIM and RTRIM instead of STRIP functin.
TRANSLATE(RTRIM(LTRIM(TRANSLATE(CHAR(200.03), ' ', '0'))),'0',' ')