Error in varchar to numeric conversion with sub qu

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Error in varchar to numeric conversion with sub qu

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-09-18 : 08:45:42

I am facing an error even if I use numeric value in my outer query.


IF OBJECT_ID('tempdb..#TestNumeric') IS NOT NULL
BEGIN
    DROP TABLE #TestNumeric
END

CREATE TABLE #TestNumeric (ids INT, Valu NVARCHAR(50))
INSERT INTO #TestNumeric 
SELECT 1, '12345' UNION ALL
SELECT 1, '234234 23423' UNION ALL
SELECT 1, '234234' 

;WITH CTE 
AS
(
  SELECT ids, Valu
         , Convert(NUMERIC(18,0),Valu) as OriginalNumValue
         , ISNUMERIC(Convert(NUMERIC(18,0), Valu) ) IsNumericTrue
  FROM  #TestNumeric 
  WHERE ISNUMERIC(Valu) = 1
        AND Valu not like '%[^0-9]%'
)
SELECT  *
FROM    CTE 
WHERE   Valu = 100

DROP TABLE #TestNumeric

If I simply remove WHERE condition then it shows only numeric values. Even if it gives error and showing other rows in read.

Why it reads other rows in outer query as I have already filtered?

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-09-18 : 08:58:40

IF OBJECT_ID('tempdb..#TestNumeric') IS NOT NULL
BEGIN
DROP TABLE #TestNumeric
END

CREATE TABLE #TestNumeric (ids INT, Valu NVARCHAR(50))
INSERT INTO #TestNumeric
SELECT 1, '12345' UNION ALL
SELECT 1, '23423' UNION ALL
SELECT 1, '234234'

;WITH CTE
AS
(
SELECT ids,Valu
, Convert(NUMERIC(18,0),Valu) as OriginalNumValue
, ISNUMERIC(Convert(NUMERIC(18,0), Valu) ) IsNumericTrue
FROM #TestNumeric
WHERE ISNUMERIC(Valu) = 1
AND Valu not like '%[^0-9]%'
)
SELECT *
FROM CTE
WHERE Valu = 100

DROP TABLE #TestNumeric

veeranjaneyulu

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-09-18 : 10:15:45

Please read my question first.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-18 : 10:29:43

This behaviour is documented and was changed with SQL Server 2005.
The Query Optimizer is more aggressive in how to optimize the queries and therefore can "jump scopes" too see what can be done.

See "Expressions in queries" here http://technet.microsoft.com/en-us/library/ms143359(v=sql.90).aspx

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-18 : 10:36:48

[code]DECLARE @Sample TABLE
(
ID INT,
Valu NVARCHAR(50)
);

INSERT @Sample
SELECT 1, '12345' UNION ALL
SELECT 1, '234234 23423' UNION ALL
SELECT 1, '234234';

SELECT ID,
Valu
FROM @Sample
WHERE CASE
WHEN Valu LIKE '%[^0-9]%' THEN 0
WHEN ISNUMERIC(Valu) = 0 THEN 0
WHEN Valu = 100 THEN 1
ELSE 0
END = 1;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-09-18 : 11:07:56

Great!! Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources