how could i add the value of below three fields of a table and store it into user define fields from DB2 query

I am looking for a solution to add the below three decimal fields and store them in a user defined field, these three fields belongs to 1 table and I need to sum the value of it. Kindly help!

I have tried the CAST and the convert function.

RECV_OH_QTY         DECIMAL(7,0)  
IN_PROC_OH_QTY      DECIMAL(7,0)  
CS_OH_QTY           DECIMAL(7,0)

Query below:

SELECT TB.ITEM_NO,
       TB.LOC,
       TB.RECV_OH_QTY,      
       TB.IN_PROC_OH_QTY,   
       TB.CS_OH_QTY,
       CAST(TB.RECV_OH_QTY + TB.IN_PROC_OH_QTY AS DECIMAL(7,0)) AS RES_VAR
FROM TB
WHERE  TB.ITEM_NO = 'xxxxxxxx'    
WITH UR;

Solution

If your table is TB for example, and you want SUM of those value for each TB.ITEM_NO so you might use this query:

SELECT ITEM_NO 
      ,CAST(SUM(TB.RECV_OH_QTY)+SUM(TB.IN_PROC_OH_QTY)+SUM(TB.CS_OH_QTY) AS FLOAT) AS SumValue
INTO YourNewTable  -- if you want to store it in another table
FROM TB
GROUP BY ITEM_NO

And if you want to calculate Sum of these values for all items so:

SELECT 
      CAST(SUM(TB.RECV_OH_QTY)+SUM(TB.IN_PROC_OH_QTY)+SUM(TB.CS_OH_QTY) AS FLOAT)
INTO YourNewTable  -- if you want to store it in another table
FROM TB