Skip to Content
avatar image
Former Member

Numeric overflow error while calculation

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Apr 12, 2013 at 02:51 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 12, 2013 at 06:32 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Apr 25, 2016 at 07:33 PM

    This message was moderated.

    Add comment
    10|10000 characters needed characters exceeded