Referencing AS columns

I'm just converting my Access database to SQL Server and have come across a number of differences. In Access I was able to do the following:
SELECT ZNew = Max( ..
ZNew2 = [ZNew] - Price
FROM ...
Can I reference ZNew in line 2 above, or do I need to duplicate the 'Max(' line? Help would be appreciated. Thanks.Try:

SELECT ZNew = Max([ZNew] - Price)
FROM ...

blindman|||Did you mean ZNew2 = Max([ZNew] etc. ...
The first line ZNew = Max( .. is an extensive CASE evaluation.
Nice not to have to repeat it in the ZNew2 expression.|||Can I reference ZNew in line 2 above, or do I need to duplicate the 'Max

No, seems to me that you must specify all the syntax :

SELECT ZNew = Max( ...),
ZNew2 = Max(...) - Price
FROM ...|||If you are using a case function you need to show us your statement.

blindman|||Here's the code:
SELECT TCode, ZValue = Max(CASE WHEN TCode = 'AAA' AND TValue > 1000 THEN 1000
WHEN TCode = 'BBB' AND TValue > 500 THEN 500 ELSE TValue END) ,
ZNew2 = ZValue - TKgValue|||You haven't included your FROM clause, so I can't tell if ZValue exists in an underlying table as well as being constructed from your case clause. If your tables have a field called ZValue in them, that is the value that will be used when you try to calculate ZNew2. Otherwise, I think you will get an error stating that SQL Server can't find field ZValue. You cannot create it and then reference it in the same statement, so you will have to repeat your case statement.

There are ways to avoid repeating the CASE statement, such as this method using nested queries:

SELECT TCode,
ZValue,
ZValue - TKgValue ZNew2
FROM (SELECT TCode,
Max(CASE WHEN TCode = 'AAA' AND TValue > 1000 THEN 1000
WHEN TCode = 'BBB' AND TValue > 500 THEN 500
ELSE TValue END) ZValue,
TKgValue
From YourTableReferencese) ZValueSubquery

blindman|||The Value does not exist and a nested query will not work in this case, so I'll just have to repeat the CASE statement. Thanks.|||I think your code would be easier to maintain, (and may run faster) if you use the nested query approach.

blindman