Arithmetic Error converting Varchar in SQL Server query

Question

I am receiving an error

Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting numeric to data type numeric

when trying to execute the following SQL. Any ideas on what I can do to correct the issue? I am sure it is related to the VarChar but I am not sure what I can do to fix it.

SELECT        
   CONVERT(VARCHAR(10), t1.date_transaction, 10) AS summaryDate,      
   t1.qb_merchant_id, t1.store_id, 
   SUM(t1.amount) - ISNULL(SUM(te.TransAmount), 0) + 
       ISNULL(SUM(te.OriginalTransAmount), 0) AS grossSales, 
   SUM(CASE WHEN t1.voided = '1' THEN 0 ELSE t1.amount END) AS netSales,
   (SELECT COUNT(*) AS Expr1
    FROM dbo.sas_transactions AS t3
    WHERE (voided = '1') AND (qb_merchant_id = t1.qb_merchant_id) AND (store_id = t1.store_id) AND (CONVERT(VARCHAR(10), date_transaction, 10) = CONVERT(VARCHAR(10), t1.date_transaction, 10))) AS voids, 
   SUM(ISNULL(te.Commission, CASE WHEN t1.voided = '1' THEN 0 ELSE t1.amount_commission END)) AS totalCommissions, 
   SUM(CASE WHEN t1.voided = '1' THEN 0 ELSE t1.amount_sas END) AS totalShareASale,
   (SELECT COUNT(*) AS Expr1
    FROM dbo.sas_transactions AS t2
    WHERE (transaction_type = 'Sale' OR transaction_type = 'Manual Sale') AND (qb_merchant_id = t1.qb_merchant_id) AND (store_id = t1.store_id) AND (voided <> '1') AND (CONVERT(VARCHAR(10), date_transaction, 10) = CONVERT(VARCHAR(10), t1.date_transaction, 10))) AS numOrders,
   (SELECT COUNT(*) AS Expr1
    FROM dbo.sas_transactions AS t4
    WHERE (transaction_type = 'Lead') AND (qb_merchant_id = t1.qb_merchant_id) AND (store_id = t1.store_id) AND (voided <> '1') AND (CONVERT(VARCHAR(10), date_transaction, 10) = CONVERT(VARCHAR(10), t1.date_transaction, 10))) AS numLeads,
   (SELECT COUNT(DISTINCT user_id) AS Expr1
    FROM dbo.sas_transactions AS t2
    WHERE (transaction_type = 'Sale' OR transaction_type = 'Manual Sale') AND (qb_merchant_id = t1.qb_merchant_id) AND (store_id = t1.store_id) AND (voided <> '1') AND (CONVERT(VARCHAR(10), date_transaction, 10) = CONVERT(VARCHAR(10), t1.date_transaction, 10))) AS numAffiliates
FROM            
    dbo.sas_transactions AS t1 
LEFT OUTER JOIN
    dbo.sas_transaction_edits AS te ON t1.transaction_id =  te.TransID
WHERE        
    (t1.transaction_type <> 'Manual Deposit') 
    AND (t1.transaction_type <> 'Autodeposit')
GROUP BY 
    CONVERT(VARCHAR(10), t1.date_transaction, 10), t1.qb_merchant_id, t1.store_id

I am getting the same error with the following one as well:

    SELECT        CONVERT(VARCHAR(10), t1.date_transaction, 10) AS summaryDate, t1.qb_merchant_id, t1.store_id, SUM(t1.amount) - ISNULL(SUM(te.TransAmount), 0) + ISNULL(SUM(te.OriginalTransAmount), 0) AS grossSales, 
                     SUM(CASE WHEN t1.voided = '1' THEN 0 ELSE t1.amount END) AS netSales,
                         (SELECT        COUNT(*) AS Expr1
                           FROM            dbo.sas_transactions AS t3
                           WHERE        (voided = '1') AND (qb_merchant_id = t1.qb_merchant_id) AND (store_id = t1.store_id) AND (CONVERT(VARCHAR(10), date_transaction, 10) = CONVERT(VARCHAR(10), t1.date_transaction, 10))) 
                     AS voids, SUM(ISNULL(te.Commission, CASE WHEN t1.voided = '1' THEN 0 ELSE t1.amount_commission END)) AS totalCommissions, SUM(CASE WHEN t1.voided = '1' THEN 0 ELSE t1.amount_sas END) 
                     AS totalShareASale,
                         (SELECT        COUNT(*) AS Expr1
                           FROM            dbo.sas_transactions AS t2
                           WHERE        (transaction_type = 'Sale' OR
                                                     transaction_type = 'Manual Sale') AND (qb_merchant_id = t1.qb_merchant_id) AND (store_id = t1.store_id) AND (voided <> '1') AND (CONVERT(VARCHAR(10), date_transaction, 10) 
                                                     = CONVERT(VARCHAR(10), t1.date_transaction, 10))) AS numOrders,
                         (SELECT        COUNT(*) AS Expr1
                           FROM            dbo.sas_transactions AS t4
                           WHERE        (transaction_type = 'Lead') AND (qb_merchant_id = t1.qb_merchant_id) AND (store_id = t1.store_id) AND (voided <> '1') AND (CONVERT(VARCHAR(10), date_transaction, 10) 
                                                     = CONVERT(VARCHAR(10), t1.date_transaction, 10))) AS numLeads,
                         (SELECT        COUNT(DISTINCT user_id) AS Expr1
                           FROM            dbo.sas_transactions AS t2
                           WHERE        (transaction_type = 'Sale' OR
                                                     transaction_type = 'Manual Sale') AND (qb_merchant_id = t1.qb_merchant_id) AND (store_id = t1.store_id) AND (voided <> '1') AND (CONVERT(VARCHAR(10), date_transaction, 10) 
                                                     = CONVERT(VARCHAR(10), t1.date_transaction, 10))) AS numAffiliates
    FROM            dbo.sas_transactions AS t1 LEFT OUTER JOIN
                     dbo.sas_transaction_edits AS te ON t1.transaction_id = te.TransID
    WHERE        (t1.transaction_type <> 'Manual Deposit') AND (t1.transaction_type <> 'Autodeposit') AND (t1.user_id <> 144212)
    GROUP BY CONVERT(VARCHAR(10), t1.date_transaction, 10), t1.qb_merchant_id, t1.store_id

