在where子句中不能使用临时列？

select  cast(de.ApprovalOrder AS VARCHAR(32)) 
            + cast(de.EntityCode AS VARCHAR(32)) 
            + isnull(cast(de.DelegationCode AS VARCHAR(32)), '') as 'RowID' ,
            *
from    workflow.delegation_engine de
where   RowID <> NULL

When I try to execute the following I receive the error:

当我尝试执行以下操作时收到错误:

Msg 207, Level 16, State 1, Line 13 Invalid column name 'RowID'.

消息207,级别16,状态1,行13无效的列名称'RowID'。

Just wondering how I can reference this temporary column? I searched for previous postings which suggested using 'having' for this however that doesn't appear to work either.

只是想知道我如何引用这个临时列?我搜索了以前的帖子,建议使用'have'来做这件事,但这似乎也不起作用。

2 个解决方案

#1

One solution would be to make a subselect of the entire statement, applying the where clause on its result

一种解决方案是对整个语句进行子选择,对其结果应用where子句

select  *
from    (
          select  cast(de.ApprovalOrder AS VARCHAR(32)) 
                  + cast(de.EntityCode AS VARCHAR(32)) 
                  + isnull(cast(de.DelegationCode AS VARCHAR(32)), '') as 'RowID'
                  , *
          from    workflow.delegation_engine de
        ) de 
where   de.RowID IS NOT NULL

Another solution could be to repeat the entire clause in the WHERE clause

另一种解决方案可能是重复WHERE子句中的整个子句

select  cast(de.ApprovalOrder AS VARCHAR(32)) 
        + cast(de.EntityCode AS VARCHAR(32)) 
        + isnull(cast(de.DelegationCode AS VARCHAR(32)), '') as 'RowID' ,
        *
from    workflow.delegation_engine de
where   cast(de.ApprovalOrder AS VARCHAR(32)) 
        + cast(de.EntityCode AS VARCHAR(32)) 
        + isnull(cast(de.DelegationCode AS VARCHAR(32)), '') IS NOT NULL

Or you could test each individual field for NULL

或者您可以测试每个字段的NULL

select  cast(de.ApprovalOrder AS VARCHAR(32)) 
        + cast(de.EntityCode AS VARCHAR(32)) 
        + isnull(cast(de.DelegationCode AS VARCHAR(32)), '') as 'RowID' ,
        *
from    workflow.delegation_engine de
where   de.ApprovalOrder IS NOT NULL
        AND de.EntityCode IS NOT NULL

#2

You'd either have to use the express in the WHERE clause, or use your SELECT query as a subquery, like so:

您必须使用WHERE子句中的express,或者将SELECT查询用作子查询,如下所示:

select *
from
(
    select  cast(de.ApprovalOrder AS VARCHAR(32))  
                + cast(de.EntityCode AS VARCHAR(32))  
                + isnull(cast(de.DelegationCode AS VARCHAR(32)), '') as RowID, 
                * 
    from    workflow.delegation_engine de 
)
where RowID is not NULL

Or, the sloppier (in my opinion) route would be:

或者,更粗略的(在我看来)路线将是:

select  cast(de.ApprovalOrder AS VARCHAR(32))    
            + cast(de.EntityCode AS VARCHAR(32))    
            + isnull(cast(de.DelegationCode AS VARCHAR(32)), '') as RowID,   
            *   
from    workflow.delegation_engine de   
where   cast(de.ApprovalOrder AS VARCHAR(32))    
            + cast(de.EntityCode AS VARCHAR(32))    
            + isnull(cast(de.DelegationCode AS VARCHAR(32)), '') is not null

I would go with the first solution every time.

我每次都会选择第一个解决方案。

Also notice that I've changed your WHERE clause from

另请注意,我已经更改了WHERE子句

RowID <> NULL

RowID is not NULL

This is because <> NULL will never evaluate to true. SQL Server tests for NULL (i.e. unknown) using IS and IS NOT.

这是因为<> NULL永远不会评估为true。 SQL Server使用IS和IS NOT测试NULL(即未知)。

#1