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

2 thoughts on “DB2 Decimal Division Example

  1. Pingback: DB2 Decimal Division Example | Ragnarok Connection
  2. Pingback: DB2 Decimal Division Instance | Ragnarok Connection

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s