Numeric overflow error while calculation

[former_member212706]

Dear Experts,

     I have created a calculation view. the var_out statement looks like

  select a,b,.....

,

CASE WHEN "Field1"=1

THEN 1/7

ELSE "Field1"*"Field2"/"Field3"

END  AS "Field4",

from table a, table b

I get the following error

314]: numeric overflow:  [314] INF126293.PROC_TEST1: line 203 col 1 (at pos 4849): numeric overflow exception: numeric overflow: search table error:  [6944] AttributeEngine: overflow in numeric calculation;JECalculate pop12(setResultFromTo(0, 2147483647),addExpression('box ( "schema1.table b.fiels 1" , 1 , ( decfloat ( 1 ) / decfloat ( 7 ) ) , ( decfloat ( ( "schema1._SYS_CE_LLANG_5162F1DF92976332E10000000A9CF502_TMP:IT_0x7f77228e13500x7f76ee07c800:1@vid:2.VAL0000" * "INF126293._SYS_CE_LLANG_5162F1DF92976332E10000000A9CF502_TMP:IT_0x7f77228e13500x7f76ee07c800:1@vid:2.BILL_OFFPEAK" ) ) / decfloat ( "schema1"._SYS_CE_LLANG_5162F1DF92976332E10000000A9CF502_TMP:IT_0x7f77228e13500x7f76ee07c800:1@vid:2.USAGE_OFFPEAK_TOTAL" ) ) )'),addViewAttribute('_CV0x7f77228e2f40'))

what could be the reason behind the error

Thanks,

Rajesh.

[henrique_pinto · Accepted solution]

Can Field3 be 0?

If yes, you'll get an overflow all the time...

You need to do some treatment like WHEN "Field3"<>0 THEN... inside your CASE.

Cheers,

Henrique.

[VijayCR]

This message was moderated.

[lbreddemann]

Hi Rajesh,

please check the data type definition for the result of your calc. view.

What data type have you picked for the result of the division?

- Lars

[former_member212706]

The result data type is decimal(16,5).

I also tried to create a procedure with the same code. even there i get the same problem..

[lbreddemann]

Rajesh,

although you defined the result data type as decimal in the output structure, you don't take care of actually making sure that the result fits this data type.

So, for your code, you might go with something like this:

select a,b,.....

,

to_decimal (CASE

                    WHEN "Field1"=1  THEN

                                   1/7

                    ELSE

                              "Field1"*"Field2"/"Field3"

                    END,

                    16, 5)  AS "Field4",

from

     table a, table b

- Lars