numeric overfllow error

Database
Enthusiast

numeric overfllow error

Hi All,

 

THis error looks weried for me. numeric overfllow  error is coming while  joinig new table.

   select
   date
 ,sum(A.tran_amt) as tran_amt 
,sum(A.tran_cnt)   as tran_cnt
 from factmarch  a
 left outer join  merch_lu      B
      on A.STMT_DESC = B.STMT_DESC
    where
 and tran_cnt < 5000
group by 1

 

 

when i  comment out left outer join its working fine. when i join with  lookup table its throwing

numeric  overflow occured during computation- error

Can any one explain the problem plz.

 

thanks

10 REPLIES
Senior Apprentice

Re: numeric overfllow error

Hi,

 

What is probably happening is that your join condition results in a "1 to many" relationship between the 'factmarch' and the 'merch_lu'. Specifically, with that join in place one or more rows in table factmarch are now joining to multiple rows in 'merch_lu'.

 

This results in more values for tran_amt and tran_cnt being aggregated causing one of them to overflow.

 

Unless this is simplified sql...

What is the purpose of this join? Given that there are no columns in the select list or selection criteria from the 'merch_lu table then I don't think it adds anything (apart from an error Smiley Happy ).

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: numeric overfllow error

i removed columns from lkp table. we need that table ....

Enthusiast

Re: numeric overfllow error

wht is the best way to get rid of this error

Senior Apprentice

Re: numeric overfllow error

Hi,

 

Sorry, but I'm not sure I understand your comment about 'removing columns'.

 

Let's try another approach. To understand the cause of your 'numeric overflow' error try the following two queries.

 select count(*)
 from factmarch  a
    where tran_cnt < 5000;

This first query will give the number of rows which are being aggregated.

 

 select count(*)
 from factmarch  a
 left outer join  merch_lu      B
      on A.STMT_DESC = B.STMT_DESC
    where tran_cnt < 5000;

This second query will give the number of rows which are being aggregated.

 

I think you'll find that the two queries give a different number of rows - which is due to the '1 to many' relationship between the two tables on the join column (STMT_DESC).

 

Even if the query with the join were to complete, I suspect that the resulting values are not correct.

 

I think you'll probably need to add additional join and/or selection criteria so that a qualifying row in table factmarch only joins to one row in the merch_lu table.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: numeric overfllow error

sure will try 

Enthusiast

Re: numeric overfllow error

First query i got  Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 552 StartFragment: 314 EndFragment: 520 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet

41,632,551  rows

2ND QUERY I GOT

 478,014,534 ROWS .

 

 

Senior Apprentice

Re: numeric overfllow error

And those results show the base cause of your 'numeric overflow' error.

 

Adding in the join gives a "1 to many" relationship, causing more rows (@11 fold increase) and therefore more values to be aggregated.

 

Assuming that you need the join then as I said previously, you probably need to add additional join and/or selection criteria.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Senior Apprentice

Re: numeric overfllow error

Hi,

 

To avoid the error typically you'll use a CAST function, but in the correct place. Try:

,sum(CAST(A.tran_cnt AS FLOAT))   as tran_cnt

Note that I've used a FLOAT data type. You may be better using BIGINT or a DECIMAL column, it really depends on the data type of the base column.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: numeric overfllow error

Dave i fixed this issue by defining proper join condition. Thanks for your help on this .

And one more question  cycle and no cycle wht is difference b/w them

      ID_num INTEGER GENERATED ALWAYS AS IDENTITY

           (START WITH -214748364

            INCREMENT BY 1

            MINVALUE -2147483647

            MAXVALUE 2147483647

            CYCLE)

 

and

 

      ID_num INTEGER GENERATED ALWAYS AS IDENTITY
           (START WITH 1 
            INCREMENT BY 1 
            MINVALUE -2147483647 
            MAXVALUE 2147483647 
            NO CYCLE),

 Thanks