September 2, 2010 at 10:50 pm
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.
September 2, 2010 at 11:12 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 2, 2010 at 11:45 pm
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
September 3, 2010 at 11:17 am
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
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 3, 2010 at 7:52 pm
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.
September 4, 2010 at 10:10 am
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"
September 5, 2010 at 9:56 pm
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.
September 7, 2010 at 12:52 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 7, 2010 at 1:27 pm
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
)
SELECTp1.Digit + p2.Digit + '.' + p4.Digit + p5.Digit
FROMcte AS p1
CROSS JOINcte AS p2
CROSS JOINcte AS p4
CROSS JOINcte AS p5
ORDER BYp1.Digit,
p2.Digit,
p4.Digit,
p5.Digit
DELETE
FROM@Sample
OUTPUTdeleted.RowID,
deleted.Data
WHEREISNUMERIC(Data) = 0
-- Ordinary convert, fails for RowID 4
SELECTData,
CAST(Data AS DECIMAL(4, 2)) AS ConvertedValue
FROM@Sample
WHERERowID = 6
-- Here is a failsafe conversion
SELECTRowID,
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"
September 8, 2010 at 6:05 am
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
November 18, 2010 at 1:14 pm
I'm with a very similiar problem and I tried to test this last code to check if it solves my problem. But it doesn't helped me.
The difference between the scenarios is that my query is using "like" instead of "=".
When I try to execute the code below I get the error message:
select * from
( select * from test
where kind like'AB%' ) x
where convert(decimal(4,2),data) > 1
Arithmetic overflow error converting varchar to data type numeric.
I also checked the estimated execution plan and it seems to treat everything like only one query (attached).
Does anyone know how to fix this?
Tks
March 9, 2011 at 3:11 pm
Nat Na (11/18/2010)
I'm with a very similiar problem and I tried to test this last code to check if it solves my problem. But it doesn't helped me.The difference between the scenarios is that my query is using "like" instead of "=".
When I try to execute the code below I get the error message:
select * from( select * from test
where kind like'AB%' ) x
where convert(decimal(4,2),data) > 1
Msg 8115, Level 16, State 8, Line 1Arithmetic overflow error converting varchar to data type numeric.
I also checked the estimated execution plan and it seems to treat everything like only one query (attached).
Does anyone know how to fix this?
Tks
The solution is to use a case statement:
select *
from test
where case when kind like'AB%'
then case when convert(decimal(4,2),data) > 5 then 1 else 0 end
else 0
end = 1
And the reason why the query plan show it as one query, is because the query optimizer TURNS it into one query -- SQL guarantees the results, not the method. As long as the final result is the same, the query engine is entitled to do whatever it wants. SQL doesn't have short circuit OR's or ANDs (or even a prefered order of evaluation) but you can rewrite short circuit conditions into a case statement, which effectively has a defined execution sequence (the sequence of evaluation isn't actually defined, but the RESULTS of the evaluation is, so if it does them out of sequence and there's an exception, the excpetion has to be trapped and then returned to the caller only when the test should have been done if the evaluation was done in sequence).
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply