[RESOLVED] Arithmatic Overflow Error On DB2 Query

[Hack]

One of the folks I work with runs a lot of queries against DB2 tables. He is using MS Access 2000 and linking to the DB2 tables.

On this particular query, he is receiving an Arthmatic Over Flow error and has asked if I could provide some possibilities with respect to why it is occuring. I image there are a number of questions that need to be answered but, I don't even know what the questions are. Here is the query

SELECT CDFA0000.CTFS0010.FACILITY_CD, CDFA0000.CTFS0010.RECORD_TYPE, 
CDFA0000.CTFS0020.FEE_BASED_IND, 
Sum((CDFA0000.CTFS0010.CONTRACT_CHARGES*
(CDFA0000.CTFS0020.COVERED_CHARGES/CDFA0000.CTFS0010.COVERED_CHARGES))) AS Contract_chg, CDFA0000.CTFS0020.PROCEDURE_CTG, 
CDFA0000.CTFS0020.REV_OFF_SUR_IND
FROM CDFA0000.CTFS0010 INNER JOIN CDFA0000.CTFS0020 ON 
(CDFA0000.CTFS0010.FRONT_SHEET_QTR = 
CDFA0000.CTFS0020.FRONT_SHEET_QTR) AND 
(CDFA0000.CTFS0010.RECORD_ID = CDFA0000.CTFS0020.RECORD_ID) AND 
(CDFA0000.CTFS0010.FACILITY_CD = CDFA0000.CTFS0020.FACILITY_CD)
WHERE
CDFA0000.CTFS0010.ADMISSION_DT Between '10/01/2004' and '06/30/2005' And
CDFA0000.CTFS0010.RECORD_TYPE In ('NP') AND
CDFA0000.CTFS0010.FYE_END_DT ='09/30/2005' AND
CDFA0000.CTFS0010.FACILITY_CD='00179' AND 
CDFA0000.CTFS0010.FRONT_SHEET_QTR='53' AND
CDFA0000.CTFS0010.INPAT_OUTPAT_IND='O' AND
CDFA0000.CTFS0010.COVERED_CHARGES<>0
GROUP BY CDFA0000.CTFS0010.FACILITY_CD, CDFA0000.CTFS0010.RECORD_TYPE, CDFA0000.CTFS0020.FEE_BASED_IND, 
CDFA0000.CTFS0020.PROCEDURE_CTG, CDFA0000.CTFS0020.REV_OFF_SUR_IND

Here is the error message:

[si_the_geek]

Ok.. well here's what I assume is the offending section:

Sum((CDFA0000.CTFS0010.CONTRACT_CHARGES*
(CDFA0000.CTFS0020.COVERED_CHARGES/CDFA0000.CTFS0010.COVERED_CHARGES))) AS Contract_chg

Which means that we have 3 fields to worry abou:
CDFA0000.CTFS0010.CONTRACT_CHARGES
CDFA0000.CTFS0020.COVERED_CHARGES
CDFA0000.CTFS0010.COVERED_CHARGES

What data types are they (in DB2, and in Access), and what is the "likely" data for each?

[Hack]

Ok.. well here's what I assume is the offending section:


Which means that we have 3 fields to worry abou:
CDFA0000.CTFS0010.CONTRACT_CHARGES
CDFA0000.CTFS0020.COVERED_CHARGES
CDFA0000.CTFS0010.COVERED_CHARGES

What data types are they (in DB2, and in Access), and what is the "likely" data for each?

In DB2 and in Access the fields are defined as decimal (9,2). The expected values are charge amounts (dollars and cents) – ranging from a few bucks up to a million in extreme cases. My guess would be the average charge is somewhere around $800 per record. The charge values in the CTFS0010 table are totals for the record, and the charge values in CTFS0020 are the individual line amounts that make up the total.

[si_the_geek]

Ok.. it sounds like the "extreme" case is probably the one causing the problem, as that barely fits into the data type as it is.

I would recommend basically a Cast on the CONTRACT_CHARGES field, which should make the query use a 'bigger' data type. I would guess that this needs to be Access syntax, so try changing to one of these:

Sum((CCur(CDFA0000.CTFS0010.CONTRACT_CHARGES)*

Sum((Format(CDFA0000.CTFS0010.CONTRACT_CHARGES,"##,###,###.##) *

I guess it is possible that one or more of the fields has a Null - in which case an error would occur (I don't think it should be this one, but you can't be sure with different DB's!).


As an alternative, the equation could be re-arranged (the result will be different, but it may be what is actually wanted)..

(CDFA0000.CTFS0010.CONTRACT_CHARGES*
(Sum(CDFA0000.CTFS0020.COVERED_CHARGES)/CDFA0000.CTFS0010.COVERED_CHARGES)) AS Contract_chg,

[Hack]

Thanks Si.

I've passed along your questions and responses.

Apparently what you have posted tickled some brain cells as I just got a email back that started out with:

"Ohhhhhhhhhhhh....I think I see what he (meaning you) is saying. Thanks Robert, we will give it shot and let you know."

[Hack]

si: I just heard back from the DB2 people and they tell me that your suggestions worked, and they are querying happily away.

Thanks.