How to Fix Arithmetic Overflow Error When Merging Queries

sql serversybase

Let us consider the first query:

SELECT r.cnum as cnum, c.name as name, r.assets as assets
FROM research..complex c, research..retirement_simple_ira r
where r.date = '9/30/2016'
and c.cnum = r.cnum
ORDER BY cnum

This gives us a result set as follows:

enter image description here

Here is my second query:

SELECT cf.cnum, SUM(rs.dh_simple + rs.ha_simple) as sums
FROM retirement_survey as rs
INNER JOIN currentfunds as cf
ON rs.fundid = cf.fundid
WHERE rs.date  = '9/30/2016'
GROUP BY cf.cnum
HAVING SUM(rs.dh_simple + rs.ha_simple) > 0

enter image description here

I attempt to merge these two queries so that I would get a result set which lists 4 columns:

cnum | name | assets | sums

Here is my attempt:

SELECT cf.cnum as cnum, c.name as name, r.assets as assets, SUM(rs.dh_simple + rs.ha_simple) as sums
FROM research..complex c, research..retirement_simple_ira r, retirement_survey as rs
INNER JOIN currentfunds as cf
ON rs.fundid = cf.fundid
WHERE rs.date = '9/30/2016'
HAVING SUM(rs.dh_simple + rs.ha_simple) > 0

and that is when I receive the error. What am I doing incorrectly?

Best Answer

FWIW ... and in case you're (still) not sure why the original (combined) query was generating an overflow error ... consider:

  • missing join clause between the c and r tables (c.cnum = r.cnum)
  • missing join clause between the c and cf tables (c.cnum = cf.cnum) or r and cf tables (r.cnum = cf.cnum)
  • missing 'group by' clause

The missing join clauses means your query was generating a cartesian product between the c/r/cf tables (ie, generating a lot more join records than what you wanted).

The missing 'group by' clause means the sum() was being applied across that massive set of (cartesian product) join records; and as Henrico has suggested the sum() thinks it's working with integer datatypes so the sum() result is assumed to be integer; Sybase's integer datatype has a max value of 2B so when the sum() (sans a 'group by'; applied to massive cartesian product) hits above 2B you get the overflow error.

Your latest query includes those pieces (joins, 'group by') missing from the first attempt hence you're no longer generating an overflow error. ("Duh, Mark!" ?)

Related Question