Problem with Insert statement - Arithmetic overflow

Hi
I'm trying to do a script that can combine data from different databases and
then present them in a list to for some payment purposes. I have little
problem though with a small bit of the script.
The following small part of the script works fine :
Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
SELECT SUM([Amount]), SUM([belb]), CVR
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Belb <>0
GROUP BY CVR , belb
This is part works fine - I get 1731 records inserted into my temp table.
I'd like to get the records grouped a little bit further though, so I've
tried with the modification below :
Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
SELECT SUM([Amount]), SUM([belb]), CVR
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Belb <>0
GROUP BY CVR
Here I just remove the "belb" column in the GROUP BY clause and now I get
the following error :
"Arithmetic overflow error converting float to data type numeric.
The statement has been terminated."
I don't quite understand why I get this error message since it's the same
values I'm trying to insert in both cases.
If I just do a -
SELECT SUM([Amount]), SUM([belb]), CVR
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Belb <>0
GROUP BY CVR
- I get 129 records which looks fine and looks like what I want.
The spe_temp..LonSum table is created as -
CREATE TABLE spe_temp..Lonsum (Amount_ decimal(9,2), Beloeb Decimal(9,2),
CVR varchar(10)COLLATE SQL_Danish_Pref_CP1_CI_AS ,
Periode Int, Sats int, Areal_total numeric(9,2), Areal_andelsbolig
numeric(9,2), Procent numeric(9,2), LoenSumBeloeb decimal(9,2) )
I hope that some of you can shed some light on this?
Regards
Steen
The greatest amount you can store in a decimal(9,2) column is 9,999,999.99.
Is the sum for one of the CVR's you group by greater than that?
Jacco Schalkwijk
SQL Server MVP
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ejWLt61PFHA.3788@.tk2msftngp13.phx.gbl...
> Hi
> I'm trying to do a script that can combine data from different databases
> and
> then present them in a list to for some payment purposes. I have little
> problem though with a small bit of the script.
> The following small part of the script works fine :
> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
> SELECT SUM([Amount]), SUM([belb]), CVR
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
> Belb <>0
> GROUP BY CVR , belb
> This is part works fine - I get 1731 records inserted into my temp table.
> I'd like to get the records grouped a little bit further though, so I've
> tried with the modification below :
> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
> SELECT SUM([Amount]), SUM([belb]), CVR
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
> Belb <>0
> GROUP BY CVR
> Here I just remove the "belb" column in the GROUP BY clause and now I get
> the following error :
> "Arithmetic overflow error converting float to data type numeric.
> The statement has been terminated."
> I don't quite understand why I get this error message since it's the same
> values I'm trying to insert in both cases.
> If I just do a -
> SELECT SUM([Amount]), SUM([belb]), CVR
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
> Belb <>0
> GROUP BY CVR
> - I get 129 records which looks fine and looks like what I want.
> The spe_temp..LonSum table is created as -
> CREATE TABLE spe_temp..Lonsum (Amount_ decimal(9,2), Beloeb Decimal(9,2),
> CVR varchar(10)COLLATE SQL_Danish_Pref_CP1_CI_AS ,
> Periode Int, Sats int, Areal_total numeric(9,2), Areal_andelsbolig
> numeric(9,2), Procent numeric(9,2), LoenSumBeloeb decimal(9,2) )
> I hope that some of you can shed some light on this?
> Regards
> Steen
>
|||Hi Jacco
You're right that there could be an issue here. The biggest value seems to
be 33.213.057,179999199 but even though I change the [Amount] and [Belb]
definition for my temp table to e.g. (Decimal 25,12) it still gives me the
error.
Regards
Steen
Jacco Schalkwijk wrote:[vbcol=seagreen]
> The greatest amount you can store in a decimal(9,2) column is
> 9,999,999.99. Is the sum for one of the CVR's you group by greater
> than that?
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ejWLt61PFHA.3788@.tk2msftngp13.phx.gbl...
|||Can you post the result of:
SELECT CVR, SUM([Amount]), SUM([belb])
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Belb <>0
GROUP BY CVR
?
Jacco Schalkwijk
SQL Server MVP
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:Oixs1L2PFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi Jacco
> You're right that there could be an issue here. The biggest value seems to
> be 33.213.057,179999199 but even though I change the [Amount] and [Belb]
> definition for my temp table to e.g. (Decimal 25,12) it still gives me the
> error.
> Regards
> Steen
>
> Jacco Schalkwijk wrote:
>
|||Hi Jacco
I got the problem solved - and it was the definition of the Decimal column
that wasn't big enough. First time I changed the definition I just changed
the syntax for CREATE TABLE.... - but I actually missed to re-create the
table.....doohhhh.....;-).
Thanks for you help.....it was spot on....
Regards
Steen
Jacco Schalkwijk wrote:[vbcol=seagreen]
> Can you post the result of:
> SELECT CVR, SUM([Amount]), SUM([belb])
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
> AND Belb <>0
> GROUP BY CVR
> ?
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:Oixs1L2PFHA.1176@.TK2MSFTNGP12.phx.gbl...
sql