DB2 Decimal Division Example

Decimal is one of the most commonly used data type in DB2 database. The difficulty with DECIMAL arises when division is the case. You can come through errors such negative scale or unexpected values. I prefer to use FLOAT casting and recasting to refrain from this kind of errors.

CREATE PROCEDURE DIVISION_EXAMPLE (IN BALANCE_EUR DECIMAL(20,2),
IN EXC_EUR_TO_USD DECIMAL(12,5),
OUT BALANCE_USD DECIMAL(20,2))
RESULT SETS 1
LANGUAGE SQL 

P1: BEGIN
DECLARE V_BALANCE_EUR FLOAT;
DECLARE V_EXC_EUR_TO_USD FLOAT;
DECLARE V_DIVISION FLOAT;

SET V_BALANCE_EUR = BALANCE_EUR;
SET V_EXC_EUR_TO_USD = EXC_EUR_TO_USD;
SET V_DIVISION = BALANCE_EUR / EXC_EUR_TO_USD;
SET BALANCE_USD = ROUND(V_DIVISION ,2);

END P1
Advertisements