How to reuse column alias in another column within same select statement?

Question

select pd.id
       ,[value 1] = Case
                    ----
                    End
       ,[value 2] = Case when [value 1] in ('test1', 'test2') Then 0
                    else [value 1] End

I am getting as invalid column when I try to use value 1 in value 2. Is there a possibility to use calculated [value 1] to be used in [value 2]

My current solution is to create a separate sp or view for value 1 but that's not what I want to do.

The answer is no, not the way you're trying to do it. Either repeat the case expression, or have a derived table or cte returning value1. — jarlh, Apr 07 '21 at 14:29
As said, you cannot reference a computed column from another computed column - you can use a `view` instead. — Stu, Apr 07 '21 at 14:34

Kazi Mohammad Ali Nur Romel · Answer 1 · 2021-12-20T16:58:39.980

You can select [value 1] with a select statement then write an outer query to select [value 1] and [value 2]

SELECT  [value 1], [value 2] = Case when [value 1] in ('test1', 'test2') Then 0
                                    else [value 1] End 
FROM
(
select pd.id
       ,[value 1] = Case
                    ----
                    End
FROM TABLENAME
)T

Or you can repeat the case statement for [value 1] within case statement of [value 2]

select pd.id
       ,[value 1] = Case
                    ----
                    End
       ,[value 2] = Case when ( Case
                    ----
                    End [value 1]) in ('test1', 'test2') Then 0
                    else ( Case
                    ----
                    End [value 1]
                              ) End

score 2 · Accepted Answer · answered Apr 07 '21 at 14:37

In SQL Server, you can use APPLY to define column aliases in the FROM clause -- a good place, in my opinion:

select pd.id v.value_1,
       (Case when value_1 in ('test1', 'test2') Then '0'
             else value_1
        End) as value_2
from pd cross apply
     (values ( case . . . )
     ) v(value_1);

Notes:

Don't use identifier names that need to be escaped, hence value_1 instead of value 1.
value_1 appears to be string. The case expression for value_2 should return a string, so '0' instead of 0.
I define table aliases using as after the expression. This is the SQL standard, but there is nothing per se wrong with using =, which is SQL Server specific syntax.

How to reuse column alias in another column within same select statement?

2 Answers2