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
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With