Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL question
I'd recommend that you use "decode" which is much like a case-statement. The
syntax is:
decode(field, case1, result1, case2, result2,...,default)
In pseudo-code, that'd be:
switch (field1)
case1 : result1 case2 : result2 casex : resultx
The following query ought to do the job:
Select sum(decode(type, 'S', amount, 'R', (amount*(-1)), 0) TotalO from order;
Hope this helps
Ivan Bajon
Ellegaard Data ApS
Henrik Staun Poulsen wrote in message <35F67CCD.5104D35F_at_infolog.dk>...
>Dear All,
>
>I want to select the sum of all orders, but I want to subtract the all
>the sale reversals, i.e. all orders that where canceled out.
>Do I need to create a union like
>
>create view test as
> select sum(amount) 'TotalO' from order where Type = 'S'
> UNION
> select (sum(amount) * (-1)) 'TotalO' from order where Type = 'R'
>
>select TotalO from test
>
>OR is there a way of writing the select statement with an IF statement
>or a CASE statement?
>
>TIA
>
>Henrik Staun Poulsen
>InfoLog
>
>
>
>
>
Received on Wed Sep 09 1998 - 08:51:41 CDT