Invalid column name, on select-statement created columns

Question

I have simplified my problem to the following select statement.

select 
   u.UserId,
   aVariable = cast((case when exists(select * from TblUser u where u.Disabled=1) then 1 else 0 end) as bit), 
from TblUser u
where aVariable = 1

aVariable is not a column of a table, but just a column that gets a value in this select statement.

Is there a way to do the above without getting the Invalid column name aVariable error?

Answer 1

select  
   u.UserId, 
   1 as 'aVariable'
from TblUser u 
-- where aVariable = 1 - Omit this

Answer 2

The SELECT must look like so:

select 
   u.UserId,
   1 as aVariable
from TblUser u

Answer 3

select q.* from (
select 
   u.UserId,
   cast((case when exists(select * from TblUser u where u.Disabled=1) then 1 else 0 end) as bit) as aVar, 
from TblUser u
)q 
where q.aVar = 1

Answer 4

The statement you picked as correct makes no sense.

select q.* from (
select 
   u.UserId,
   cast((case when exists(select * from TblUser u where u.Disabled=1) then 1 else 0 end) as bit) as aVar, 
from TblUser u
)q 
where q.aVar = 1

The statement above says select all users from the tbluser if there is one user who is disabled.

I think you want to see the users in the table who are disabled. If that is so then you want the following select statement:

SELECT userid, disabled as aVar
FROM TblUser
WHERE disabled = 1

Give this a shot.

Prior answered deleted since the question is a tad unclear.

Answer 5

You need to do this:

select 
   u.UserId,
   aVariable = cast((case when exists(select * from TblUser u where u.Disabled=1) then 1 else 0 end) as bit), 
from TblUser u
where cast((case when exists(select * from TblUser u where u.Disabled=1) then 1 else 0 end) as bit) = 1