How to use conditional columns values in the same select statement?

Question

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

Martin Smith · Accepted Answer · 2011-04-26T13:07:13.753

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.

CTE did the job, i didn't look to CROSS APPLY, I'll bookmark it for the future. Thanks. — UnDiUdin, Apr 26 '11 at 13:28

score 3 · Answer 2 · answered Apr 26 '11 at 13:38

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

Lukas Eder · Answer 3 · 2022-11-21T16:06:06.627

Personally, I find using CTE or derived tables a bit confusing for this purpose, as you have to nest things one level and think about the nesting impliciations. A much simpler approach (at least in my opinion) is to use APPLY (or standard SQL LATERAL in other RDBMS) to generate column expression aliases:

SELECT
  ID_Operation,
  FirstCheck,
  SecondCheck,
  ThirdCheck,
  AllChecksOk = CASE
    WHEN FirstCheck + SecondCheck + ThirdCheck = 3 THEN 'OK' ELSE 'NO'
  END
FROM
  AllOperationsTable
  CROSS APPLY (
    SELECT
      FirstCheck = CASE WHEN COMPLEX_EXPRESSION_1 = 0 THEN 0 ELSE 1 END,
      SecondCheck = CASE WHEN COMPLEX_EXPRESSION_1 = 0 THEN 0 ELSE 1 END,
      ThirdCheck = CASE WHEN COMPLEX_EXPRESSION_1 = 0 THEN 0 ELSE 1 END
  ) t

How to use conditional columns values in the same select statement?

3 Answers3

Linked