Xref: alice comp.databases.oracle.misc:19692
Path: alice!news-feed.fnsi.net!news.idt.net!newshub.northeast.verio.net!news-feed.inet.tele.dk!bofh.vszbr.cz!news1.tele.dk!not-for-mail
From: "Ivan Bajon" <ib@ed.dk>
Newsgroups: comp.databases.oracle.misc
Subject: Re: SQL question
Date: Wed, 9 Sep 1998 15:51:41 +0200
Organization: Customer at Tele Danmark Erhverv
Lines: 52
Message-ID: <6t60te$fje$1@news1.tele.dk>
References: <35F67CCD.5104D35F@infolog.dk>
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3

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
    default   :    default
end;

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@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
>
>
>
>
>