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

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))

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.