CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005t-sql
Michael A
Michael A

Reputation: 9900

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:

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.

Upvotes: 7

Views: 6303

Answers (2)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

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

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

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

Upvotes: 10

user596075
user596075

Reputation:

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

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

RowID <> NULL

To

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.

Upvotes: 5

Related Questions