Invalid Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Invalid Column

Author

Topic

Qnavry
Starting Member

4 Posts

Posted - 2009-01-05 : 16:10:44

I am getting a error when I run the select statement below. I know this is probably a easy fix. what am I missing?

select
case col1 when 46 then 'Yes' else 'No' end as Test,
col2,col3 from table1
where Test = 'No'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-05 : 16:18:29

You can't use a column Alias in the WHERE criteria.

Try one of these:


select case col1 when 46 then 'Yes' else 'No' end as Test
       ,col2
       ,col3 
from   table1
where  case col1 when 46 then 'Yes' else 'No' end = 'No'

select Test
       ,col2
       ,col3 
from   (
       select case col1 when 46 then 'Yes' else 'No' end as Test
              ,col2
              ,col3 
       from   table1
       ) d
where  test = 'No'

Be One with the Optimizer
TG

Qnavry
Starting Member

4 Posts

Posted - 2009-01-05 : 16:44:45

Thanks

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-01-06 : 15:49:39

Wouldn't this be much simpler?? Depending on row count, it's possible a performance hit, if any, would be minimal.

select
'No' as Test,
col2,col3 from table1
where col1 <> 46

edit - Although, TG, your's are more elegant.

Terry

-- Procrastinate now!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-06 : 17:11:08

Definately. (more simple usually seems more elegant to me:) I assumed this was not the OPs actual query and they were just generally confused about how to restrict rows based on an expression.

-- why procrastinate now when you can just nip it in the bud later?

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources