Error in numeric oveflow.

G

Guest

i have the follwing query.

SELECT Last(namefilter.year) AS [year], Last(namefilter.mis_country) AS
mis_country, Last(namefilter.activity_unit) AS activity_unit,
Last(namefilter.org_unit) AS org_unit, Last(namefilter.org_unit_description)
AS org_unit_description, Last(namefilter.head_type) AS head_type,
namefilter.id, Last(namefilter.job_summary_description) AS
job_summary_description, namefilter.long_description,
Last(namefilter.product_family) AS product_family,
Last(namefilter.job_status) AS job_status, Max(namefilter.MaxOfJan) AS Jan,
Max(namefilter.Feb) AS Feb, Max(namefilter.Mar) AS Mar, Max(namefilter.Apr)
AS Apr, Max(namefilter.May) AS May, Max(namefilter.Jun) AS Jun,
Max(namefilter.Jul) AS Jul, Max(namefilter.Aug) AS Aug, Max(namefilter.Sep)
AS Sep, Max(namefilter.Oct) AS Oct, Max(namefilter.Nov) AS Nov,
Max(namefilter.Dec) AS [Dec]
FROM namefilter
GROUP BY namefilter.id, namefilter.long_description
HAVING (((namefilter.id) Is Null));



some how i keep having numeric overflow error thus i was unable to export
this query to my excel spreadsheet. anyone can tell me what wrong with that.
i have anyother similiar query but it can work,only this got problem.
 
N

Nick 'The database Guy'

Hi Violet,

Make sure that all your fields are long integers, I wouldn't say that
but I have had exactly the same trouble and some fields were set to
smaller data types.

Good luck,
 
J

John Spencer

Guesses only.
DEC might be a reserved word. Try Surrounding Dec with square brackets.
(unlikely, but a guess)

It appears that you are using another query named NameFilter as the source
for this query. Could the problem lie in that query?

Trouble Shooting recommendation; Try dropping half the month fields and see
if the query still fails. Try dropping the other half, etc, until you
identify which field (or fields) are causing the error to occur. Then take
a look at the data for that field.

