How to use conditional columns values in the same select statement?
Solution 1
You can't reference a column alias in the select
but you can use a CTE as below.
;WITH CTE AS
(
select
ID_Operation,
FirstCheck = CASE WHEN (COMPLEX_EXPRESSION_1)= 0 then 0 else 1 end,
SecondCheck = CASE WHEN (COMPLEX_EXPRESSION_2)= 0 then 0 else 1 end,
ThirdCheck = CASE WHEN (COMPLEX_EXPRESSION_3)= 0 then 0 else 1 end
from
AllOperationsTable
)
SELECT *,
AllChecksOk = Case WHEN
(COMPLEX_EXPRESSION_1+ COMPLEX_EXPRESSION_2+
COMPLEX_EXPRESSION_3CHeck = 3) Then 'OK' Else 'No' End
FROM CTE
You can also use CROSS APPLY
to define the 3 column aliases then reference them in the main SELECT
list as in this example.
Solution 2
Below is a derived table solution
SELECT
T.ID_Operation,
FirstCheck = CASE WHEN T.Expr1 = 0 THEN 0 ELSE 1 END,
SecondCheck = CASE WHEN T.Expr2 = 0 THEN 0 ELSE 1 END,
ThirdCheck = CASE WHEN T.Expr3 = 0 THEN 0 ELSE 1 END,
AllChecksOk = CASE WHEN T.Expr1 + T.Expr2 + T.Expr3 = 3 THEN 'OK' ELSE 'No' END
FROM
(
SELECT
ID_Operation,
Expr1 = (COMPLEX_EXPRESSION_1),
Expr2 = (COMPLEX_EXPRESSION_2),
Expr3 = (COMPLEX_EXPRESSION_3)
FROM
AllOperationsTable
) T
![UnDiUdin](https://i.stack.imgur.com/aDIR6.png?s=256&g=1)
UnDiUdin
I am a Delphi Developer using SQL Server mostly as backend database. I have some web development and Unity/webgl skills. I currenly mostly Instant Developer Foundation by Progamma srl, check it out: https://www.instantdeveloper.com/en/products/instant-developer-foundation/ Any fool can write code that a computer can understand. Good programmers write code that humans can understand. — M. Fowler The reason why I choose "UnDiUdin" as username is that where I live (Friuli, Italy) when there is a need to search for an expert we mention "a person from Udine", "un di Udin" in the frioulan language. So even if i am not from Udine I give myself the name of an expert...
Updated on June 13, 2022Comments
-
UnDiUdin about 2 years
I have something like
(
COMPLEX_EXPRESSION_N
stands for a long subquery)select ID_Operation, FirstCheck = CASE WHEN (COMPLEX_EXPRESSION_1)= 0 then 0 else 1 end, SecondCheck = CASE WHEN (COMPLEX_EXPRESSION_2)= 0 then 0 else 1 end, ThirdCheck = CASE WHEN (COMPLEX_EXPRESSION_3)= 0 then 0 else 1 end, AllChecksOk = Case WHEN (FirstCheck + SecondCheck + Third CHeck = 3) Then 'OK' Else 'No' End from AllOperationsTable
Is it possible to use FirstCheck, SecondCheck, ThirdCheck as I did in the AllChecksOk line?
I am not concerned about performance, this is something that is manually run once a day on a very small number of records, I just want to avoid to create views, tables or temporary tables and keep all in a single select statement.
As an altenrative I can do this, but it makes the query less readable (as I need to write twice every complex expression):
select ID_Operation, FirstCheck = CASE WHEN (COMPLEX_EXPRESSION_1)= 0 then 0 else 1 end, SecondCheck = CASE WHEN (COMPLEX_EXPRESSION_2)= 0 then 0 else 1 end, ThirdCheck = CASE WHEN (COMPLEX_EXPRESSION_3)= 0 then 0 else 1 end, AllChecksOk = Case WHEN (COMPLEX_EXPRESSION_1+ COMPLEX_EXPRESSION_2+ COMPLEX_EXPRESSION_3CHeck = 3) Then 'OK' Else 'No' End from AllOperationsTable
-
UnDiUdin about 13 yearsCTE did the job, i didn't look to CROSS APPLY, I'll bookmark it for the future. Thanks.