Re-use aliased field in SQL SELECT STATEMENT

Question

Hi, the question is hard to define without giving an example, e.g. I'd like to achieve something like this :

SELECT 
 (CASE WHEN ...) AS FieldA,
 20 + FieldA AS FieldB
FROM Tbl

Assuming that by "..." I've replaced a long and complex CASE statement, hence I don't want to repeat it when selecting FieldB and use the aliased FieldA instead.

IS THIS EVEN POSSIBLE? What could be a workaround? Note, that this will return multiple rows , hence the DECLARE/SET outside the SELECT statement is no good in my case.

PLEASE HELP.

P.S. I am using SQL 2008

Answer 1

A workaroud would be to use a sub-query:

SELECT
  FieldA,
  FieldA + 20 AS FieldB
FROM (
  SELECT 
    (CASE WHEN ...) AS FieldA
  FROM Tbl
) t

---

To improve readability you could also use a CTE:

WITH t AS (
  SELECT 
    (CASE WHEN ...) AS FieldA
  FROM Tbl
)
SELECT
  FieldA,
  FieldA + 20 AS FieldB
FROM
  t

Answer 2

When I have complicated logic to compute a "virtual" column value from other column values in a table I generally create a single-table view of the original table with all the original columns plus the computed values as well. Then I do other SELECTs against the view. That allows me:

1. To name my computed columns.

2. To keep the logic for the computations in one place instead of scattered through various queries in the application.