CODEWITHSUNDEEP
sqlsql-servert-sqlcase
opaera
opaera

Reputation: 41

query with case column

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

Upvotes: 4

Views: 614

Answers (4)

KuldipMCA
KuldipMCA

Reputation: 3149

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.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332531

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

Upvotes: 3

FrustratedWithFormsDesigner
FrustratedWithFormsDesigner

Reputation: 27486

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;

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146429

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

Upvotes: 0

Related Questions