Re: tricky sql

From: Suresh Easwar <sje_at_sigma-inc.com>
Date: Sat, 11 Apr 1998 02:30:27 -0400
Message-ID: <352F0E02.B4C3B401@sigma-inc.com>

try the following:

select pohnum, pollin, sum(qtyrec), sum(qtyinv) from (

    select pohnum, pollin, qtyrec, 0 qtyinv     from polmas, prcmas
    where polmas.pohnum = prcmas.pohnum

         polmas.relnum = prcmas.relnum
         polmas.pollin = prcmas.pollin

    union
    select pohnum, pollin, 0 qtyrec, qtyinv     from polmas, inlmas
    where polmas.pohnum = inlmas.pohnum

         polmas.relnum = inlmas.relnum
         polmas.pollin = inlmas.pollin

)
group by pohnum, pollin

Suresh

David Bleckler wrote:

> It doesn't matter. And that is part of the problem.
> There are no corresponding rows between PRCMAS and INLMAS. The user wants
> to see individual line quantities for both INLMAS and PRCMAS. Using a join
> creates four rows. This then, throws the totals off.
> Using your data, this would be the desired output:
>
> POHNUM POLLIN QTYREC QTYINV
> 1 1 111 333
> 1 1 222 444
>
> And if we had a third record in INLMAS, the output would be:
>
> POHNUM POLLIN QTYREC QTYINV
> 1 1 111 333
> 1 1 222 444
> 1 1 555
>
> I'm not sure if this is even possible using sql, sqlplus or view creation.
>
> Thanks for your help
>
> Thomas Sommerfeld <Thomas.Sommerfeld_at_Baden-Online.de> wrote in article
> <3525525B.D701577F_at_Baden-Online.de>...
> > Hi David,
> >
> > one question to your problem: If there's no relationship between PRCMAS
> > and INLMAS, how do you determine which row from PRCMAS corresponds to a
> > row in INLMAS (If you have a possibilty to do so, you HAVE a
> > relationship between the two tables)
> >
> > Suggest the following data
> > POLMAS:
> > POHNUM POLLIN
> > 1 1
> >
> > PRCMAS
> > POHNUM POLLIN QTYREC
> > 1 1 111
> > 1 1 222
> >
> > INLMAS
> > POHNUM POLLIN QTYINV
> > 1 1 333
> > 1 1 444
> >
> > The possible joins depending on this data look like this:
> > No POHNUM POLLIN QTYREC QTYINV
> > 1 1 1 111 333
> > 2 1 1 111 444
> > 3 1 1 222 333
> > 4 1 1 222 444
> >
> >
> > Which combination do you want ???
> >
> > Bye
> > Thomas
> >
> >
> > David Bleckler wrote:
> >
> > > I have 3 tables, POLMAS (purchase order line master), PRCMAS (po
> > > receipts),
> > > and INLMAS (po invoice lines).
> > >
> > > All 3 tables have the fields POHNUM (po header #), RELNUM (release #)
> > > and
> > > POLLIN (line #).
> > >
> > > There is a one-to-many relationship between POLMAS and PRCMAS and
> > > between
> > > POLMAS and INLMAS.
> > > There is NO relationship between PRCMAS and INLMAS.
> > >
> > > I would like a query/report that returns the following:
> > >
> > > POHNUM, POLLIN, QTYREC, QTYINV
> > >
> > > where QTYREC is quantity received (from PRCMAS) and QTYINV is quantity
> > >
> > > invoiced (from INLMAS).
> > >
> > > My problem is that if I have an order line with two receipts and two
> > > invoices, it returns four rows. I would like it to only return two.
> > > Likewise, if I have a po line with three invoices and two receipts, it
> > >
> > > should return three rows with the QTYREC column empty.
> > >
> > > I believe I need to use (two) outer joins but I have been unsuccessful
> > > so
> > > far.
> > > Creating views is an option also available.
> > >
> > > Any help/suggestions would be appreciated.
> >
> >
> >
Received on Sat Apr 11 1998 - 01:30:27 CDT