CODEWITHSUNDEEP
sqlsql-serversql-server-2008alias
Ruslan
Ruslan

Reputation: 10147

Re-use aliased field in SQL SELECT statement

I'd like to achieve something like this:

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

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

Note, that this will return multiple rows, hence the DECLARE/SET outside the SELECT statement is no good in my case.

Upvotes: 13

Views: 7017

Answers (2)

Peter Lang
Peter Lang

Reputation: 55524

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

Upvotes: 16

Larry Lustig
Larry Lustig

Reputation: 50970

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.

Upvotes: 3

Related Questions