Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007
    Posts
    21

    Unanswered: decimal fields in db2

    Hi,
    I'm querying 3 fields which are based on a field of decimal (18 ,2) (the cobol equivalent field is pic s9(16) v 9(2) comp-3.

    the fields are like this,,,

    select sno,
    sum(amount),
    decimal(sum(round(amount * 0.02,2)),18,2),
    decimal(sum(round(amount * 0.002/24),2)),18,2)

    from tablea
    group by sno.

    I want to change the fields to multiply the amount by a variable field ws-var instead of 0.02. query look like this...

    select sno,
    sum(amount),
    decimal(sum(round(amount * :ws-var,2)),18,2),
    decimal(sum(round(amount * :ws-var/24),2)),18,2)
    from tablea
    group by sno.


    I'm getting error :ws-var is not defined as variable.

    I tried calculating the fields separately using cobol working storage variables of PICTURE type pic s9(16) v 9(2) comp-3.
    and selecting them directly. Now I'm getting the error -310 and SQLSTATE 22023.

    Can somebody help me getting these fields using db2 query?

    Thanks,
    Anant

  2. #2
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    First make sure that you populated WS-VAR with a valid packed decimal value before executing the query. If you still have a problem, try using the CAST function on the host variable, CAST(:WS-VAR as DECIMAL(18,2))

  3. #3
    Join Date
    Nov 2007
    Posts
    21

    decimal fields in db2

    Thanks,

    First I used PIC s9(4)v9(2) comp3 later I changed to 9(4)v9(4) comp3.
    Now it's working fine.
    Not sure is there any calculation to use the corrent size of the picture clause field. I thought the data might be truncated if the precision size is less.

Posting Permissions

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