WHERE on View Alias Causes Error

Question

I have a view like this:

SELECT location, CAST(SUBSTRING(location, 9, 4) AS int) AS ProcessCode
FROM   dbo.asset
WHERE  (status NOT IN ('INACTIVE', 'NOT READY', 'LIMITEDUSE'))
AND (location LIKE '[1-6]-[12]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-_')

If I call it like this, it works:

SELECT * FROM FooView

However, if I add a WHERE clause:

SELECT * FROM FooView WHERE ProcessCode > 0

I get this error:

Conversion failed when converting the varchar value '-01-' to data type int.

Why? Since location must be in the format 1-2-100-0800-A, I don't see how there can be a conversion error. Is it possible that the CAST fails before the WHERE has a chance to filter the results? If so, then why does the first query work?

EDIT - WORK-AROUND

I just had a co-worker suggest a good work-around. It works, but it still doesn't explain why the initial problem.

This is in the SELECT for ProcessCode:

CASE WHEN location LIKE '[1-6]-[12]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-_'
THEN CAST(SUBSTRING(location, 9, 4) AS int) ELSE 0 END AS ProcessCode, 

Answer 1

Change your view to this

SELECT location,
       CASE WHEN SUBSTRING(location, 9, 4) > ''
             AND SUBSTRING(location, 9, 4) NOT LIKE '%[^0-9]%' THEN
                 CAST(SUBSTRING(location, 9, 4) AS int) END AS ProcessCode
  FROM dbo.asset
 WHERE (status NOT IN ('INACTIVE', 'NOT READY', 'LIMITEDUSE'))
   AND (location LIKE '[1-6]-[12]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-_')

See this Connect item

SQL Server is free to evaluate the WHERE/SELECT clause in the order that it decides is optimized. A view unless materialized as an INDEXED VIEW is expanded to the outer query, so your WHERE clause is actually being streamlined into the view, i.e.

SELECT * FROM FooView WHERE ProcessCode > 0
 -- is really seen as
SELECT location, CAST(SUBSTRING(location, 9, 4) AS int) AS ProcessCode
FROM   dbo.asset
WHERE  (status NOT IN ('INACTIVE', 'NOT READY', 'LIMITEDUSE'))
AND (location LIKE '[1-6]-[12]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-_')
AND CAST(SUBSTRING(location, 9, 4) AS int) > 0 ---- << Expanded inline

Because the expression is used both for the SELECT and WHERE clauses, it seems SQL Server has decided to resolve the expression in the SELECT clause first in the original retrieval. This can easily be seen by using Ctrl-L to view the query execution plan. You will see that SQL Server makes a single retrieval from the table, taking 2 expressions, being location and CAST(SUBSTRING(location, 9, 4) AS int) at the same time.

Answer 2

This works on Sql Server 2008, something else funky is going on...

create view myview
AS
SELECT  CAST(SUBSTRING('1-2-100-0800-A', 9, 4) AS int) as ProcessCode
Where '1-2-100-0800-A' LIKE '[1-6]-[12]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-_'

GO

SELECT * FROM myview WHERE ProcessCode > 0

Here's the fiddle > http://sqlfiddle.com/#!3/3bcfd/2

Edit Could be the order of execution as suggested below, try it with an in (optimised with ids)

SELECT location, CAST(SUBSTRING(location, 9, 4) AS int) AS ProcessCode
FROM   dbo.asset 
Where id in(
 select id
 from dbo.asset 
 WHERE  (status NOT IN ('INACTIVE', 'NOT READY', 'LIMITEDUSE'))
 AND (location LIKE '[1-6]-[12]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-_')
)