By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 1,012 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

Arithmetic overflow/other exception - what to do to find the source?

P: n/a
I'm building a revenue management tool and this requires some
understanding of how good my forecast accuracy is.

Therefore I'm trying to collect the mean absolute percentage error
(MAPE) on a set of predictions that I've been making; I have a table
that stores the actual result that occurred each day, and another that
stores the predictions that I've made on particular days for each day.
(All these predictions are integers).

My SQL is as follows:

INSERT INTO aps_ym_mape
(site,
distance,
customer_type,
gtd_mape,
ltw_mape)
select y.site,
days(y.date) - days(prediction_date) distance,
'II',
round(AVG(decimal(abs(gtd_prediction - cars), 8, 3)/cars), 4),
round(AVG(decimal(abs(ltw_prediction - cars), 8, 3)/cars) , 4)
from (SELECT prediction_date, date, site, sum(gtd_prediction)
gtd_prediction, sum(ltw_prediction) ltw_prediction
FROM aps_ym
WHERE date >= '01.08.2004'
AND customer_type != '~~'
AND prediction_date between date - 60 days and date
GROUP BY prediction_date, date, site
) as y,
(SELECT date, site, sum(cars) cars
FROM airparks_occ o
WHERE date >= '01.08.2004'
GROUP BY date, site
) as o
where o.site = y.site
AND o.date = y.date
AND y.date <= y.prediction_date
GROUP BY y.site,
days(y.date) - days(prediction_date),
'II';

What this should do is put the MAPE for each predictive method into
another table (the MAPEs go into a decimal(7, 4) field).

However, I'm getting
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0802N Arithmetic overflow or other arithmetic exception occurred.

What's the best way for me to identify where the exception is
occurring? Could this provoked by MAPE being greater than 999.9999 ?
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
James,

Use the -a option on the CLP and pass me the SQLCA output.
I may be able to deduce soem more info from the error source.
In general teh best way to track these kinds of errors is divide and
conquer.

Cheers
Serge
Nov 12 '05 #2

P: n/a
Thanks. Although about half an hour after I posted this, I realised
my error - one of the subqueries has a missing join, and thus the
whole thing was bringing back values that would overflow a decimal(7,
4) field.

Cheers

James

Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<2s*************@uni-berlin.de>...
James,

Use the -a option on the CLP and pass me the SQLCA output.
I may be able to deduce soem more info from the error source.
In general teh best way to track these kinds of errors is divide and
conquer.

Cheers
Serge

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.