query with case column

Question

In a query, I have a column like this:

case when X = 1 then
  case when Y <> 0 then YY else XX end
    else ZZ
end as MyColumn

Is there a way, in another column, to check the above column value referencing MyColumn instead of rewriting the case statement?

I should value another column based on the MyColumn value.

Thanks

Answer 1

No, not without either:

• duplicating the logic in subsequent columns

• doing the initial CASE evaluation in a derived table/inline view:

  SELECT aa.mycolumn,
         CASE 
           WHEN aa.mycolumn = ? THEN ....
         END AS next_mycolumn_evaluation
    FROM (SELECT CASE
                   WHEN X = 1 THEN
                    CASE WHEN Y <> 0 THEN YY 
                      ELSE XX END
                   ELSE ZZ 
                 END AS mycolumn
            FROM ...) aa
  

Answer 2

If I understand you correctly, (and I may not), (ssuming X, Y, XX, YY, and ZZ are other columns in this table), you could create a calculated column on this table, that ues this expression as it's formula ..

Alter Table TableName Add NewColumn 
   as Case when X = 1 then
       Case when Y <> 0 YY else XX end
       else ZZ end 

Answer 3

You could probably do it with subqueries. For example:

Select someTable.ID, someTable.A, someTable.B,
    case subT.myColumn = 0 then 'BLARG' else 'huh?' end As outerMyColumn
from someTable,
    (Select ID, case when X = 1 then
      case when Y <> 0 YY else XX end
        else ZZ
    end as MyColumn
    From someTable
    where someCondition) subT
where subT.ID = someTable.ID;

Answer 4

You can Write like that case when X = 1 and Y <> 0 then YY When X = 1 and Y = 0 then XX else ZZ end end as MyColumn

there is only one case in this.