P: n/a

Hi I am writing a select statement that has an arithmetic function
inside a case statement that uses logic to decide whether to divide or
multiply and when I run the arithmetic statements outside the case
statement they work fine, but blow up with an overflow error in the
case statement. The select looks like:
SELECT a.acct_cd,
a.crrncy_cd,
a.mkt_val,
c.rate,
a.mkt_val / c.mrrate,
a.mkt_val * c.mrrate,
CASE
WHEN b.mcopr = 'M' THEN a.mkt_val / c.mrrate
WHEN b.mcopr = 'D' THEN a.mkt_val * c.mrrate
END
FROM account a,
exchange_calcs b,
exchange_rates c
WHERE b.currency = a.crrncy_cd
AND c.currency = b.currency
AND c.datetime = 1070531;
If I comment out the case statement above it works perfectly, with the
case statement active it gives the following error:
[SQL0802] Data conversion or data mapping error. Cause . . . . . :
Error type 1 has occurred. Error types and their meanings are: 1 
Arithmetic overflow. 2  Floating point overflow. 3  Floating point
underflow. 4  Floating point conversion error. 5  Not an exact
result. 6  Numeric data that is not valid. 7  Doublebyte
character set (DBCS) or UTF8 data that is not valid. 8  Division by
zero. 9  Hash value cannot be computed for the requested query. 10
 Userdefined function returned a mapping error. 11  Not valid
length found in a varyinglength column returned from an array result
set. 12  Result of a concatenation operation on a varyinglength
field exceeded the maximum allowed length of the result type. If the
error occurred when assigning a value to a host variable of a FETCH,
embedded SELECT, SET, or VALUES INTO statement, the host variable name
is *N and the relative position of the host variable in the INTO
clause is 0. If the host variable name is *N, the error occurred when
attempting to resolve a search condition. If more than one data
mapping error occurred, this is a description of the first error that
occurred. For a description of any other data mapping errors, see the
previously listed messages in the job log. Recovery . . . : The
error was caused by data that was not valid or that was too large.
Look at the previously listed messages in the job log (DSPJOBLOG
command) or press F10 (Display messages in job log) on this display to
determine what row and columns were involved in the error. Correct
the data and then try the request again.  
Share this Question
P: n/a
 je****@gmail.com wrote:
Hi I am writing a select statement that has an arithmetic function
inside a case statement that uses logic to decide whether to divide or
multiply and when I run the arithmetic statements outside the case
statement they work fine, but blow up with an overflow error in the
case statement. The select looks like:
SELECT a.acct_cd,
a.crrncy_cd,
a.mkt_val,
c.rate,
a.mkt_val / c.mrrate,
a.mkt_val * c.mrrate,
CASE
WHEN b.mcopr = 'M' THEN a.mkt_val / c.mrrate
WHEN b.mcopr = 'D' THEN a.mkt_val * c.mrrate
END
What are the datatypes of mkt_val and mrrate?
Note that the caseEXPRESSION (:)) has to derive a final result type
for both expressions. Maybe the overflow occurs in that assignment.
Describe the columns for the /, * and CASE entries.
What version of DB2 for zOS is this btw?
Cheers
Serge

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab  
P: n/a

What are the datatypes of mkt_val and mrrate?
Note that the caseEXPRESSION (:)) has to derive a final result type
for both expressions. Maybe the overflow occurs in that assignment.
Describe the columns for the /, * and CASE entries.
mkt_val is a DECIMAL(17,2) and mrrate is a DECIMAL(15, 9).
What version of DB2 for zOS is this btw?
I am not sure what version of DB2 this is but it is running on an
AS400.
Jeremy.  
P: n/a

According to the manual "DB2 UDB for iSeries SQL Reference Version 5
Release 4".
(Note 1)
The symbol mp denotes the maximum precision.
The value of mp is 63 if:
 either w or y is greater than 31, or
 a value of 63 was explicitly specified for the maximum precision.
Otherwise, the value of mp is 31.
The symbol ms denotes the maximum scale.
The default value of ms is 31. ms can be explicitly set to any number
from 0 to the maximum precision.
The symbol mds denotes the minimum divide scale.
The default value of mds is 0. mds can be explicitly set to any number
from 0 to the maximum scale.
(Following calculations, I assumed mp, ms and mds are default(i.e. 31,
31, 0).)
DECIMAL(p,s) / DECIMAL(p',s')
=: ( (ps+s') + max(mds, min(ms, mp  (ps+s') ) ), max(mds, min(ms,
mp  (ps+s') ) ) )
Apply the rule for this case.
a.mkt_val / c.mrrate
=: DECIMAL(17,2) / DECIMAL(15, 9)
=: ( (172+9) + MAX(0, MIN(31, 31(172+9))), MAX(0, MIN(31,31
(172+9))) )
=: ( (24 + MAX(0, MIN(31, 7)), MAX(0, MIN(31, 7)) )
=: DECIMAL( 31, 7 )
(Integer part is 24 digits, fractional part is 7 digits)
DECIMAL(p,s) * DECIMAL(p',s')
=: ( min (mp,p+p), min(ms,s+s) )
Apply the rule for this case.
a.mkt_val * c.mrrate
=: DECIMAL(17,2) / DECIMAL(15, 9)
=: DECIMAL( MIN(31,17+15), MIN(31,2+9) )
=: DECIMAL( 31, 11 )
(Integer part is 20 digits, fractional part is 11 digits)
CASE expression
One operand column is ...
DECIMAL(w,x)
Another operand column is ...
DECIMAL(y,z) or NUMERIC(y,z,)
The data type of the result column is...
DECIMAL(p,s) where p = min(mp, max(x,z)+max(wx,yz)) s = max(x,z)
mp = 31 or 63 (See Note 1)
Apply the rule for this case.
a.mkt_val / c.mrrate
=: DECIMAL(w,x) =: DECIMAL(31,7)
a.mkt_val * c.mrrate
=: DECIMAL(y,z) =: DECIMAL(31,11)
Result is
DECIMAL( min(mp, max(x,z)+max(wx,yz)), max(x,z) )
=: DECIMAL( MIN(31, MAX(7,11)+MAX(317,3111)), MAX(7,11) )
=: DECIMAL( MIN(31, 11+24), 11 )
=: DECIMAL( 31, 11 )
(Integer part is 20 digits, fractional part is 11 digits)
(I might made some mistake. Because, calculations are complex.)
Anyway, my guess is that "a.mkt_val / c.mrrate" made overflow at the
time used in CASE expression.
Because, if "a.mkt_val / c.mrrate" is used in a column, result is
DECIMAL( 31, 7 ) (Integer part is 24 digits, fractional part is 7
digits)
While, if it is used in CASE expression with "a.mkt_val * c.mrrate",
result is
DECIMAL( 31, 11 ) (Integer part is 20 digits, fractional part is 11
digits)   This discussion thread is closed Replies have been disabled for this discussion.   Question stats  viewed: 8530
 replies: 3
 date asked: Jun 1 '07
