SEO

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 ?

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

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 <srielau@ca.ibm.com> wrote in message news:<2si74uF1kq2b8U1@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