1

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?

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
k-s
  • 2,192
  • 11
  • 39
  • 73
  • possible duplicate of [TSQL divide by zero encountered despite no columns containing 0](http://stackoverflow.com/questions/5191701/tsql-divide-by-zero-encountered-despite-no-columns-containing-0) – Martin Smith Sep 18 '13 at 11:45
  • 1
    Some additional links in [my answer here](http://stackoverflow.com/a/7192951/73226) – Martin Smith Sep 18 '13 at 11:46
  • But, I am fetching rows physically in outer table alias. not in same scope. Little confused here. – k-s Sep 18 '13 at 12:37
  • 1
    See http://connect.microsoft.com/SQLServer/feedback/details/537419/sql-server-should-not-raise-illogical-errors – Martin Smith Sep 18 '13 at 12:44
  • Great Thanks Martin for explanation. – k-s Sep 18 '13 at 13:02

1 Answers1

0

Answer is dedicated to Martin Smith.

http://connect.microsoft.com/SQLServer/feedback/details/537419/sql-server-should-not-raise-illogical-errors

Thanks Martin.

k-s
  • 2,192
  • 11
  • 39
  • 73