Re: newbie- decode, case or other SQL in Oracle 9i?

bbcrock_at_gmail.com wrote:

> My customers have a report that uses preliminary and final cost
> amounts. They requeted a change to the data model so they can track
> the historic preliminary amount with the final amount. They want to
> modify the report to read something like:
>
> If finalamount field is 0 then use preliminaryamount field, if
> finalamount field contains a value, us that. Group the sum of both
> amounts by type.
>
> I have an existing report that only references finalamount. This is an
> easy issue, I'm sure, and want to solve it in SQL without running two
> queries.
>
> Can I use decode or case to do this? I can't make it work. How would
> you write something like this? I'm attempting something like:
> select AmountType,
> CASE WHEN (finalamount = 0) THEN preliminaryamount ELSE finalamount
> from BudgetTable

DECODE is a simpler implementation of CASE, and all you really need:

"select DECODE(finalamount,0,preliminaryamount,finalamount) from ...."

//Walt Received on Wed Oct 05 2005 - 14:17:55 CDT