Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Arithmetic overflow error converting varchar to data type numeric. Expand / Collapse
Author
Message
Posted Thursday, September 2, 2010 10:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 27, 2012 12:32 AM
Points: 4, Visits: 27
I have a SELECT query that is searching for the maximum value for each person in a varchar field, but of course it is searching alphabetically when I need it to search numerically. I am using CONVERT to get the data into numerical format. The sub query works perfectly well by itself but I need the date field too. If I include the date with the max value I end up with multiple data per person, hence the subquery. The whole query doesn't give the error message if I take out the 2nd last line, but it gives me multiple results for each person and I only want the max. The resultant data is in a format such as "4.5" etc.

The error message states: Arithmetic overflow error converting varchar to data type numeric.

Note that I am querying a database developed by someone else and they will not want to change anything.

Here is a simplified version of my query:

SELECT DISTINCT
TABLE.PERSON_ID,
TABLE.VALUE,
TABLE.DATE

FROM
TABLE,
( SELECT DISTINCT
TABLE.PERSON_ID,
MAX ( CONVERT ( DECIMAL(4,2), TABLE.VALUE ) ) AS MaxValue
FROM
TABLE
WHERE
TABLE.TYPE = 'ABC'
GROUP BY
TABLE.PERSON_ID ) MaxResults

WHERE
TABLE.PERSON_ID = MaxResults.PERSON_ID AND
CONVERT ( DECIMAL(4,2), TABLE.VALUE ) = MaxResults.MaxValue AND
TABLE.TYPE = 'ABC'


It doesn't work if the 2nd last line doesn't have the conversion. Perhaps there's a better way to do the whole thing - I'm not an SQL expert by any means. I have always managed to solve my problems using the Internet but this is the first time I have given up and have had to ask for help, so any ideas would be greatly appreciated. Thanks.
Post #980031
Posted Thursday, September 2, 2010 11:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:58 PM
Points: 6,259, Visits: 7,453
Run a WHERE LEN(col) > 4 against the one you're converting to Numeric(4,2). If you don't find anything, Try WHERE CHARINDEX( '.', col) > 3, to see if you have something over 99.x in the column.

Numeric/Decimal 4,2 means you get xx.xx in the field. It's not a floating decimal.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #980035
Posted Thursday, September 2, 2010 11:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 27, 2012 12:32 AM
Points: 4, Visits: 27
Thanks very much Craig, I think you're on the right track. I should point out that the data I'm searching usually has 1 or 2 digits to the left of the decimal point and either none or 1 to the right, for example "4.5" or "10.2" or "5"

I couldn't get it to work with a " > " or " < " symbol but I could with " = ". I tested all this in the main query, not the subquery - hope that's right:

WHERE LEN (COL) = 4 resulted in all the values 10.0 and above
WHERE LEN (COL) = 3 resulted in all the values 9.9 and below
WHERE LEN (COL) = 1 resulted in all the values without decimals

I still need to get everything together though. I tried different numbers in DECIMAL(4,2) but it didn't make any difference. Must be close now!!! Thanks

Post #980041
Posted Friday, September 3, 2010 11:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:58 PM
Points: 6,259, Visits: 7,453
I wouldn't worry as much about values to the right of the decimal, it should round in. It's the ones to the left that'll cause the error. Somehow or another you've got a value of 100+. I'm confused why the > and < failed, but I'll leave that up to you unless you want to post code and errors.

Personally, I'd break it off the main query and find the problem data rows *first*, so it'd just be a
select col from table where CAST( col as NUMERIC(18,4))>= 100



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #980383
Posted Friday, September 3, 2010 7:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 27, 2012 12:32 AM
Points: 4, Visits: 27
Hi Craig

Thank you very much for your help again. I have searched for values 100+ as suggested but nothing came up. I didn't think it was necessary to mention this earlier but I'm starting to think that my problem might be due to all the other data in this column that I'm currently filtering out using WHERE TYPE = 'ABC'. I have this TYPE = 'ABC' in both my main and sub query. Data in this column of other types, excluded by my query, will certainly have values >= 100, along with text and dates. Do you think this might have something to do with it?

I tried increasing the (4,2) to (18,4) in both queries and received this error: Error converting data type varchar to numeric. Even (6,2) caused this error. I'm surprised that while I don't get any errors when I run either the sub query or the main query independently, as soon as I put them together I get the error. This is what I really don't understand but obviously due to my lack of experience. It would be a great hurdle to get over!

Thanks again.
Post #980566
Posted Saturday, September 4, 2010 10:10 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:58 AM
Points: 2,397, Visits: 3,407
What does this query return?

SELECT MAX(LEN(PARSENAME('0.' + Col1, 1))) AS Part1,
MAX(LEN(PARSENAME('0.' + Col1, 2))) AS Part2
FROM Table1
WHERE Type = 'ABC'



N 56°04'39.16"
E 12°55'05.25"
Post #980639
Posted Sunday, September 5, 2010 9:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 27, 2012 12:32 AM
Points: 4, Visits: 27
Both columns return "2". What does this tell you?

