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.
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:
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
.as
after the expression. This is the SQL standard, but there is nothing per se wrong with using =
, which is SQL Server specific syntax.