Error converting varchar to numeric only in where clause
-
Hi experts...
I have a problem with one of my SQL Server query's...
First of all, we are in SQL Server 2008 R2 Enterprise Edition (64-bit), version 10.50.4000.0 on a server with OS : Microsoft Windows NT 6.0 (6002)
I have a table with following columns:
DataId int
PathId int
Value varchar(4000)
The column 'Value' can contain all kind of data, from string to int to decimal to dates...
But, depending on the PathId, it always has the same type of data.
For example, for PathId's 30 and 31, the Value column contains always an amount --> numeric data
Following query runs without any problem, returning almost 30.000 records :
SELECTDataId, PathId,
,CONVERT(numeric(14,2),Value) as Amount
FROM dbo.TestTable
WHERE PathId IN (30,31)
Following query proves to me that all records do have numeric data in the table :
SELECTDataId, PathId,
,ISNUMERIC(Value) as Test_Value_Numeric
,CONVERT(numeric(14,2),Value) as Amount
,ISNUMERIC(CONVERT(numeric(14,2),Value)) as Test_Amount_Numeric
FROM dbo.TestTable
WHERE PathId IN (30,31)
Both columns Test_Value_Numeric and Test_Amount_Numeric always return 1.
Now, when I try to leave out the records which have an amount = 0, my query returns me an error : 'Msg 8114, Level 16, State 5, Line 1; Error converting data type varchar to numeric.'
Just add following line to the query to see what's happening : "AND CONVERT(numeric(14,2),Value) > 0"
(or use a CTE to create a dataset with amount, and then in your result-query, retrieve data from the cte where amount > 0, same issue)
To test, I used the last query which works, and did a "select into" to a 2nd testTable, and the column 'Amount' was created as numeric(14,2) in the new testTable.
I really hope someone can point out my issue, I am searching for it now for hours, have tried 3 experts, but nobody can give an explanation.
Is this a bug ???
PS : I have attached in an Excel the real data, so you can test it yourself with my data...
-
I suspect this is because it's attempting to do the conversion in the WHERE clause for all values, not just those with PathID 30 and 31.
Try something like this:
WITH MyValues AS (SELECT DataId, PathId,
, ISNUMERIC(Value) as Test_Value_Numeric
, CONVERT(numeric(14,2),Value) as Amount
, ISNUMERIC(CONVERT(numeric(14,2),Value)) as Test_Amount_Numeric
FROM dbo.TestTable
WHERE PathId IN (30,31)
)
SELECT
DataId,
PathId,
Test_Value_Numeric,
Amount,
Test_Amount_Numeric
FROM
MyValues
WHERE
Amount > 0
John
-
Thanks for the suggestion, but I already tried it, same error...
-
Its running fine for me. It returned 29877 when I added CONVERT(numeric(14,2),Value) > 0 in where Clause.
My machine is
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)
-
Here we have an object lesson in why EAV tables are not a good thing. If you can't change the database structure, this should work for you:
SELECT DataId, PathId,, ISNUMERIC(Value) as Test_Value_Numeric
, CONVERT(numeric(14,2),Value) as Amount
, ISNUMERIC(CONVERT(numeric(14,2),Value)) as Test_Amount_Numeric
INTO #TempValues
FROM dbo.TestTable
WHERE PathId IN (30,31)
SELECT
DataId,
PathId,
Test_Value_Numeric,
Amount,
Test_Amount_Numeric
FROM
#TempValues
WHERE
Amount > 0
DROP TABLE #TempValues
John
-
Try this:
DROP TABLE #TestCREATE TABLE #Test (ID INT IDENTITY (23,6921), Value VARCHAR(25))
INSERT INTO #Test (Value) VALUES ('3.14'), ('Twenty seven'), ('24/04/2013'), ('100'), ('Yesterday'), ('0')
SELECT *
FROM (
SELECT
ID,
Value,
NumericValue = CAST(Value AS NUMERIC(10,2))
FROM #Test
WHERE ISNUMERIC(Value) = 1
) d
WHERE NumericValue <> 0
Grab the estimated plan and hover on the Table Scan icon. Have a look at the predicate - the CAST is evaluated before the ISNUMERIC.
Now try the same with this:
SELECT *FROM (
SELECT
ID,
Value,
NumericValue = CAST(Value AS NUMERIC(10,2))*1
FROM #Test
WHERE ISNUMERIC(Value) = 1
) d
WHERE NumericValue <> 0
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
DevDB (4/24/2013)
Its running fine for me. It returned 29877 when I added CONVERT(numeric(14,2),Value) > 0 in where Clause.My machine is
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)
I think that's because all the sample data provided was numeric.
I like Chris's solution - it doesn't involve the expense of creating and populating a temporary table and so will be more efficient than mine.
John
-
John Mitchell-245523 (4/24/2013)
DevDB (4/24/2013)
Its running fine for me. It returned 29877 when I added CONVERT(numeric(14,2),Value) > 0 in where Clause.My machine is
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)
I think that's because all the sample data provided was numeric.
I like Chris's solution - it doesn't involve the expense of creating and populating a temporary table and so will be more efficient than mine.
John
It's not reliable though, John. This is:
SELECTID,
Value,
x.NumericValue
FROM #Test
CROSS APPLY (
SELECT [NumericValue] = CASE
WHEN ISNUMERIC(Value) = 1 THEN CAST(Value AS NUMERIC(10,2))
ELSE 0 END
) x
WHERE NumericValue <> 0
Edit: within the known limitations of ISNUMERIC()
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Chris, I can see the little differences in the plan.
However, I am not a dba-expert, and not that good at analysing query plans.
Can you please explain me a bit what the difference is about, and what are the consequences ?
Also, I am confused if I can use your solution or not, after your last post...
Already great thanks for the help !!!
-
tom.wauters (4/24/2013)
Chris, I can see the little differences in the plan.However, I am not a dba-expert, and not that good at analysing query plans.
Can you please explain me a bit what the difference is about, and what are the consequences ?
Also, I am confused if I can use your solution or not, after your last post...
Already great thanks for the help !!!
The predicate in the top picture is from the query which generates the error. ISNUMERIC() is evaluated after converting the string to a number.
The predicate in the bottom picture is from a query which does not generate the error, because ISNUMERIC is evaluated before converting the string to a number.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Thanks Chris.
But if I leave out the where-clause, both 'IsNumeric' checks in the select always return 1, which means in my oppinion that all data can be converted to numeric format.
So I thought, I create a CTE, which selected at first only the data for which I am sure it is in numeric format and I add the converted value.
Then in the second query, I remove all those records for which the value = 0...
I can't see why this doesn't work...
A work-around I have found so far is the following :
WITH CteComplement AS (
SELECTDataId, PathId, CONVERT(numeric(14,2),Value) as Amount
FROM TransCopy.BLOB_Data_Filtered
WHERE PathId IN (30,31)
) ,
CteAmountNot0 AS (
-- Cte to add a flag to dataset, which indicates if the Amount <> 0 (flag = Y) or Amount = 0 (flag N)
SELECT*
,CASE WHEN Amount = 0 THEN 'N' ELSE 'Y' END AmountNot0
FROM CteComplement
)
SELECT*
FROM CteAmountNot0
WHERE AmountNot0 = 'Y'
This works just fine the way I want it to work, but it is still strange to me...
-
Tom, this CTE method does exactly the same thing - it reverses the order in which the two predicates are evaluated. The CTE's aren't evaluated in order, they're more or less substituted out. You can extract the predicate from the properties tab of the table scan:
([tempdb].[dbo].[#Test].[PathID]=(30) OR [tempdb].[dbo].[#Test].[PathID]=(31)) AND CASE WHEN CONVERT(numeric(14,2),[tempdb].[dbo].[#Test].[Value],0)=(0.00) THEN 'N' ELSE 'Y' END='Y'and use it to reconstruct a query which more closely resembles what SQL Server is doing:
SELECT *, CONVERT(numeric(14,2),Value) as AmountFROM #Test
WHERE (#Test.[PathID]=(30) OR #Test.[PathID]=(31)) AND
CASE WHEN CONVERT(numeric(14,2),#Test.[Value],0)=(0.00) THEN 'N' ELSE 'Y' END='Y'
and, sure enough, it works - because the predicates are evaluated with the ISNUMERIC check first.
Your best option is to realise that SQL Server will evaluate predicates in whatever order best fits when the plan is chosen, and not leave it to chance.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
This works too:
WITH CteComplement AS (SELECT *, CONVERT(numeric(14,2),Value) as Amount
FROM #Test
WHERE PathId IN (30,31)
)
-- Cte to add a flag to dataset, which indicates if the Amount <> 0 (flag = Y) or Amount = 0 (flag N)
SELECT *
FROM CteComplement
WHERE CASE WHEN Amount = 0 THEN 'N' ELSE 'Y' END = 'Y';
Here's the filter predicate for the table scan:
([tempdb].[dbo].[#Test].[PathID]=(30) OR [tempdb].[dbo].[#Test].[PathID]=(31)) AND CASE WHEN CONVERT(numeric(14,2),[tempdb].[dbo].[#Test].[Value],0)=(0.00) THEN 'N' ELSE 'Y' END='Y'PathID first, identifying numeric values.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Please remember that ISNUMERIC(somevalue) = 1 means that the value in somevalue can be converted to one of the numeric datatypes, but that it may still fail depending on the datatype you are actually converting to in your code.
Please read the following article: http://www.sqlservercentral.com/articles/IsNumeric/71512/.
-
@Lynn, indeed, I have read it, I also did a test and concatenated 'Value' with 'e0', and still the IsNumeric evaluates too 1, so it really always is a number.
@chris-2, great thanks for all your help. If I can conclude, the best option I have for the moment is to continue with the workaround I have found (or shortcut it with your last query, the case in the where clause).
Should I report this a some kind of bug, or this is a 'feature' 🙂
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply