Double to Varchar

donraja_ht · #1 (**permalink**) 09-11-07, 09:59

Hello Gurus,

I have written a DB2 procedure which calculates some values using a formula and the result of the formula which should be and is a double value. Now my problem here is that i want to save this value in a table field which is varchar datatype. when i use the following

cast(cast(xFInterTotal as char(250)) as varchar(1024))

and cast it into varchar it saves the value in the varchar field, but this is not what i want, it saves the value in a uninterpreted form (1.104E4) where the actual double value is 4800.
How can i save 4800 as is in the varchar field. Please guide me in doing this.

Thanks and Regards
Harish

ARWinner · #2 (**permalink**) 09-11-07, 16:56

Cast the double to decimal first.

Andy

donraja_ht · #3 (**permalink**) 09-12-07, 01:53

Thanks Andy

Tried that but still it is giving me different result

In the varchar field it saves 11040.
where as in the double field it saves 4800

even though the calulation is same for both the values. I am not sure how this can be done.....Here is what i am doing whicle casting it to varchar

insert into Test_Proc values (xid, cast(cast(cast(xFInterTotal as decimal) as char(250)) as varchar(1024)), xResourceRate, 'xResourceRate')

here the second field is the varchar field and the third field is a double field.

Is there any other way to do this

stolze · #4 (**permalink**) 09-12-07, 03:51

Could you provide is with a small but full example where you get the wrong values? I guess that you have some other code that changes the value and is responsible for the differences.

Here you have an example how the output looks like for some casts:

Code:

$ db2 "values ( double(4800), decimal(double(4800), 8, 2), char(double(4800)), char(decimal(double(4800), 8, 2)) )"

1                        2          3                        4
------------------------ ---------- ------------------------ ----------
  +4.80000000000000E+003    4800.00 4.8E3                    004800.00

donraja_ht · #5 (**permalink**) 09-12-07, 05:26

Hi

I tried the following...

varchar(char(decimal(double(xResourceRate))))

and the double value of xResourceRate = 6240 and in the varchar field it saves the following : 000000000006240.

this is near to then the last time, but this is not the exact.

ARWinner · #6 (**permalink**) 09-12-07, 07:52

Quote:

Originally Posted by donraja_ht

Hi

I tried the following...

varchar(char(decimal(double(xResourceRate))))

and the double value of xResourceRate = 6240 and in the varchar field it saves the following : 000000000006240.

this is near to then the last time, but this is not the exact.

What datatype is xResourceRate?

Andy

donraja_ht · #7 (**permalink**) 09-12-07, 08:58

It is double i presume, the query for calculation goes here

declare xResourceRate DOUBLE;
select sum(COALESCE(resource_rate, 0) * xduration / xCount) into xResourceRate from table_assignment;

Thanks in advance