Arithmetic overflow error converting expression to datatype int

Question

I'm selecting data into a temp table, then querying that table to get other values for a report. Here is the code that produces the error, and if it makes any difference there are about 36 million records in the temp table. It says the error is on the first column of the first query.

SELECT SUBSCRIPTION_ALIAS,
       'Count' = 0,
       COUNT(SUBSCRIPTION_ALIAS) AS MonthChange
FROM #Temp1 A WHERE DATEDIFF(day,JOIN_DTM,'01/01/2012') < 31    
GROUP BY SUBSCRIPTION_ALIAS

UNION

SELECT B.SUBSCRIPTION_ALIAS,
       COUNT(B.SBSCRPTN_MBR_KEY) AS [Count],
       'MonthChange' = 0
FROM #Temp1 B JOIN #Temp1 A ON B.SUBSCRIPTION_ALIAS = A.SUBSCRIPTION_ALIAS
GROUP BY B.SUBSCRIPTION_ALIAS

DROP TABLE #Temp1

Here is the statement for the temp table (it's ugly). The error message is the title of this thread.

SELECT
  bi_communications.F_EML_RCPNT.SBSCRPTN_LIST_KEY,
  bi_communications.F_EML_RCPNT.SBSCRPTN_MBR_KEY,
  bi_communications.D_SBSCRPTN_LIST.SBSCRPTN_LIST_NM,
  bi_communications.F_EML_RCPNT.DLRY_DT_KEY,
  bi_communications.D_DT.DT_DT,

  CASE
WHEN D_SBSCRPTN_LIST.SBSCRPTN_LIST_NM = 'active-offers' THEN 'Special Offers'
WHEN D_SBSCRPTN_LIST.SBSCRPTN_LIST_NM = 'reserveamerica-offers' THEN 'RA Special Offers'
WHEN D_SBSCRPTN_LIST.SBSCRPTN_LIST_NM in
   ('active-team-sports-insider','active-tennis','active-women-news', ... (omitting excess)
   ) THEN 'Content'
    WHEN D_SBSCRPTN_LIST.SBSCRPTN_LIST_NM = 'local-events' THEN 'Local Events'
    ELSE 'Blank'
  END AS SUBSCRIPTION_ALIAS,
  bi_communications.D_SBSCRPTN_MBR.JOIN_DTM
INTO #Temp1     
FROM bi_communications.F_EML_RCPNT
    JOIN
     bi_communications.D_SBSCRPTN_LIST ON bi_communications.F_EML_RCPNT.SBSCRPTN_LIST_KEY = bi_communications.D_SBSCRPTN_LIST.SBSCRPTN_LIST_KEY
JOIN bi_communications.D_DT ON bi_communications.F_EML_RCPNT.DLRY_DT_KEY = bi_communications.D_DT.DT_KEY
    JOIN bi_communications.D_SBSCRPTN_MBR ON bi_communications.F_EML_RCPNT.SBSCRPTN_MBR_KEY = bi_communications.D_SBSCRPTN_MBR.SBSCRPTN_MBR_KEY
WHERE
  bi_communications.F_EML_RCPNT.SBSCRPTN_LIST_KEY  IN 
  ( 180740437, 180741017, 180740482, 180740438, ... )
AND bi_communications.D_DT.DT_DT BETWEEN  '01/01/2011 00:00:00' AND '01/01/2012 00:00:00'

What makes you think there is a conversion to int? Maybe you should include the error message. — wallyk
– wallyk, Commented Jul 6, 2012 at 18:19
@wallyk - That's part of what is so confusing to me about the error. I can't see why it would be converting anything to an int, especially on that column. — RageQwit
– RageQwit, Commented Jul 6, 2012 at 18:20
Let's see the statement for create table #temp1 and the error message. — wallyk
– wallyk, Commented Jul 6, 2012 at 18:21
what datatype is bi_communications.F_EML_RCPNT.SBSCRPTN_LIST_KEY? If it not an integer then you should be able to wrap your values in quotes to fix — Matt Smucker
– Matt Smucker, Commented Jul 6, 2012 at 18:31

Gordon Linoff · Accepted Answer · 2012-07-06 18:35:53Z

1

Hmmm, subscription alias takes on 5 or so different values in a table with 34 million rows. Well, one of these is going to have at least 6 million rows. The self join is going to produce at least 36 trillion rows on this, which quickly gets to an arithmetic overflow on the second query.

Do you really mean this?

SELECT B.SUBSCRIPTION_ALIAS, COUNT(B.SBSCRPTN_MBR_KEY) AS [Count],'MonthChange' = 0
FROM #Temp1 B JOIN #Temp1 A
     ON B.SUBSCRIPTION_ALIAS = A.SUBSCRIPTION_ALIAS
GROUP BY B.SUBSCRIPTION_ALIAS

This is essentially squaring the number of rows for the count. I would guess that the error is in the second subquery; the location is not being reported correctly.

answered Jul 6, 2012 at 18:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

RageQwit Over a year ago

The end result is supposed to be a 3 column table with list name, the overall member count of the list, and the count of members joining in the last 30 days. I'm pretty sure that query you pointed out is the right thing for what I want.

Gordon Linoff Over a year ago

Can you ask another question so we can help you create the right query?

RageQwit Over a year ago

Is there a way to do what I want to do in a single query instead of using a union? What if I were to use a subquery to populate the value of one of the calculated columns?

Gordon Linoff Over a year ago

Like I suggested, ask another question. Include the format of the data you are starting with and the format that you want to produce.

nyxthulhu · Accepted Answer · 2012-07-06 18:23:26Z

0

Is it possible your JOIN_DTM has an invalid date (outwith range or just null) then the DATEDIFF runs onit, converting it to a int and causes a error?

answered Jul 6, 2012 at 18:23

nyxthulhu

9,8029 gold badges60 silver badges91 bronze badges

Collectives™ on Stack Overflow

Arithmetic overflow error converting expression to datatype int

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related