CODEWITHSUNDEEP
sql-servert-sql
Bob Horn
Bob Horn

Reputation: 34297

WHERE on View Alias Causes Error

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,

Upvotes: 5

Views: 148

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

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.

Upvotes: 4

jenson-button-event
jenson-button-event

Reputation: 18951

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]-_')
)

Upvotes: 1

Shokodemon
Shokodemon

Reputation: 5

the cast fails before the where, yes. Indeed the cast is failing before the SELECT itself, if i'm not mistaken, right after it looks at the table and determines the shape of the substring under the location column.

You seem to be making a miscalculation as to what the Substring being returned is, because if you say the format indeed is 1-2-100-0800-A, then SUBSTRING(str,9, 4) should return 0800, but it's returning '-1-', which is not an INT.

dissect your statement into smaller ones. i'd look into the result of SUBSTRING first.

hope that helped.

Upvotes: 0

Related Questions