Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2005

    Unanswered: Referencing a calculated field in a query

    I would have a question, if it is possible to reference a calculated field in T-SQL query. The following expression does not work in SQLExpress 2005

    SELECT Qty, UnitPrice, Tax, Qty*UnitPrice as Expr1, Expr1*(1.0 + Tax) AS Expr2

    This problem has occurred after upsizing to SQLExpress from Access XP/Jet Engine. Since Access does not have any issue with the expression above, the SQLExpress does not even accept it.

    The only way how to avoid the issue in the SQLExpress seems probably to be

    - Duplicate some calculations (i.e. expand every expression with duplicating some mathematic operations with some performance loss)

    - Utilize computed columns if possible (no idea on performance impact here)

    Am I right or is there any other way how to reference a calculated field?

    Any suggestion is greatly welcomed! Thanks in advance.

  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    The problem is not really with the calculated field. The problem is that column aliases are not assigned until the SELECT statement's results are processed, so you cannot refer to them within the statement.
    Two workarounds are to repeat the formula within the result set each time it is needed, or to calculate expression 1 in a SELECT subquery and then refer to it in expression 2 in the outer query.
    Of the two, I prefer the first option most of the time.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

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