Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I'm having an issue on the query below. The issue is:

Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type int

The issue is located in this line of the code below.

INNER JOIN Test t ON a.AccountNO <> t.AccountNo

Any ideas?

WITH TEST AS
(
    SELECT DISTINCT AccountNo, SUBSTRING(APartyNO, 3, LEN(APartyNo)) AS APartyNoCut
    FROM (SELECT DISTINCT AccountNo, APartyNo 
            FROM prf_BatchItems 
           WHERE BatchID = 127 
             AND Code1 = 'DEDF' 
             AND APartyNo NOT LIKE '04%'
             AND (   Left(APartyNo,2) = '02' 
                  OR Left(APartyNo,2) = '03' 
                  OR Left(APartyNo,2) = '07'
                  OR Left(APartyNo,2) = '08')
        GROUP BY AccountNo, APartyNo
    UNION
    SELECT DISTINCT AccountNo, APartyNo 
      FROM prf_BatchItemAdditionalAPartyNos 
     WHERE BatchID = 127 
  GROUP BY AccountNo, APartyNo) a
)
SELECT Code2, TypeName, CallTypeName, --SUM([Count]), 
        SUM(Duration), SUM(CostGrossExGST)
FROM
( 
    SELECT 'WITHOUT STD' AS Type, 
            Code2, b.TypeName, CallTypeName,
            --SUM([COunt]) AS Count,  
            SUM(DurationSecond) AS Duration, 
            SUM(a.CostGrossExGSt) AS CostGrossExGST 
    FROM prf_BatchItems a 
            INNER JOIN (SELECT * FROM dbo.prf_BillTypeCodes WHERE BillTypeID = 2)  b ON a.Code2 = b.BillCodeName
            INNER JOIN Test t ON a.AccountNO <> t.AccountNo 
    where BatchID = 127 
            AND Code1 = 'DC'  
            AND ServiceTypeName = 'MobileNet' 
            AND CallTypeName = 'National Direct Dialled calls'
            AND (LEFT(BPartyNo,2) <> '02' AND LEFT(BPartyNo,2) <> '03' OR LEFT(BPartyNo,2) <> '07' OR LEFT(BPartyNo,2) <> '08')
            AND BPartyNo NOT LIKE '04%'
            AND BPartyNo NOT LIKE '1%'
    GROUP BY --a.AccountNo, 
            Code2,  b.TypeName, CallTypeName) zz 
GROUP BY Code2,  TypeName, CallTypeName
share|improve this question

2 Answers 2

up vote 3 down vote accepted

looks like one of the AccountNO in this line INNER JOIN Test t ON a.AccountNO <> t.AccountNo is not an integer and has a valuse that can't be converted to an integer

what is the data type of the column in both tables

share|improve this answer
    
They both are VARCHAR(32) –  dcalliances Aug 10 '10 at 0:55
    
in that case run all queries one by one, start with one query and continue until you run all of them, the one that fails will have the problem, then investigate what is stored in those columns –  SQLMenace Aug 10 '10 at 1:01
    
You are right! The issue is not really on that statement. I do have SUM(DurationSecond) and it's bigger than INT after joining. :) Thanks again. –  dcalliances Aug 10 '10 at 1:15

Some times, the values in the sql query have values larger than an integer can hold, so change the type of data from int (integer ) to Bigint, problem is solved.

share|improve this answer
    
please give the limit on the integer for SQLServer –  user1428716 Feb 26 '13 at 3:00
    
-2,147,483,647 to 2,147,483,647 msdn.microsoft.com/en-us/library/ms187745.aspx –  jjzd2w Apr 23 '13 at 18:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.