Re: tricky sql

Hi, David

I think it is possible to solve your problem by single SQL statement. Try the following:

create index i_PRCMAS on PRCMAS ( POHNUM, QTYREC ); create index i_INLMAS on INLMAS ( POHNUM, QTYINV );

select distinct nn, cc, vv from
(
select t1.nn nn, cc, vv from
(
select a1.nn , cc , a1.rr-a2.rr rr from ( select POHNUM nn, QTYREC cc, rownum rr from PRCMAS  where POHNUM >= 0 and QTYREC >= 0 ) a1, ( select nn, min(rr) rr from
 ( select POHNUM nn, QTYREC cc, rownum rr from PRCMAS   where POHNUM >= 0 and QTYREC >= 0 ) a1   group by nn ) a2
where a1.nn=a2.nn
) t1,
(
select a1.nn , vv , a1.rr-a2.rr rr from ( select POHNUM nn,QTYINV vv, rownum rr from INLMAS   where POHNUM >= 0 and QTYINV >= 0 ) a1, ( select nn, min(rr) rr from
 ( select POHNUM nn,QTYINV vv, rownum rr from INLMAS   where POHNUM >= 0 and QTYINV >= 0 ) a1   group by nn ) a2
where a1.nn=a2.nn
) t2
where t1.nn=t2.nn(+) and
 t1.rr=t2.rr(+)
union
select t2.nn nn, cc, vv from
(
select a1.nn , cc , a1.rr-a2.rr rr from ( select POHNUM nn, QTYREC cc, rownum rr from PRCMAS   where POHNUM >= 0 and QTYREC >= 0 ) a1, ( select nn, min(rr) rr from
 ( select POHNUM nn, QTYREC cc, rownum rr from PRCMAS   where POHNUM >= 0 and QTYREC >= 0 ) a1   group by nn ) a2
where a1.nn=a2.nn
) t1,
(
select a1.nn , vv , a1.rr-a2.rr rr from ( select POHNUM nn,QTYINV vv, rownum rr from INLMAS   where POHNUM >= 0 and QTYINV >= 0 ) a1, ( select nn, min(rr) rr from
 ( select POHNUM nn,QTYINV vv, rownum rr from INLMAS   where POHNUM >= 0 and QTYINV >= 0 ) a1   group by nn ) a2
where a1.nn=a2.nn
) t2
where t1.nn(+)=t2.nn and
 t1.rr(+)=t2.rr
)
/

    Elena

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 14 1998 - 06:38:09 CDT