Arithmetic overflow error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Arithmetic overflow error

Author

Topic

Bernard
Starting Member

4 Posts

Posted - 2008-09-30 : 08:51:17

I get a Arithmetic overflow error converting varchar to data type numeric while executing a union select:

select v_int, v_real from view1
union
select v_int, v_real from view2

- select 1 and select 2 run perfectly when executet separately.
- view2 has 143 records
- select top 150 ...... works even in the union select
- union select as above returns the error.

There is no varchar to numeric converting what so ever.
View1 and view2 have the same data fields, v_real is "real" in both cases.

I am really lost and don't know where to search again. I hope someone can help me.

Regards
Bernard

CodesMyBusiness
Starting Member

9 Posts

Posted - 2008-09-30 : 08:58:32

If your UNIONing two columns and one is numeric, while the others not, then it will try and do a conversion.

First, make sure that view1.v_int and view2.v_int are spitting out the same type of value, and do the same for view1.v_real and view2.v_real.

Bernard
Starting Member

4 Posts

Posted - 2008-09-30 : 09:06:06

I checked it, they are identical. The strange thing is, when I limit with top 150 or top 200 (the view has only 143 records) the union works perfectly. When I don't limit or limit with Top 1000 I get the error.
Strange, isn't it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 09:27:42

Run this query and post back the result

SELECT		TABLE_NAME,
		COLUMN_NAME,
		ORDINAL_POSITION,
		DATA_TYPE,
		CHARACTER_MAXIMUM_LENGTH
FROM		INFORMATION_SCHEMA.COLUMNS
WHERE		TABLE_NAME IN ('view1', 'view2')
ORDER BY	TABLE_NAME,
		COLUMN_NAME

E 12°55'05.63"
N 56°04'39.26"

Bernard
Starting Member

4 Posts

Posted - 2008-09-30 : 10:00:31

select OP_ID,AvgDistributionBDL from VW_CentralPipelineReport_1
union
select OP_ID,AvgDistributionBDL from VW_CentralPipelineReport_2

Your select gives the following result:

VW_CentralPipelineReport_1 AvgDistributionBDL 6 float NULL
VW_CentralPipelineReport_1 Bid Manager 10 varchar 129
VW_CentralPipelineReport_1 CL_ID 22 numeric NULL
VW_CentralPipelineReport_1 ClientOppName 8 varchar 203
VW_CentralPipelineReport_1 ClienType 5 varchar 15
VW_CentralPipelineReport_1 Competitors 16 varchar 4000
VW_CentralPipelineReport_1 CP_Bezeichnung 23 varchar 50
VW_CentralPipelineReport_1 CV_Code 1 varchar 22
VW_CentralPipelineReport_1 DateCVC 2 varchar 30
VW_CentralPipelineReport_1 Duration 14 int NULL
VW_CentralPipelineReport_1 FirstRevenuePay 13 varchar 30
VW_CentralPipelineReport_1 OP_CloseCmt 19 varchar 1024
VW_CentralPipelineReport_1 OP_CloseDate 11 varchar 30
VW_CentralPipelineReport_1 OP_CreaDate 17 varchar 30
VW_CentralPipelineReport_1 OP_ID 20 numeric NULL
VW_CentralPipelineReport_1 OP_ModDate 18 varchar 30
VW_CentralPipelineReport_1 OP_OMT_ID 21 numeric NULL
VW_CentralPipelineReport_1 OP_ProjectStart 12 varchar 30
VW_CentralPipelineReport_1 OT_Bez 4 varchar 100
VW_CentralPipelineReport_1 PB_Bez 15 float NULL
VW_CentralPipelineReport_1 SBS_Name 3 varchar 100
VW_CentralPipelineReport_1 TCV Owner 9 varchar 129
VW_CentralPipelineReport_1 TCVAmountBDL 7 float NULL
VW_CentralPipelineReport_2 AvgDistributionBDL 6 float NULL
VW_CentralPipelineReport_2 Bid Manager 10 varchar 129
VW_CentralPipelineReport_2 CL_ID 22 numeric NULL
VW_CentralPipelineReport_2 ClientOppName 8 varchar 203
VW_CentralPipelineReport_2 ClienType 5 varchar 15
VW_CentralPipelineReport_2 Competitors 16 varchar 4000
VW_CentralPipelineReport_2 CP_Bezeichnung 23 varchar 50
VW_CentralPipelineReport_2 CV_Code 1 varchar 22
VW_CentralPipelineReport_2 DateCVC 2 varchar 30
VW_CentralPipelineReport_2 Duration 14 int NULL
VW_CentralPipelineReport_2 FirstRevenuePay 13 varchar 30
VW_CentralPipelineReport_2 OP_CloseCmt 19 varchar 1024
VW_CentralPipelineReport_2 OP_CloseDate 11 varchar 30
VW_CentralPipelineReport_2 OP_CreaDate 17 varchar 30
VW_CentralPipelineReport_2 OP_ID 20 numeric NULL
VW_CentralPipelineReport_2 OP_ModDate 18 varchar 30
VW_CentralPipelineReport_2 OP_OMT_ID 21 numeric NULL
VW_CentralPipelineReport_2 OP_ProjectStart 12 varchar 30
VW_CentralPipelineReport_2 OT_Bez 4 varchar 100
VW_CentralPipelineReport_2 PB_Bez 15 float NULL
VW_CentralPipelineReport_2 SBS_Name 3 varchar 100
VW_CentralPipelineReport_2 TCV Owner 9 varchar 129
VW_CentralPipelineReport_2 TCVAmountBDL 7 float NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 10:07:53

i dont think error is in the posted code. are you using this as part of some other query?

CodesMyBusiness
Starting Member

9 Posts

Posted - 2008-09-30 : 10:11:28

This should work:

SELECT
  CONVERT(VARCHAR(50),v_int) as "v_int",
  CONVERT(VARCHAR(50),v_real) as "v_real"
FROM view1
UNION
SELECT
  CONVERT(VARCHAR(50),v_int) as "v_int",
  CONVERT(VARCHAR(50),v_real) as "v_real"
FROM view2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 10:52:30

FLOAT datatype (AvgDistributionBDL column) is not an exact datatype.
When stored, it is interpreted according some IEEE floating point rules. And depending on rounding same same value can be stored differently.

As suggested recently case your columns in the view.

SELECT	CAST(OP_ID AS BIGINT) AS OP_ID,
	CAST(AvgDistributionBDL AS MONEY) AS AvgDistributionBDL
FROM	VW_CentralPipelineReport_1

UNION 

SELECT	CAST(OP_ID AS BIGINT) AS OP_ID,
	CAST(AvgDistributionBDL AS MONEY) AS AvgDistributionBDL
FROM	VW_CentralPipelineReport_2

E 12°55'05.63"
N 56°04'39.26"

Bernard
Starting Member

4 Posts

Posted - 2008-10-01 : 09:29:42

Hello

I migrated my DB from SQL Server 2000 to 2005 and.....it works!!

Thank you all for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources