Re: Query problem

It doesn't work!
Why?

Alexander Medvedev <a.medvedev_at_globalone.ru> wrote in message news:3A2F93D4.65AE98AA_at_globalone.ru...
>
>
> Alexander Medvedev wrote:
>
> > JAMES wrote:
> >
> > > I have 3 tables, I want to show current stock quantity for each item.
> > >
> > > Table item
> > > <item_id> <item_name> <cost>
> > > <selling_price>
> > > P001 A4 Paper 10
 12
> > > P002 Pen 2
> > > 3
> > > P003 Eraser 1
 2
> > > P004 Book 20
 25
> > >
> > > Table stock_out
> > > <item_id> <qty> <date>
> > > P001 4 11-NOV-00
> > > P002 2 02-NOV-00
> > >
> > > Table stock_in
> > > <item_id> <qty> <date>
> > > P001 10 01-OCT-00
> > > P002 10 01-AUG-00
> > > P003 10 01-JUL-00
> > > P004 10 01-AUG-00
> > >
> > > How can I show the following result?
> > > <item_id> <qty>
> > > P001 6
> > > P002 8
> > > P003 10
> > > P004 10
> > >
> > > THANKS
> > > James
> >
> > Hi James, try this
> >
> > select item_id, si.qty-nvl(so.qty,0)
> > from stock_in si, stock_out so
> > where si.item_id=(+)so.item_id
> >
> > Regards Alexander Medvedev
>
> A little addition. This select will work propertly if you have unique
> representativeness of each item_id in stock_in and stock_out tables (as
 explained
> in your example). Really (in common case) this condition may be violated.
 In this
> case you can use another query:
>
> select item_id,sum(qty) from
> (select item_id, sum(qty) qty from stock_in group by item_id
> union all
> select item_id,sum(-qty) qty from stock_out group by item_id)
> group by item_id
>
> Regards Alexander Medvedev
>
>
Received on Thu Dec 07 2000 - 15:00:45 CET