Aliased Column and Where Clauses

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)

Aliased Column and Where Clauses

Author

Topic

asn187
Starting Member

9 Posts

Posted - 2007-04-23 : 10:42:37

I want to use an aliased field with a where caluse as below

select member_ID,
CASE
WHEN (status_id & (16 | 128)) = (16 | 128) OR (status_id & 16 ) = 0
THEN CAST(1 AS bit) ELSE CAST(0 AS bit)
END AS IsMigrated
FROM member
LEFT OUTER JOIN language ON language.language_id = member.country_id
WHERE language = 'Deutsch'
-- AND IsMigrated = 1

However i keep getting "Invalid column name 'IsMigrated'" when i uncomment the AND IsMigrated = 1 line.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-23 : 10:46:17

[code]Select * from
(
select
member_ID,
CASE
WHEN (status_id & (16 | 128)) = (16 | 128) OR (status_id & 16 ) = 0 THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END AS IsMigrated
FROM member
LEFT OUTER JOIN language ON language.language_id = member.country_id
WHERE language = 'Deutsch'
) t
Where
t.IsMigrated = 1[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

asn187
Starting Member

9 Posts

Posted - 2007-04-23 : 10:47:37

i get the below when executing:

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 't' does not match with a table name or alias name used in the query.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-23 : 10:50:31

What is the query you wrote?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

asn187
Starting Member

9 Posts

Posted - 2007-04-23 : 10:51:16

no problem fixed the problem thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources