Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: subtracting variables in oracle
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