Re: tricky sql

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 Tue Apr 07 1998 - 00:00:00 CDT