Error converting varchar to numeric only in where clause

Question

Error converting varchar to numeric only in where clause

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

tom.wauters Say Hey Kid Points: 706 More actions · Answer 2

Thanks for the suggestion, but I already tried it, same error...

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 3

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)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Try this:

DROP TABLE #Test

CREATE 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 Shaw}

For 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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

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:

SELECT

ID,

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 Shaw}

For 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

tom.wauters Say Hey Kid Points: 706 More actions · Answer 8

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 !!!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

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 Shaw}

For 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

tom.wauters Say Hey Kid Points: 706 More actions · Answer 10

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...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

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 Amount

FROM #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 Shaw}

For 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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

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 Shaw}

For 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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

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/.

tom.wauters Say Hey Kid Points: 706 More actions · Answer 14

@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' 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 15

tom.wauters (4/25/2013)
@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' 🙂

Tom, I always go for a bombproof version, something like this:

SELECT

ID,

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

And it's not a bug, it's a feature. Catches folk out all the time 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For 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