Re: tricky sql

Hi.
 I don't think it is possible to solve your problem by single SQL  statement ( either outer join or view ), because you have a  cartesian product when joining PRCMAS and INLMAS tables.  I think you will have to make a join of POLMAS and PRCMAS, keep a result  in some temporary area and "append" to it the join of POLMAS-INLMAS  tables.
 Try to use PL/SQL tables for an intermidiate results.

In article <01bd5e44$605656a0$8a01020a_at_bleckled.noralnm.com>,   "David Bleckler" <bleckled_at_attmail.noralnm.com> 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.
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Sat Apr 04 1998 - 00:00:00 CST