Using resultant CASE statements in WHERE Clause 1

[StevenK]

I have a number of queries against a SQL Server 7.0 database that make use of CASE statements - creating additional columns in the result set according to conditions (i.e. Company_Type, Entry_Date, etc.) I am able to use the resultant columns in the ORDER BY clause but when I attempt to use one of these fields in the WHERE condition I am returned a message indicating that the field does not exist.
Seeing as I am able to use these fields in the ORDER BY clause I would have thought that I would be able to use them in the WHERE condition.
Can anyone tell me if this is achievable ?
Thanks in advance,
Steve

 

[tlbroadbent]

It possible to use a Case statement in the Where clause but it is difficult to construct the correct syntax. Perhaps, you could post your query and we could suggest ways to make it work. Here are some examples that work.

Select EmployeeName, VacBal
From Employees
Where Active = 1
And VacBal > Case Type When 'D' Then 40 Else 80 End

Select
EmployeeName,
Dept=Case Status
When 1 Then CurrDept
Else PrevDept
End,
Job,
Status
From Employees
Where Case Status
When 1 Then CurrDept
Else PrevDept
End In (2234,3455,1234,9876) Terry

http://members.home.net/tlbroadbent/prog.htm

_____________________________________
Man's mind stretched to a new idea never goes back to its original dimensions. - Oliver Wendell Holmes