Cannot use a temporary column in the where section?

Question

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 do the following, I get an error message:

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

Just wondering how can I refer to this temporary column? I was looking for previous publications that suggested using "availability" for this, but that doesn't work either.

Answer 1

One solution would be to subtask the entire operator by applying the where clause to 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 

---

Another solution might be to repeat the whole sentence 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 

---

Or you can check 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 

Answer 2

You need to either use express in the WHERE or use your SELECT query as a subquery, for example:

 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 route (in my opinion) 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 note that I changed the WHERE from

 RowID <> NULL 

For

 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 .