Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    88

    Unanswered: 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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try:

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

    blindman

  3. #3
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    88
    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.

  4. #4
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524

    Re: Referencing AS columns

    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 :

    Code:
    SELECT ZNew = Max( ...), 
                ZNew2 = Max(...) - Price
    FROM ....

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you are using a case function you need to show us your statement.

    blindman

  6. #6
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    88
    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
    Last edited by ingineu; 07-12-03 at 21:22.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  8. #8
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    88

    Smile

    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.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think your code would be easier to maintain, (and may run faster) if you use the nested query approach.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •