-1

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?

Stavros
  • 5,802
  • 13
  • 32
  • 45
  • The example doesn't make much sense, even if it were correct syntax, it's effectively the same as saying "where 1=1". Can you give a more concrete example? – TML Sep 01 '10 at 10:21
  • you need the update statement -- see my answer. – Hogan Sep 01 '10 at 10:30
  • I don't understand how the answer you picked as correct works -- see my edited answer. – Hogan Sep 01 '10 at 23:18
  • 1
    @Hogan: Forget the logic inside the sql-statement. What I needed was about syntax. It was about how to use a select-statement-column in my "where" clause. – Stavros Sep 09 '10 at 07:07

4 Answers4

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
codingbadger
  • 42,678
  • 13
  • 95
  • 110
Alex Reitbort
  • 13,504
  • 1
  • 40
  • 61
1

The SELECT must look like so:

select 
   u.UserId,
   1 as aVariable
from TblUser u
Aaron Digulla
  • 321,842
  • 108
  • 597
  • 820
1

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.

Hogan
  • 69,564
  • 10
  • 76
  • 117
  • aVariable is not a parameter, it's a column name and I want to use it in a where clause. – Stavros Sep 01 '10 at 10:27
  • you can't change column values in a select statement -- you can only read values... if you want a new output column use `as` if you want to change the table use update. – Hogan Sep 01 '10 at 10:36
  • Invalid column name 'aVariable'. :( – Stavros Sep 01 '10 at 10:36
  • does your table have a column named avariable? if it does not have it you can't change it. – Hogan Sep 01 '10 at 10:37
  • using the @ sign as a prefix is not only for parameters, it is also used for local variables. if you want a local variable you need to prefix it with an @ and have a declare statement. – Hogan Sep 01 '10 at 10:38
  • aVariable is not a column of a table, but just a column that gets a value in this select statement. – Stavros Sep 01 '10 at 14:10
  • Forget the logic inside the sql-statement. What I needed was about syntax. It was about how to use a select-statement-column in my "where" clause. – Stavros Sep 09 '10 at 07:08
  • @stavros : maybe you mean a select statement that has a case? But there is no magic -- just include the logic in the where statement, it works find. Anything in the select statement can also be in the where clause, there is no trick. – Hogan Sep 09 '10 at 09:39
1

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
codingbadger
  • 42,678
  • 13
  • 95
  • 110
  • I was looking for something better, because the select statement inside the case block, is more complicated and slow :( – Stavros Sep 01 '10 at 10:33
  • Maybe you could wrap the case statement in to a function - that may speed it up. It will certainly make it more readable. Are you able to actually post the real problem. Someone maybe be able to provide a different solution all together. – codingbadger Sep 01 '10 at 10:38