Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: subtracting variables in oracle

Re: subtracting variables in oracle

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Tue, 23 Jan 2001 20:05:13 +0800
Message-ID: <3A6D7379.3FEF@yahoo.com>

Connor McDonald wrote:
>
> pankrateon_at_my-deja.com wrote:
> >
> > the stage: Oracle 8i 8.1.6 a table that contains employee info of which there
> > is one column labeled "pay type" The "pay type" can be "Gross" or "Bonus". An
> > other field is "Amount"
> >
> > table looks like this:
> >
> > lastname | firstname | title| department | pay_type | amount
> >
> > I want to be able to group by the persons name (last and first) and then
> > subract any "Bonus" amount from their "Gross" amount
> >
> > I would want to create a view that looks as follows:
> >
> > lastname | firstname | title| department | gross - amount
> >
> > how do I do that?
> > is it possible have this displayed as a view?
> >
> > Sent via Deja.com
> > http://www.deja.com/
>
> self-join should work
>
> select a.key_fields, a.amount - b.amount
> from blah a, blah b
> where a.key_fields = b.key_fields
>
> HTH
> --
> ===========================================
> Connor McDonald
> http://www.oracledba.co.uk (mirrored at
> http://www.oradba.freeserve.co.uk)
>
> "Some days you're the pigeon, some days you're the statue"

Whoops... forgot to add something

select a.key_fields, a.amount - b.amount from blah a, blah b
where a.key_fields = b.key_fields
and a.pay_type = 'GROSS'
and b.bay_type = 'NET'

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"
Received on Tue Jan 23 2001 - 06:05:13 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US