Re: tricky sql

From: Kevin Bass <akil1_at_mindspring.com>
Date: 1998/04/04
Message-ID: <6g6347$uqo$1@camel29.mindspring.com>#1/1

David Bleckler:

I hope that this helps.

select pol.pohnum || ' ' ||

           pol.pollin || '    ' ||
           prc.qtyrec || '    ' ||
           inl.qtyinv
  from prcmas prc,
           polmas pol,
           inlmas  inl

where pol.<primary key> = inl.<primary key>

    and pol.<primary key> = pol.<promary key>;

I think that you are not getting the rows than you want due to not connecting the primary keys within the WHERE clause. If there are primay keys with the POLMAS and INLMAS tables, match them up in your WHERE clause and also match up the primary keys within POLMAS and PRCMAS tables. This will narrow down the amount of data that you are selecting because the primary keys will being unique.

Kevin

bialik_at_wis.weizmann.ac.il wrote in message <6g5t8s$15f$1_at_nnrp2.dejanews.com>...
>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