View Alias上的WHERE导致错误

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:

但是,如果我添加一个WHERE子句:

SELECT * FROM FooView WHERE ProcessCode > 0

I get this error:

我收到此错误:

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

将varchar值'-01-'转换为数据类型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?

为什么?由于位置必须采用1-2-100-0800-A格式,因此我看不出转换错误的原因。在WHERE有机会过滤结果之前,CAST是否可能失败?如果是这样,为什么第一个查询有效?

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:

这是在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,

3 个解决方案

#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.

SQL Server可以按其决定优化的顺序*评估WHERE / SELECT子句。除非将实现为INDEXED VIEW的视图扩展为外部查询,因此您的WHERE子句实际上被简化为视图,即

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.

因为表达式同时用于SELECT和WHERE子句,所以似乎SQL Server决定首先在原始检索中解析SELECT子句中的表达式。通过使用Ctrl-L查看查询执行计划可以很容易地看到这一点。您将看到SQL Server从表中进行单个检索,同时采用2个表达式,即位置和CAST(SUBSTRING(location,9,4)AS int)。

#2

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

这适用于Sql Server 2008,其他时髦的东西正在发生......

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

这是小提琴> http://sqlfiddle.com/#!3/3bcfd/2

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

编辑可以是下面建议的执行顺序,尝试使用in(使用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]-_')
)

#3

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.

演员在哪里失败,是的。事实上,如果我没有弄错的话,演员在SELECT之前就失败了,就在它查看表并确定位置列下的子串的形状之后。

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.

你似乎在计算返回的子字符串是错误的,因为如果你说格式确实是1-2-100-0800-A,那么SUBSTRING(str,9,4)应该返回0800,但它返回'-1-',这不是INT。

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

将你的陈述分解为较小的陈述。我先看看SUBSTRING的结果。

hope that helped.

希望有所帮助。

#1