CTE 返回错误 [英] CTE returning error

问题描述

我编写了一个 CTE 来从数据集中删除非数值，然后获取某个范围内数值的计数.

I wrote a CTE to remove non numeric values from a data set, then get a count of numeric values within a range.

WITH    dtr
      AS ( SELECT   resultlevel r
           FROM     dbo.Result
           WHERE    DrugID = 'AMP'
                    AND ISNUMERIC(ResultLevel) = 1
                    AND AuditStamp > '1/1/2016'
                    AND DeleteFlag = 0
         )
SELECT  COUNT(*)
FROM    dtr
WHERE   CONVERT(INT, r) BETWEEN 50 AND 75

这会在 SMS 中返回错误

This returns an error in SMS

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'PND            ' to data type int.

如果没有 CTE 中的 'dtr' 查询，这个错误是完全可能的.

This error is completely possible without the 'dtr' query in the CTE.

当我重写这个时，它不是点击率而是一个 TEMP 表，它可以工作.

When I rewrite this, instead of a CTR, but a TEMP table, it works.

SELECT  resultlevel r
INTO    #d
FROM    dbo.Result
WHERE   DrugID = 'AMP'
        AND ISNUMERIC(ResultLevel) = 1
        AND AuditStamp > '1/1/2016'
        AND DeleteFlag = 0

SELECT  COUNT(*)
FROM    #d
WHERE   CONVERT(INT, r) BETWEEN 50 AND 75

所以我的问题是为什么?我一直认为 CTE 就像创建一个 TEMP 表.

So my questions is why?? I have always thought a CTE was like creating a TEMP table.

测试数据

if object_id('tempdb..#temp') is not null drop table #temp

create table #temp (result char(5)) 
insert into #temp (result) values 
('1'),('A'),('>2'),('PEN ') ,('@3'),('-2'),('-33') 


;with isnum AS ( 
SELECT result 
FROM #temp 
WHERE ISNUMERIC(result) = 1)

--Selecting from the CTE yields 1, -2, and -33 all of which can be converted to INT
--Running the query with the where clause causes the conversion error
SELECT 
    result, 
    ISNUMERIC(result)
FROM isnum
--WHERE CONVERT(INT,result) > 1

推荐答案

在 SQL Server 中有 SELECT 语句的逻辑处理顺序，它决定了一个步骤中定义的对象何时可用于后续步骤中的子句:

In SQL Server there is Logical Processing Order of the SELECT statement, which determines when the objects defined in one step are made available to the clauses in subsequent steps:

1. 来自
2. 开启
3. 加入
4. 哪里
5. 分组依据
6. WITH CUBE 或 WITH ROLLUP
7. 拥有
8. 选择
9. 与众不同
10. 订购者
11. 顶部

这是您查询的处理方式，您的查询看起来非常好.但有时，SQL Server 决定不遵循此顺序以优化您的查询.

This is how your query is going to be proccesed and your query looks perfectly fine. But sometimes, the SQL Server decides not to follow this order in order to optimize your query.

在您的情况下，SQL Server 可能会在应用 where isnumericconvert 功能> 过滤.

In your case, the SQL Server might be simplyfing/transforming your query into another and performing the convert function, before applying the where isnumeric filtering.

如果我们让您的查询稍微复杂一些(但仍然给出相同的结果)，那么这次 SQL Server 正在正确执行代码:

If we made your query a little more complex (but still giving the same results), the SQL Server is executing the code correctly this time:

;with isnum AS ( 
    SELECT result
    FROM #temp 
    WHERE ISNUMERIC(result) = 1
    GROUP BY result
    HAVING MAX(result) = result
)
SELECT 
    result, 
    ISNUMERIC(result)
FROM isnum
WHERE CONVERT(INT,result) > 1;

在你的情况下(这就是我在不同类型存储在一列中的情况下所做的)，你可以简单地使用 TRY_CONVERT 函数:

In your case (and this is what I am doing in such situations when different types are stored in one column), you can simply use TRY_CONVERT function:

;with isnum AS ( 
SELECT result 
FROM #temp 
WHERE ISNUMERIC(result) = 1)

SELECT 
    result, 
    ISNUMERIC(result)
FROM isnum
WHERE TRY_CONVERT(INT, result) > 1

这篇关于CTE 返回错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！