Re: tricky sql

Hi, David.

I think that simple solution is possible:

1. Create 2 views : CREATE VIEW V_PRCMAS AS SELECT POHNUM,RELNUM,POLLIN,ROWNUM RR,QTYREC FROM PRCMAS; CREATE VIEW V_INLMAS AS SELECT POHNUM,RELNUM,POLLIN,ROWNUM RR,QTYINV FROM INLMAS;
2. Use following select : SELECT A.POHNUM, A.RELNUM, A.POLLIN, A.RR, A.QTYREC, B.QTYINV FROM V_PRCMAS A, V_INLMAS B WHERE A.POHNUM = B.POHNUM(+) AND A.RELNUM = B.RELNUM(+) AND A.POLLIN = B.POLLIN(+) AND A.RR = B.RR(+) UNION SELECT A.POHNUM, A.RELNUM, A.POLLIN, A.RR, A.QTYREC, B.QTYINV FROM V_PRCMAS A, V_INLMAS B WHERE A.POHNUM(+) = B.POHNUM AND A.RELNUM(+) = B.RELNUM AND A.POLLIN(+) = B.POLLIN AND A.RR(+) = B.RR

  Michael Bialik.
In article <35334AA1.36C08DAB_at_quantum.de>,   Elena Nossova <nossova_at_quantum.de> wrote:
>
> 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.
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Sun Apr 19 1998 - 16:43:21 CDT