Error converting varchar to numeric only in where clause
-
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:
SELECTID,Value,x.NumericValueFROM #TestCROSS APPLY (SELECT [NumericValue] = CASEWHEN ISNUMERIC(Value) = 1 THEN CAST(Value AS NUMERIC(10,2))ELSE 0 END) xWHERE NumericValue <> 0And 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 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, as far as I know that's not bombproof. While under most circumstances the predicate will be filtered prior to the activation of the cross apply, but I don't believe that guarantees it.
The only bombproof way of single query execution for an overloaded column that I'm familiar with is using (FORCE ORDER).
Basically this structure:
SELECTa.id,CONVERT( INT, a.overloadedField) AS NumValueFROM(SELECTid,overloadedFieldFROMEAVtableWHEREISNUMERIC( overloadedField) = 1) AS aOPTION (FORCE ORDER)
- Craig FarrellNever 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 -
Evil Kraig F (4/25/2013)
Chris, as far as I know that's not bombproof. While under most circumstances the predicate will be filtered prior to the activation of the cross apply, but I don't believe that guarantees it.The problem arises when the two predicates in OP's original query are applied "the wrong way round" i.e. with the NUMERIC() test after the CAST(...) <> 0. If there's only one predicate -
CASEWHEN isnumeric([tempdb].[dbo].[#Test].[Value])=(1) THEN CONVERT(numeric(10,2),[tempdb].[dbo].[#Test].[Value],0)ELSE (0.00) END<>(0.00)- how can it fail?
The only bombproof way of single query execution for an overloaded column that I'm familiar with is using (FORCE ORDER).
Basically this structure:
SELECTa.id,CONVERT( INT, a.overloadedField) AS NumValueFROM(SELECTid,overloadedFieldFROMEAVtableWHEREISNUMERIC( overloadedField) = 1) AS aOPTION (FORCE ORDER)That's interesting. I've not used this query hint before, but understood that it operated on the order of joins in the FROMlist, rather than the order of searches in a predicate. It doesn't work on this simple query:
SELECT *, CONVERT(numeric(14,2),Value) as AmountFROM #TestWHERE (#Test.[PathID]=(30) OR #Test.[PathID]=(31))AND CONVERT(numeric(14,2),#Test.[Value],0)=(0.00)OPTION (FORCE ORDER)It generates the CAST error and the plan shows the predicates in the unwanted order, with or without the hint.
Here's the sample dataset:
DROP TABLE #TestCREATE TABLE #Test (ID INT IDENTITY (23,6921), [PathID] INT, Value VARCHAR(25))INSERT INTO #Test (PathID, Value) VALUES(30, '3.14'),(29, 'Twenty seven'),(32,'24/04/2013'),(31,'100'),(7,'Yesterday'),(30,'0')Tweaking the skeleton query "basically this structure" to work with the same dataset using the same filters also fails whether or not the hint is used:
SELECTa.id,CONVERT( INT, a.Value) AS NumValueFROM(SELECTid,ValueFROM#TestWHEREISNUMERIC( Value) = 1) AS aWHERE CONVERT( INT, a.Value) <> 0OPTION (FORCE ORDER)- and it fails for the same reason as the OP's original query.
“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 -
Personally, for readability and your specific requirement, as long as you can guarantee the data type for PathIDs 30 and 31, then you can use this:
select DataId, PathId, Valuefrom dbo.TestTableWHEREPathID in (30,31)ANDCASEWHEN PathID in (30,31) THEN CONVERT(NUMERIC(14,2),Value)ELSE 0END <> 0;MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0); - Forum Etiquette: How to post Reporting Services problems [/url]
- Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden [/url]
- How to Post Performance Problems - by Gail Shaw [/url]
I agree, this is the most readable, AND it works
mister.magoo (4/26/2013)
Personally, for readability and your specific requirement, as long as you can guarantee the data type for PathIDs 30 and 31, then you can use this:
select DataId, PathId, Value
from dbo.TestTable
WHERE
PathID in (30,31)
AND
CASE
WHEN PathID in (30,31) THEN CONVERT(NUMERIC(14,2),Value)
ELSE 0
END <> 0;
I think this is logically the same as the "bombproof" query I posted earlier (even though the plan for my version includes a compute scalar) - the code in the CROSS APPLY can be dropped down to the WHERE clause:
SELECT
DataId,
Value,
x.NumericValue
FROM #Test
CROSS APPLY (
SELECT [NumericValue] = CASE
WHEN PathID in (30,31) THEN CAST(Value AS NUMERIC(10,2))
ELSE 0 END
) x
WHERE PathID in (30,31)
AND [NumericValue] <> 0
SELECT
DataId,
Value,
NumericValue = CAST(Value AS NUMERIC(10,2))
FROM #Test
WHERE PathID in (30,31)
AND CASE
WHEN PathID in (30,31) THEN CAST(Value AS NUMERIC(10,2))
ELSE 0 END <> 0
The end result is the same – value is only CAST and compared in a single expression which includes the numeric check:
([tempdb].[dbo].[#Test].[PathID]=(30) OR [tempdb].[dbo].[#Test].[PathID]=(31))
AND CASE
WHEN [tempdb].[dbo].[#Test].[PathID]=(31) OR [tempdb].[dbo].[#Test].[PathID]=(30)
THEN CONVERT(numeric(14,2),[tempdb].[dbo].[#Test].[Value],0)
ELSE (0.00) END
<>(0.00)
Your version also works if the datatype for PathID is changed to VARCHAR:
DROP TABLE #Test
CREATE TABLE #Test (DataId INT IDENTITY (23,6921), [PathID] VARCHAR(2), Value VARCHAR(25))
INSERT INTO #Test (PathID, Value) VALUES
(30, '3.14'),
(29, 'Twenty seven'),
(32,'24/04/2013'),
(31,'100'),
(7,'Yesterday'),
(30,'0')
select DataId, PathId, Value
from #Test
WHERE
PathID in (30,31)
AND
CASE
WHEN PathID in (30,31) THEN CONVERT(NUMERIC(14,2),Value)
ELSE 0
END <> 0;
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 (4/26/2013)
I think this is logically the same as the "bombproof" query I posted earlier (even though the plan for my version includes a compute scalar) - the code in the CROSS APPLY can be dropped down to the WHERE clause:
...
The end result is the same – value is only CAST and compared in a single expression which includes the numeric check:
Absolutely, yes - I agree - just wanted to offer that as an alternative "code format", but your "Bombproof" code removes the need for my caveat of "as long as you can guarantee the data type", so in the end I would probably use yours anyway 
because I like to be assured of that sort of thing by the code, not by the person...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);