2

I was trying out a query and it was returning an error which I cannot explain. (5 th query ).
Can someone help me understand what is causing the error. 

DROP TABLE #temp  

SELECT * INTO #temp  
FROM  
(  
SELECT 'A_100' Col  
union all  
SELECT 'A_101'   
union all  
SELECT 'A_102'  
union all  
SELECT 'A_103'  
union all  
SELECT 'A_104'  
union all  
SELECT 'B_104'  
) a  

-- 1
SELECT * FROM #temp where col like 'A%'
     
-- 2
SELECT replace(col,'A_','')  FROM #temp where col like 'A%'
     
--3
SELECT cast(replace(col,'A_','') as INT) FROM #temp where col like 'A%'     

-- 4
SELECT * FROM
(
SELECT cast(replace(col,'A_','') as INT) Num
 FROM #temp a where col like 'A%'
) A
     
-- 5
SELECT * FROM
(
SELECT cast(replace(col,'A_','') as INT) Num
 FROM #temp a where col like 'A%'
) A
WHERE A.Num>102

 

bummi
  • 27,123
  • 14
  • 62
  • 101
Aravind V Shibu
  • 56
  • 3
  • Just two questions: what exactly is the error message you get? Have you tried doing:- SELECT * FROM ( SELECT cast(replace(col,'A_','') as INT) Num FROM #temp a where col like 'A%' ) A where A.Num > 201 ? ........... Probably it should be SELECT * FROM ( (SELECT cast(replace(col,'A_','') as INT) Num FROM #temp a where col like 'A%' ) as A where A.num > 102) – 5122014009 Oct 07 '14 at 11:15
  • @Sayuri the error messagw that I am getting is "convertion failed when coverting b_104 to datatype int" – Aravind V Shibu Oct 07 '14 at 11:21
  • 1
    Related [Conversion failed when converting the varchar value to int](http://stackoverflow.com/questions/2764424/conversion-failed-when-converting-the-varchar-value-to-int) – bummi Oct 07 '14 at 11:37
  • @ypercube Throwing me up against Gordon and Aaron huh :) that's low. – Daniel E. Oct 07 '14 at 12:15
  • @DanielE. I think Aaron has an answer in another, newer question which is better but I can't find it. – ypercubeᵀᴹ Oct 07 '14 at 12:19
  • 1
    And now it's there (the better answer) – ypercubeᵀᴹ Oct 07 '14 at 13:47

2 Answers2

1

@Sayuri That does not solve the error, and OP does do that check, the error should not be happening.

It also is not what is causing the error, as SELECT replace(col,'A_','') from #temp where col like 'A%' correctly outputs 100,101,102,103,104 there is something getting cached that shouldn't be. If it splits into a new #tmp table first instead of a nested select it then works correctly.

SELECT replace(col,'A_','') [Num]
INTO #tmp 
FROM #temp b 
WHERE col LIKE 'A%'

SELECT [Num]
FROM #tmp 
WHERE [Num] > 101

Complete Runnable Code

SELECT *
INTO #temp
FROM (
    SELECT 'A_100' Col
    UNION ALL
    SELECT 'A_101'
    UNION ALL
    SELECT 'A_102'
    UNION ALL
    SELECT 'A_103'
    UNION ALL
    SELECT 'A_104'
    UNION ALL
    SELECT 'B_104'
    ) a


SELECT cast(replace(col, 'A_', '') as int) Num
INTO #tmp
FROM #temp b
WHERE col LIKE 'A%'

SELECT *
FROM #tmp
WHERE Num > 101

DROP TABLE #tmp
DROP TABLE #temp
Daniel E.
  • 2,029
  • 3
  • 22
  • 28
  • You'll still get the same error with the first statement of yours. If you only `SELECT Num ... WHERE ...` into the temp table and then do `SELECT REPLACE...` from it, it will work. – ypercubeᵀᴹ Oct 07 '14 at 11:43
  • This runs just fine with OP's input data, the conversion is implicit in the second where clause. – Daniel E. Oct 07 '14 at 11:45
  • @Daniel E. Are you sure the '104' you see is the casting from 'B_104' and not 'A_104'? I haven't executed the query but it clearly looks like the "replace" should fail for 'B_104' – 5122014009 Oct 07 '14 at 11:47
  • I changed B_104 to B_105 just to check, it works both ways. – Daniel E. Oct 07 '14 at 11:47
  • Why would the replace fail? there is a `where` filter... – Daniel E. Oct 07 '14 at 11:48
  • For the same reason that it fails in the OP's code. See the link in @bummi's comment. – ypercubeᵀᴹ Oct 07 '14 at 11:50
  • OPs code threw the error and mine does not, I don't know what else to tell you. – Daniel E. Oct 07 '14 at 11:52
  • OK, read the link, probably the same error, I suppose this is a working work around then. – Daniel E. Oct 07 '14 at 11:54
  • Yes, I tested your code and seems to work. Not sure if it is a reliable workaround though. – ypercubeᵀᴹ Oct 07 '14 at 11:56
  • @Sayuri the OP is trying to filter out the `B_104`, explicitly. We don't want the 104 from that line. – Daniel E. Oct 07 '14 at 11:57
  • if the order of operations is what is causing the bug this forces SQL server to do it in a more linear fashion.You will have the overhead of writing the records to the new `#tmp` table. 60% of the query cost here was writing those records to the new `#tmp` table... – Daniel E. Oct 07 '14 at 11:58
0

Ok, the issue lies in this.

Your query is as follows: SELECT cast(replace(col,'A_','') as INT) Num FROM #temp a where col like 'A%'

It first tries to replace a col "A_" that matches 'A%' and casts it into an INT. Now, since 'b_104' does not meet the first criterion of being like 'A%'; replacement does not happen; hence casting to INT fails. You can try doing the following and see the output for yourself:

                      SELECT replace(col,'A_','') from #temp where col like 'A%'

And then try a select * on #temp

5122014009
  • 3,766
  • 6
  • 24
  • 34