After my last post I woke up in the morning with a good idea (funny how that happens - bit of a worry really!). You suggested earlier to add "WHERE LEN(COL) > 4 ", which didn't work. I thought about capping the greater than with a less than and have had success. I am now using " WHERE LEN(COL)>0 AND LEN(COL)<6 ". This is working perfectly.

I have since changed the WHERE TYPE = 'ABC' to the other type I want, let's say 'XYZ', and it's failing again and I can't see why because the data is in the same format, e.g. 5, 1.5, 9.99. All very strange to me. In the meantime I accidentally discovered that it works by removing the join to the other table in the sub query, which was simply to exclude archived people. As this is not essential, as I can exclude them further in the process, I might just carry on like this. If the above, or your PARSENAME suggestion, has given you an answer I, and perhaps other readers, would be keen to hear your response. If none of this helps I will carry on with the first query that's working as is, and for the second one just exclude archived people.

If I don't hear from you, thank you very very much for your help - you have really saved the day! I hope others can benefit as well.
Post #980896
Posted Tuesday, September 7, 2010 12:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:58 PM
Points: 6,259, Visits: 7,453
I'm sorry, but without having the data and the database in front of me to help figure out what exactly is going on there, I'm afraid I'm at a loss. I could only assume your where clauses have to do with on which side your conversion calculation is occuring, pre- or post- data filtering, but that doesn't make a lot of sense even to me.

The only 'easy way' out I could see without digging into your structure and data is (*twitch*) to create an actual numeric column next to the other field, and run an update through your table to push numbers only into the field (CASE WHEN ISNUMERIC(@field) = 1 THEN @field ELSE NULL END), and use THAT field for these conversions.

Sorry I can't help you more on this one.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #981823
Posted Tuesday, September 7, 2010 1:27 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:58 AM
Points: 2,397, Visits: 3,407
DECLARE	@Sample TABLE
(
RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
Data VARCHAR(5) NOT NULL
)

INSERT @Sample
(
Data
)
SELECT '' UNION ALL
SELECT '1' UNION ALL
SELECT '11'

;WITH cte(Digit)
AS (
SELECT ' ' UNION ALL
SELECT '1'
)
INSERT @Sample
(
Data
)
SELECT p1.Digit + p2.Digit + '.' + p4.Digit + p5.Digit
FROM cte AS p1
CROSS JOIN cte AS p2
CROSS JOIN cte AS p4
CROSS JOIN cte AS p5
ORDER BY p1.Digit,
p2.Digit,
p4.Digit,
p5.Digit

DELETE
FROM @Sample
OUTPUT deleted.RowID,
deleted.Data
WHERE ISNUMERIC(Data) = 0

-- Ordinary convert, fails for RowID 4
SELECT Data,
CAST(Data AS DECIMAL(4, 2)) AS ConvertedValue
FROM @Sample
WHERE RowID = 6

-- Here is a failsafe conversion
SELECT RowID,
Data,
CAST(CASE PATINDEX('%.%', Data)
WHEN 0 THEN '0' + RTRIM(LTRIM(Data))
ELSE RTRIM(LTRIM(Data)) + '0'
END AS DECIMAL(4, 2)) AS Peso
FROM @Sample




N 56°04'39.16"
E 12°55'05.25"
Post #981851
Posted Wednesday, September 8, 2010 6:05 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:06 PM
Points: 290, Visits: 199
When you specify two filter conditions with AND, it's up to the SQL Server to decide the order in which to apply the conditions.

Consider this code:

create table test ( data varchar(10), kind varchar(10) )

insert into test ( data, kind ) values ( '1.1', 'ABC' )
insert into test ( data, kind ) values ( '9.9', 'ABC' )
insert into test ( data, kind ) values ( '222', 'XYZ' )
insert into test ( data, kind ) values ( 'zrk', 'GHI' )

select * from test
where convert(decimal(4,2),data) > 5
and kind='ABC'

drop table test

The first time I ran this, I got this error:
Arithmetic overflow error converting numeric to data type numeric.

Why? Because SQL Server decided to check the conversion first, and then check the kind. When it got to the third row, it tried to convert '222' to decimal(4,2) and failed, since it is larger than 99.99.

Next I commented out the third insertion, and ran the query again.
This time, I got this error:
Error converting data type varchar to numeric.

This time, SQL Server got to the last row and tried to convert 'zrk' to a number and failed, since it is not a number.

Then I un-commented out that third line, returning the code to its original form, and ran it again.
This time, the query worked, and it returned the one correct row with the value 9.9.

Why? For whatever mysterious reason, SQL Server came up with a different execution plan this time. It decided to check the 'ABC' filter first, and therefore never saw the other two values that would lead to errors.

How can you ensure that SQL Server would always apply the 'ABC' filter first? The query could be rewritten:

select * from 
( select * from test
where kind='ABC' ) x
where convert(decimal(4,2),data) > 5




Of course I've got free will. What other choice do I have?
Post #982223
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse