Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2007
    Posts
    56

    Unanswered: Double to Varchar

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Cast the double to decimal first.

    Andy

  3. #3
    Join Date
    Sep 2007
    Posts
    56
    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

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Sep 2007
    Posts
    56
    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.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  7. #7
    Join Date
    Sep 2007
    Posts
    56
    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

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am afraid that that is the best DB2 is going to do for you. Your only alternative is to write a UDF that removes the leading zeros.

    Andy

  9. #9
    Join Date
    Sep 2007
    Posts
    56
    Thanks Andy for your Help :-) will have to look for a alternative solution

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •