Skip to Content
0
Jul 22, 2013 at 08:26 PM

Numeric overflow error when querying large volume of data

1819 Views

Hi folks,

I have a calculation view that runs against large BSEG table and joins to some other tables, and it works fine until you select a larger date range. For example 1 year works fine but if you choose 2 years of history (based upon BUDAT) we get an 'numeric overflow' error. So I started dissecting the view and stripped out all of the outputted fields until it just outputted a single field and I got success for large volumes of data for this single field. Next I started slowly adding fields back into the output until the numeric overflow error returned; I then discovered some fields had a certain length in the source table BSEG such as NVARCHAR(1) and were defined wrong in the view output as NVARCHAR(2). While this is baffling to me why this would cause a numeric overflow, I fixed these mismatches and those fields are now fine. However there are some calculated measure fields that are still causing problems;

Example:

CASE WHEN B.SHKZG = 'H' THEN (B.DMBTR * -1) ELSE (B.DMBTR * 1) END AS "AMOUNT",

This case statement is simply checking value of credit/debit indicator and changing the number to a positive or a negative value and then outputting as AMOUNT. This AMOUNT field is type DECIMAL 13,2 which happens to be the same exact definition as the source field DMBTR. So I'm not sure why this simple case statement would cause a numeric overflow when selecting a larger date range, yet is running ok with a smaller date range.

I've started analyzing the visual execution plan, which is quite extensive and will take some time, but in the meantime I figured I would post this question in case somebody here see's an obvious problem with this case statement.

Thanks!

-Patrick