Why not start by stripping out all the results and making it a `select *` statement. If that works, add each calculation in 1 by 1 until the error occurs ... — Hugh Jones, Feb 03 '15 at 18:19
It says *converting numeric to data type numeric* so I would not assume it a char conversion — Alex K., Feb 03 '15 at 18:22
If `date_transaction*` is a DATETIME you could not perform all those varchar conversions to compare the date part, instead; [How to compare datetime with only date in SQL Server](http://stackoverflow.com/questions/25564482/how-to-compare-datetime-with-only-date-in-sql-server) — Alex K., Feb 03 '15 at 18:23
I am guessing its a null date, but I think the OP should isolate the exact piece of the query that is barfing — Hugh Jones, Feb 03 '15 at 18:40
This query would benefit greatly from using a sum with a case expression. There is no need to hit the same base table over and over and over in all these subqueries. — Sean Lange, Feb 03 '15 at 19:20

score 0 · Answer 1 · answered Feb 03 '15 at 19:43

0

Pretty sure your query could be simplified to something like this using case expressions. Now with a greatly simplified query, let's figure out what the issue it that you were running in to. I suspect that somewhere in your existing case expressions you have an implicit conversion happening but it is difficult to tell from here.

SELECT CONVERT(VARCHAR(10), t1.date_transaction, 10) AS summaryDate
    , t1.qb_merchant_id
    , t1.store_id 
    , Sum(t1.amount) - Isnull(Sum(te.TransAmount), 0) + Isnull(Sum(te.OriginalTransAmount), 0) AS grossSales
    , Sum(CASE WHEN t1.voided = '1' THEN 0 ELSE t1.amount END) AS netSales
    , SUM(case when voided = '1' then 1 end) as voids
    , Sum(Isnull(te.Commission, CASE WHEN t1.voided = '1' THEN 0 ELSE t1.amount_commission END)) AS totalCommissions
    , Sum(CASE WHEN t1.voided = '1' THEN 0 ELSE t1.amount_sas END) AS totalShareASale
    , SUM(case when transaction_type IN ('Sale', 'Manual Sale') and voided <> '1' then 1 end) as numOrders
    , sum(case when transaction_type = 'Lead' then 1 end) as numLeads
    , count(case when transaction_type IN ('Sale', 'Manual Sale') and voided <> '1' then 1 end) as numAffiliates
FROM   dbo.sas_transactions AS t1
LEFT OUTER JOIN dbo.sas_transaction_edits AS te ON t1.transaction_id = te.TransID
WHERE  t1.transaction_type not in ('Manual Deposit', 'Autodeposit')
    AND t1.user_id <> 144212
GROUP BY CONVERT(VARCHAR(10), t1.date_transaction, 10)
    , t1.qb_merchant_id
    , t1.store_id

answered Feb 03 '15 at 19:43

Sean Lange

33,028
3
25
40

I am still getting the same arithmetic error with the changes in place. – Bethany Minor Feb 04 '15 at 17:02
Right...as I said this just simplifies the query. It is almost certainly in one of your sum aggregates. I am guessing that somewhere in there is a column that is not a numeric datatype and there is a value in that column that isn't numeric. We have no idea of what your tables actually look like. Purely guessing here but is at least one of these columns a varchar? (t1.amount, te.TransAmount, te.OriginalTransAmount, t1.amount_commission, t1.amount_sas) – Sean Lange Feb 04 '15 at 17:07
I honestly didn't build this originally but looking at the table values: voided, transaction type, and Paid Currency are varchar. Everything else is decimal, int, datetime, or text. – Bethany Minor Feb 04 '15 at 23:08
If at all possible I would suggest changing the text datatype to varchar(max). The text datatype is deprecated and is quite frankly a real pain in the rear to deal with. – Sean Lange Feb 05 '15 at 15:25
Looking at the values is irrelevant, The datatypes of the columns are what is important. The problem you are encountering is almost certainly because one of these columns is not a numeric datatype. Without more details all we can do is help you isolate the problem. – Sean Lange Feb 05 '15 at 15:26
thanks for the feedback. I only meant that this query has historically worked up until Monday, so I assumed that there had to be an issue with one of the values in the table that is no longer capable of being converted. I made the change to archer(max) and also got he arithmetic error. I am now working on isolating each section of the query to see if I can isolate which piece of the query is causing the error. – Bethany Minor Feb 05 '15 at 18:10
of course changing text to varchar(max) isn't going to raise that exception. The exception is still happening because you have a value in one of those columns that isn't a numeric value. This is one of the major reasons NOT to store numbers as text. To find the problem, remove all the columns in the select statement and start adding them back in one at a time. Another method might be to select 1 column at a time from that table where ISNUMERIC(your column) = 0. I can't do much to help you debug this. – Sean Lange Feb 05 '15 at 19:23

Arithmetic Error converting Varchar in SQL Server query

1 Answers1