不能在 where 子句中使用临时列? [英] Can't use temporary column in where clause?
问题描述
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:
消息 207,级别 16,状态 1,第 13 行无效的列名称RowID".
Msg 207, Level 16, State 1, Line 13 Invalid column name '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.
推荐答案
一种解决方案是对整个语句进行子选择,对其结果应用 where 子句
One solution would be to make a subselect of the entire statement, applying the where clause on its result
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
<小时>
另一种解决方案可能是在 WHERE 子句中重复整个子句
Another solution could be to repeat the entire clause in the WHERE clause
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
<小时>
或者您可以测试每个单独的字段是否为 NULL
Or you could test each individual field for 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
这篇关于不能在 where 子句中使用临时列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!