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
当我尝试执行以下操作时收到错误：
Msg 207,Level 16,State 1,Line 13 Invalid column name ‘RowID’.
只是想知道我如何引用这个临时列？我搜索了以前的帖子,建议使用’have’来实现这一点,但这似乎也不起作用.
解决方法
一种解决方案是对整个语句进行子选择,在结果上应用where子句
select *
from (
select cast(de.ApprovalOrder AS VARCHAR(32))
+ cast(de.EntityCode AS VARCHAR(32))
+ isnull(cast(de.DelegationCode AS VARCHAR(32)),*
from workflow.delegation_engine de
) de
where de.RowID IS NOT NULL
另一种解决方案可能是重复WHERE子句中的整个子句
select cast(de.ApprovalOrder AS VARCHAR(32))
+ cast(de.EntityCode AS VARCHAR(32))
+ isnull(cast(de.DelegationCode AS VARCHAR(32)),*
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
select cast(de.ApprovalOrder AS VARCHAR(32))
+ cast(de.EntityCode AS VARCHAR(32))
+ isnull(cast(de.DelegationCode AS VARCHAR(32)),*
from workflow.delegation_engine de
where de.ApprovalOrder IS NOT NULL
AND de.EntityCode IS NOT NULL
