April 25, 2013 at 4:19 am
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 😉
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
April 25, 2013 at 1:57 pm
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:
SELECT
a.id,
CONVERT( INT, a.overloadedField) AS NumValue
FROM
(SELECT
id,
overloadedField
FROM
EAVtable
WHERE
ISNUMERIC( overloadedField) = 1
) AS a
OPTION (FORCE ORDER)
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
April 26, 2013 at 1:41 am
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 -
CASE
WHEN 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:
SELECT
a.id,
CONVERT( INT, a.overloadedField) AS NumValue
FROM
(SELECT
id,
overloadedField
FROM
EAVtable
WHERE
ISNUMERIC( overloadedField) = 1
) AS a
OPTION (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 Amount
FROM #Test
WHERE (#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 #Test
CREATE 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:
SELECT
a.id,
CONVERT( INT, a.Value) AS NumValue
FROM
(SELECT
id,
Value
FROM
#Test
WHERE
ISNUMERIC( Value) = 1
) AS a
WHERE CONVERT( INT, a.Value) <> 0
OPTION (FORCE ORDER)
- and it fails for the same reason as the OP's original query.
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
April 26, 2013 at 3:45 am
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;
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);April 26, 2013 at 3:56 am
I agree, this is the most readable, AND it works 🙂
April 26, 2013 at 4:25 am
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, Valuefrom 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
April 26, 2013 at 4:33 am
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);April 26, 2013 at 1:32 pm
ChrisM@Work (4/26/2013)
- how can it fail?
That can't. My apologies. I rushed myself and didn't really think it through. Thanks for taking the time to show me in a slightly different way. It's penetrated my thick skull now. 🙂
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.
Well, because the join list is forced, the subqueries are supposed to be ran in their order so nothing 'leaks out' into the main query. Unfortunately, predicates are leaking 'into' the earlier query, as you've shown (and is repeatable on my system).
I need to go back to the drawing boards on a few queries I've built this way and fix them. Apparently I've been getting lucky, because I've used this as a fix in a number of places. I'm wondering if it's because it's just the single 'inner query' and if it's because there's no join order to actually force it to calculate, but even some manipulation on that idea doesn't function.
DROP TABLE #Test
CREATE 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')
SELECT
a.id,
CONVERT( NUMERIC(10,2), a.Value) AS NumValue
FROM
(SELECT DISTINCT id FROM #Test) AS b
JOIN
(SELECT
id,
Value
FROM
#Test
WHERE
ISNUMERIC( Value) = 1
) AS a
ON b.id = a.id
WHERE
CONVERT( NUMERIC(10,2), a.Value) <> 0
OPTION (FORCE ORDER)
Sorry about that Chris. I'll be over there hashing things out with some beer popsicles and dust bunnies if I can get Jeff to lend me them. That was an abrupt awakening that I'm doing things wrong... Appreciate it.
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
April 29, 2013 at 1:10 am
Evil Kraig F (4/26/2013)
...Thanks for taking the time to show me in a slightly different way...
Welcome. It's one of those things we take for granted after a while, not really thinking about it but knowing intuitively "I can't do it like that, it doesn't work!"
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
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply