CODEWITHSUNDEEP
sqlsql-server
Mark O Keeffe
Mark O Keeffe

Reputation: 301

Different than normal -- overflowed an int column. Maximum integer value exceeded

I'm aware of the data size limit of an Int but have an error throw like this "The conversion of the varchar value '51805030400' overflowed an int column. Maximum integer value exceeded." from sql server

Thing is, that the row throwing the error should be excluded with the where clause. When I run the query for the policy that throws the error excluding the where claus it comes back, but if I include the where claus it doesn't. My question is, howcome this policy is throwing the error, I was under the impression that the where claus gets executed before the select. The highest value of the expected values to be returned is 65,126.

In the select statement I'm coverting a decimal(11,2) to an Int.

Here is the code

     SELECT DISTINCT  
        f.pol_id
        ,   F.trftxn_tot_ordr_amt *100
         ,  CONVERT(int,left(F.trftxn_tot_ordr_amt *100,len(F.trftxn_tot_ordr_amt *100)-3))

       FROM dbo.tr_ftran_header_t F
           INNER JOIN dbo.tr_policy_t  P
                 ON F.pol_id    = P.pol_id


       WHERE 
            (   (F.trftxn_proc_dt > '2014-08-08'
                 AND F.trtxn_stat_cd IN ('PROCESSD', 'REVERSAL')
                )                  
            OR (trftxn_rev_dt > '2014-08-08'
                AND F.trtxn_stat_cd = 'REVERSED')   
             )

Any help on why its executing like this, I know I can change to put in Bigint instead of Int to overcome the issue.

thanks

Upvotes: 0

Views: 1488

Answers (2)

kristof
kristof

Reputation: 53834

As already stated by Gordon there is no guaranteed order of execution in SQL Server, the Query Optimizer can rewrite the query execution plan in any way it sees as the best.

In your case it decides to transform the column before filtering the data, and that is causing the totally unexpected conversion error. Unfortunately it is a known "feaure" or rather bug of SQL Server and as for now you need to find a workaround for it.

This issue was even raised with Microsoft with a suggestion how to fix it by SQL guru Erland Sommarskog

Going back to your code, if you are running sql server 2012 and up you can use TRY_CONVERT in place of simple CONVERT. If you are on the older version you will need to resort to using CASE expression.

SQL Server 2012 and newer

SELECT DISTINCT  
    ...
    ,TRY_ CONVERT(int,left(F.trftxn_tot_ordr_amt *100,len(F.trftxn_tot_ordr_amt *100)-3))
    ...

Older versions

SELECT DISTINCT  
    ...
    ,case when <the condition that will ensure a valid conversion> 
        then CONVERT(int,left(F.trftxn_tot_ordr_amt *100,len(F.trftxn_tot_ordr_amt *100)-3))
    end

BTW

You can convert DECIMAL to INT directly. when converting from DECIMAL (also known as NUMERIC) to INT the decimals are truncated. so e.g. convert(int,1.25) will result in 1 so will convert(int,1.99)

In your query where you want the result multiplied by 100 you can do
CONVERT(int,F.trftxn_tot_ordr_amt *100) to achieve the same results as CONVERT(int,left(F.trftxn_tot_ordr_amt *100,len(F.trftxn_tot_ordr_amt *100)-3))

That is assuming that F.trftxn_tot_ordr_amt as DECIMAL(11,2)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270081

SQL Server rearranges the processing of statements. There is no guarantee that the where clause is evaluated before the expressions in the select. This can be confusing. And I personally wish the error handling would follow the logical processing defined here.

Unfortunately, you cannot fix this using a subquery or CTE. The only sure way to normally fix the problem is using case. I usually encounter this problem with conversions (which try_convert() now fixes). This code might fail:

select cast(col as float)
from t
where isnumeric(col) = 1;

The solution is:

select (case when isnumeric(col) = 1 then cast(col as float) end)
from t
where isnumeric(col) = 1;

The case statement does guarantee processing order in most cases (there are some exceptions when using aggregation functions).

Upvotes: 1

Related Questions