Re: tricky sql

Hi, Michael

It isn't so simple! We need order (from 1 to ...) in each group (by POHNUM)! If we can use the views, we must write:

create view v1_PRCMAS
as select POHNUM nn, QTYREC cc, rownum rr from PRCMAS where POHNUM >= 0 and QTYREC >= 0;

create view v2_PRCMAS
as select nn, min(rr) rr from v1_PRCMAS group by nn;

create view v3_PRCMAS
as select a1.nn , cc , a1.rr-a2.rr rr from v1_PRCMAS a1,v2_PRCMAS a2
where a1.nn=a2.nn;

create view v1_INLMAS
as select POHNUM nn, QTYINV vv, rownum rr from INLMAS where POHNUM >= 0 and QTYINV >= 0;

create view v2_INLMAS
as select nn, min(rr) rr from v1_INLMAS group by nn;

create view v3_INLMAS
as select a1.nn , vv , a1.rr-a2.rr rr from v1_INLMAS a1, v2_INLMAS a2
where a1.nn=a2.nn;

select distinct nn, cc, vv from
(
select t1.nn nn, cc, vv from
v3_PRCMAS t1, v3_INLMAS t2
where t1.nn=t2.nn(+) and
 t1.rr=t2.rr(+)
union
select t2.nn nn, cc, vv from
v3_PRCMAS t1, v3_INLMAS t2
where t1.nn(+)=t2.nn and
 t1.rr(+)=t2.rr
)
/

          Elena Nossova

bialik_at_wis.weizmann.ac.il wrote:

> 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.
Received on Mon Apr 20 1998 - 04:31:02 CDT