Use a Case to return 2 field values 1

[scc]

Okay, I'm drawing a blank on this one.

I need to check several fields in the database and based on the criteria, return 2 fields in my select statement.

For instance, I have this CASE statement (this is just a small sampling of it):
CASE --Determine the Category
WHEN Disposition = 'GROUP' and Error_Code is null
THEN 'Group'

WHEN Disposition ='UNREPRICED' and Error_Code is null
THEN 'Provider'

WHEN Disposition = 'AUDITOR' and Error_Code is null
THEN 'Auditor'

ELSE 'Ready'
END AS Category

But I would also like to return a field called 'Stage' as well. In other words, all records with a Category of 'Group' would have a 'Stage' = 'Processed', all records with a Category of 'Provider' would have a 'Stage'= 'Complete' and all records with a Category of 'Auditor' or 'Ready' need to return a 'Stage' = 'Outbound'

What is the best way to do this?

 

[cheyney]

Just have two case statements one after the other


CASE --Determine the Category
WHEN Disposition = 'GROUP' and Error_Code is null
THEN 'Group'

WHEN Disposition ='UNREPRICED' and Error_Code is null
THEN 'Provider'

WHEN Disposition = 'AUDITOR' and Error_Code is null
THEN 'Auditor'

ELSE 'Ready'
END AS Category,

CASE --Determine the Category
WHEN Disposition = 'GROUP' and Error_Code is null
THEN 'Processed'

WHEN Disposition ='UNREPRICED' and Error_Code is null
THEN 'Complete'


ELSE 'Outbound'
END AS Stage


Cheyney

 

[scc]

Thanks for responding.

I thought of that, but the Case statement is sooo long and detailed, I was wondering if there was a better way so all this code didn't have to be repeated. I know cut and paste is easy, but it makes the maintenance much more difficult if someone else has to inherit and support the code.

 

[swampBoogie]

You can embed the case [bold]expression[/bold] in a derived table, and thus have it only once.

select c1, c2, case c2 when 'Group' then 'Processed'
                       when ... 
                       when ... end 
from ( select c1 , CASE --Determine the Category
        WHEN Disposition = 'GROUP' and Error_Code is null
        THEN 'Group'
        WHEN Disposition ='UNREPRICED' and Error_Code is null
        THEN 'Provider'
        WHEN Disposition = 'AUDITOR' and Error_Code is null
        THEN 'Auditor'
        ELSE 'Ready'
    END AS C2 from t 
   where ...
) as dt

 

[scc]

swampBoogie,

Ok, think I'm following you.

I have never used a derived table. Is "as dt" the derived table name, and is this like a temp table? Do I need to destroy it or anything?

And do I need to reference dt in anyway? If not, then what is the purpose of the "as dt" statement.

Thanks for your help. Again, this is something completely new to me.

 

[swampBoogie]

Yes dt is the (arbitrary) name of the derived table. It must be named even though you don't use the name anywhere. There is no need to destroy anything, that is handled by the server (when necessary).

 

[scc]

Thanks! This will be much easier to maintain.