Overflow error can be generated when you are summing integer numbers and the
total exceeds 32767. It can also get generated when you divide by zero
(usually you'll get a divide by zero error, but ...)

SELECT Last(namefilter.year) AS [year]
, Last(namefilter.mis_country) AS mis_country
, Last(namefilter.activity_unit) AS activity_unit
, Last(namefilter.org_unit) AS org_unit
, Last(namefilter.org_unit_description) AS org_unit_description
, Last(namefilter.head_type) AS head_type,
, namefilter.id
, Last(namefilter.job_summary_description) AS job_summary_description
, namefilter.long_description
, Last(namefilter.product_family) AS product_family
, Last(namefilter.job_status) AS job_status
, Max(namefilter.MaxOfJan) AS Jan
, Max(namefilter.Feb) AS Feb
, Max(namefilter.Mar) AS Mar
, Max(namefilter.Apr) AS Apr
, Max(namefilter.May) AS May
, Max(namefilter.Jun) AS Jun
, Max(namefilter.Jul) AS Jul
, Max(namefilter.Aug) AS Aug
, Max(namefilter.Sep) AS Sep
, Max(namefilter.Oct) AS Oct
, Max(namefilter.Nov) AS Nov
, Max(namefilter.[Dec]) AS [Dec]
FROM namefilter
WHERE NameFilter.ID is Null
GROUP BY namefilter.id, namefilter.long_description


violet said:
i have the follwing query.

SELECT Last(namefilter.year) AS [year], Last(namefilter.mis_country) AS
mis_country, Last(namefilter.activity_unit) AS activity_unit,
Last(namefilter.org_unit) AS org_unit,
Last(namefilter.org_unit_description)
AS org_unit_description, Last(namefilter.head_type) AS head_type,
namefilter.id, Last(namefilter.job_summary_description) AS
job_summary_description, namefilter.long_description,
Last(namefilter.product_family) AS product_family,
Last(namefilter.job_status) AS job_status, Max(namefilter.MaxOfJan) AS
Jan,
Max(namefilter.Feb) AS Feb, Max(namefilter.Mar) AS Mar,
Max(namefilter.Apr)
AS Apr, Max(namefilter.May) AS May, Max(namefilter.Jun) AS Jun,
Max(namefilter.Jul) AS Jul, Max(namefilter.Aug) AS Aug,
Max(namefilter.Sep)
AS Sep, Max(namefilter.Oct) AS Oct, Max(namefilter.Nov) AS Nov,
Max(namefilter.Dec) AS [Dec]
FROM namefilter
GROUP BY namefilter.id, namefilter.long_description
HAVING (((namefilter.id) Is Null));



some how i keep having numeric overflow error thus i was unable to export
this query to my excel spreadsheet. anyone can tell me what wrong with
that.
i have anyother similiar query but it can work,only this got problem.
Click to expand...
 
J

Jamie Collins

John said:
Overflow error can be generated when you are summing integer numbers and the
total exceeds 32767.
Click to expand...

Given, then, this table:

CREATE TABLE Test (
data_col SMALLINT NOT NULL)
;
INSERT INTO Test (data_col) VALUES (1)
;
INSERT INTO Test (data_col)
SELECT Units.nbr + Tens.nbr + Hundreds.nbr + Thousands.nbr +
TenThousands.nbr AS seq FROM ( SELECT nbr FROM ( SELECT 0 AS nbr FROM
DropMe UNION ALL SELECT 1 FROM DropMe UNION ALL SELECT 2 FROM DropMe
UNION ALL SELECT 3 FROM DropMe UNION ALL SELECT 4 FROM DropMe UNION ALL
SELECT 5 FROM DropMe UNION ALL SELECT 6 FROM DropMe UNION ALL SELECT 7
FROM DropMe UNION ALL SELECT 8 FROM DropMe UNION ALL SELECT 9 FROM
DropMe ) AS DIGITS ) AS Units, ( SELECT nbr * 10 AS nbr FROM ( SELECT 0
AS nbr FROM DropMe UNION ALL SELECT 1 FROM DropMe UNION ALL SELECT 2
FROM DropMe UNION ALL SELECT 3 FROM DropMe UNION ALL SELECT 4 FROM
DropMe UNION ALL SELECT 5 FROM DropMe UNION ALL SELECT 6 FROM DropMe
UNION ALL SELECT 7 FROM DropMe UNION ALL SELECT 8 FROM DropMe UNION ALL
SELECT 9 FROM DropMe ) AS DIGITS ) AS Tens, ( SELECT nbr * 100 AS nbr
FROM ( SELECT 0 AS nbr FROM DropMe UNION ALL SELECT 1 FROM DropMe UNION
ALL SELECT 2 FROM DropMe UNION ALL SELECT 3 FROM DropMe UNION ALL
SELECT 4 FROM DropMe UNION ALL SELECT 5 FROM DropMe UNION ALL SELECT 6
FROM DropMe UNION ALL SELECT 7 FROM DropMe UNION ALL SELECT 8 FROM
DropMe UNION ALL SELECT 9 FROM DropMe ) AS DIGITS ) AS Hundreds, (
SELECT nbr * 1000 AS nbr FROM ( SELECT 0 AS nbr FROM DropMe UNION ALL
SELECT 1 FROM DropMe UNION ALL SELECT 2 FROM DropMe UNION ALL SELECT 3
FROM DropMe UNION ALL SELECT 4 FROM DropMe UNION ALL SELECT 5 FROM
DropMe UNION ALL SELECT 6 FROM DropMe UNION ALL SELECT 7 FROM DropMe
UNION ALL SELECT 8 FROM DropMe UNION ALL SELECT 9 FROM DropMe ) AS
DIGITS ) AS Thousands, ( SELECT nbr * 10000 AS nbr FROM ( SELECT 0 AS
nbr FROM DropMe UNION ALL SELECT 1 FROM DropMe UNION ALL SELECT 2 FROM
DropMe UNION ALL SELECT 3 FROM DropMe UNION ALL SELECT 4 FROM DropMe
UNION ALL SELECT 5 FROM DropMe UNION ALL SELECT 6 FROM DropMe UNION ALL
SELECT 7 FROM DropMe UNION ALL SELECT 8 FROM DropMe UNION ALL SELECT 9
FROM DropMe ) AS DIGITS ) AS TenThousands
WHERE Units.nbr + Tens.nbr + Hundreds.nbr + Thousands.nbr +
TenThousands.nbr BETWEEN 2 AND 32767;

You suggest this should error:

SELECT SUM(data_col)
FROM Test
;

But it does not error; instead, the result is promoted to DOUBLE:

SELECT TYPENAME(SUM(data_col))
FROM Test
;

Jamie.

--
 
J

John Spencer

You are correct.

Jamie Collins said:
Given, then, this table:

CREATE TABLE Test (
data_col SMALLINT NOT NULL)
;
INSERT INTO Test (data_col) VALUES (1)
;
INSERT INTO Test (data_col)
SELECT Units.nbr + Tens.nbr + Hundreds.nbr + Thousands.nbr +
TenThousands.nbr AS seq FROM ( SELECT nbr FROM ( SELECT 0 AS nbr FROM
DropMe UNION ALL SELECT 1 FROM DropMe UNION ALL SELECT 2 FROM DropMe
UNION ALL SELECT 3 FROM DropMe UNION ALL SELECT 4 FROM DropMe UNION ALL
SELECT 5 FROM DropMe UNION ALL SELECT 6 FROM DropMe UNION ALL SELECT 7
FROM DropMe UNION ALL SELECT 8 FROM DropMe UNION ALL SELECT 9 FROM
DropMe ) AS DIGITS ) AS Units, ( SELECT nbr * 10 AS nbr FROM ( SELECT 0
AS nbr FROM DropMe UNION ALL SELECT 1 FROM DropMe UNION ALL SELECT 2
FROM DropMe UNION ALL SELECT 3 FROM DropMe UNION ALL SELECT 4 FROM
DropMe UNION ALL SELECT 5 FROM DropMe UNION ALL SELECT 6 FROM DropMe
UNION ALL SELECT 7 FROM DropMe UNION ALL SELECT 8 FROM DropMe UNION ALL
SELECT 9 FROM DropMe ) AS DIGITS ) AS Tens, ( SELECT nbr * 100 AS nbr
FROM ( SELECT 0 AS nbr FROM DropMe UNION ALL SELECT 1 FROM DropMe UNION
ALL SELECT 2 FROM DropMe UNION ALL SELECT 3 FROM DropMe UNION ALL
SELECT 4 FROM DropMe UNION ALL SELECT 5 FROM DropMe UNION ALL SELECT 6
FROM DropMe UNION ALL SELECT 7 FROM DropMe UNION ALL SELECT 8 FROM
DropMe UNION ALL SELECT 9 FROM DropMe ) AS DIGITS ) AS Hundreds, (
SELECT nbr * 1000 AS nbr FROM ( SELECT 0 AS nbr FROM DropMe UNION ALL
SELECT 1 FROM DropMe UNION ALL SELECT 2 FROM DropMe UNION ALL SELECT 3
FROM DropMe UNION ALL SELECT 4 FROM DropMe UNION ALL SELECT 5 FROM
DropMe UNION ALL SELECT 6 FROM DropMe UNION ALL SELECT 7 FROM DropMe
UNION ALL SELECT 8 FROM DropMe UNION ALL SELECT 9 FROM DropMe ) AS
DIGITS ) AS Thousands, ( SELECT nbr * 10000 AS nbr FROM ( SELECT 0 AS
nbr FROM DropMe UNION ALL SELECT 1 FROM DropMe UNION ALL SELECT 2 FROM
DropMe UNION ALL SELECT 3 FROM DropMe UNION ALL SELECT 4 FROM DropMe
UNION ALL SELECT 5 FROM DropMe UNION ALL SELECT 6 FROM DropMe UNION ALL
SELECT 7 FROM DropMe UNION ALL SELECT 8 FROM DropMe UNION ALL SELECT 9
FROM DropMe ) AS DIGITS ) AS TenThousands
WHERE Units.nbr + Tens.nbr + Hundreds.nbr + Thousands.nbr +
TenThousands.nbr BETWEEN 2 AND 32767;

You suggest this should error:

SELECT SUM(data_col)
FROM Test
;

But it does not error; instead, the result is promoted to DOUBLE:

SELECT TYPENAME(SUM(data_col))
FROM Test
;

Jamie.
Click to expand...
 
G

Guest

in my case, i did not sum up any value..could it be because of my id..cos id
field has large number.hwever, i dunno how to change the field to long
interger.My data is a link table from excel so i cant change the field
setting at the table there.
 
Top