1

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?

  • where r.date = '9/30/2016' ... Oh why, why, why? – ypercubeᵀᴹ May 25 '17 at 18:03
3

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!" ?)

2

I suspect your SUM(rs.dh_simple + rs.ha_simple) is INT, try CASTING both to BIGINT/FLOAT/DECIMAL before doing the SUM.

INT maximum size = 2,147,483,647

Warning from MS though: When you use the +, -, *, /, or % arithmetic operators to perform implicit or explicit conversion of int, smallint, tinyint, or bigint constant values to the float, real, decimal or numeric data types, the rules that SQL Server applies when it calculates the data type and precision of the expression results differ depending on whether the query is autoparameterized or not.

Try:

SELECT 
cf.cnum as cnum, 
c.name as name, 
r.assets as assets, 
SUM(CAST(rs.dh_simple as BIGINT) + CAST(rs.ha_simple as BIGINT)) 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'
GROUP BY cf,num, c.name, r.assets
HAVING SUM(CAST(rs.dh_simple as BIGINT) + CAST(rs.ha_simple as BIGINT)) > 0
  • 1
    Even though I wrote those queries, I'm not familiar enough with SQL to know how to do casting... can you show me, please? However, the fact that the first two queries work lead me to believe that may not be the issue.. – Ebony Maw May 19 '17 at 16:49
  • You do a group by in second query, so it aggregates multiple CNUM SUMS together, where in your first query, you might have multiple smaller rows for multiple CNUMs (I can't see the data so I am assuming CNUMs are not UNIQUE). I'll update my answer with CAST. – Henrico Bekker May 19 '17 at 16:58
  • This query simply gives me cnum and sums. I need 4 columns. cnum | name | assets | sums. – Ebony Maw May 19 '17 at 17:06
0

After lots of trial and error, this finally appears to work!

SELECT r.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, currentfunds cf, retirement_survey rs
WHERE  
c.cnum = r.cnum
AND r.cnum = cf.cnum
AND cf.fundid = rs.fundid
AND r.date = '9/30/2016'
GROUP BY r.cnum, c.name, r.assets
ORDER BY 1

It's super slow though, and requires indexing as one of those tables is actually a view... but should work for now :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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