Use value of a column for another column (SQL Server)?

Question

lets say I have a huge select on a certain table. One value for a column is calculated with complex logc and its called ColumnA. Now, for another column, I need the value from ColumnA and add some other static value to it.

Sample SQL:

select table.id, table.number, complex stuff [ColumnA], [ColumnA] + 10 .. from table ...

The [ColumnA] + 10 is what im looking for. The complex stuff is a huge case/when block.

Ideas?

Leia

Answer 1

If you want to reference a value that's computed in the SELECT clause, you need to move the existing query into a sub-SELECT:

SELECT
    /* Other columns */,
    ColumnA,
    ColumnA + 10 as ColumnB
FROM
(select table.id, table.number, complex stuff [ColumnA].. from table ...
) t

You have to introduce an alias for this table (in the above, t, after the closing bracket) even if you're not going to use it.

(Equivalently - assuming you're using SQL Server 2005 or later - you can move your existing query into a CTE):

;WITH PartialResults as (
     select table.id, table.number, complex stuff [ColumnA].. from table ...
)
SELECT /* other columns */, ColumnA, ColumnA+10 as ColumnB from PartialResults

CTEs tend to look cleaner if you've got multiple levels of partial computations being done, I.e. if you've now got a calculation that depends on ColumnB to include in your query.

Parliament

Answer 2

Unfortunately, in SQL Server 2016:

SELECT 3 AS a, 6/a AS b;

Error: Invalid column name: 'a'.

Immolate

You can use a cte for this: WITH cte AS (SELECT 3 AS a) SELECT a, 6/a AS b FROM cte; – Immolate

Answer 3

You could solve this with a subquery and column aliases.

Here's an example:

SELECT MaxId + 10
FROM (SELECT Max(t.Id) As MaxId
      FROM SomeTable t) As SomeTableMaxId

Intrigue

Answer 4

You could:

1. Do the + 10 in the client code
2. Write a scalar-valued function to encapsulate the logic for complex stuff. It will be optimized into a single call.
3. Copy complex stuff logic for the other column. It should get optimized out into 1 call.
4. Use a sub-select to apply the additional calculation

Doer

Answer 5

One convenient option to reuse scalar expressions in a query is to use APPLY (or LATERAL in standard SQL):

SELECT
  table.id,
  table.number,
  [ColumnA],
  [ColumnA] + 10
FROM 
  table
  CROSS APPLY (SELECT complex stuff [ColumnA]) t

Excitement