Re: How can I do this problem

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 15 Dec 1999 07:33:35 -0500
Message-ID: <bg2f5skp5sk7n5qoims57jsbaagvgnk2ms@4ax.com>

A copy of this was sent to samc <samc_at_ctxopto.com.tw> (if that email address didn't require changing) On 15 Dec 1999 07:39:49 GMT, you wrote:

>I have two table
>
>a_table :
>
>PN SUB org OHQTY
>--------- ------------ ---------- ---------
>55 M1 106 60
>55 M1 106 70
>55 M1 106 30
>55 M1 108 35
>56 M1 106 40
>56 M2 106 30
>57 M1 106 20
>
>b_table is:
>
>PN SUB org TRQTY
>--------- ------------ ---------- ---------
>55 M1 106 20
>55 M1 106 20
>55 M1 106 100
>55 M1 106 -10
>55 M1 108 35
>56 M2 106 40
>56 M2 106 50
>56 M2 106 -50
>57 M3 106 20
>
>
>I want to find a_table.sum(OHQTY) != b_table.sum(TRQTY)
>

select a_pn, a_sub, a_org, ohqty, trqty   from ( select pn a_pn, sub a_sub, org a_org, sum(ohqty) ohqty

           from a_table
          group by pn, sub, org ),
       ( select pn b_pn, sub b_sub, org b_org, sum(trqty) trqty 
           from b_table
          group by pn, sub, org )

 where a_pn = b_pn and a_sub = b_sub and a_org = b_ord    and ohqty <> trqty
/

>This is my SQL statement .
>
> select a.pn, a.sub,a.org,
> sum(nvl(a.ohqty,0)),sum(nvl(b.trqty,0))
> from a_table a,
> b_table b
> where a.pn = b.pn
> and a.sub = b.sub
> and a.org = b.org
> group by a.pn, a.sub,a.org
> having sum(nvl(ohqty,0)) != sum(nvl(trqty,0))
> order by pn,sub
>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Dec 15 1999 - 06:33:35 CST