Re: Again:How to create the SELECT script?

From: <mujeeb_ur_rehman_at_hotmail.com>
Date: Thu, 13 Aug 1998 14:50:52 GMT
Message-ID: <6quugd$tcu$1@nnrp1.dejanews.com>

Hi :)

Here is the script that will produce the desired result u looking for.

Hope this help.

select x.acc_no,x.acc_nm,

decode(sum(y.amt),null,'No Record Found',sum(y.amt)) Total from table2 y, table1 x
where y.acc_no(+) = x.acc_no
group by x.acc_no,x.acc_nm
/

The the ouput produced by the above statement when run on the following tables

       Table1                               Table2
     ---------                             --------
    ACC_NO ACC_NM                       ACC_NO        AMT
---------- --------------------     ---------- ----------
        10 Training Cose                    10        100
        20 Traffic cost                     20        200
        30 Medical cost                     30       5000
        40 Sale cost                        10        400
        50 Manage cost                      50       1000
        60 Telecom cost

Statement ouput

ACC_NO ACC_NM TOTAL

---------- -------------------- ----------------------------------------
        10 Training Cose        500
        20 Traffic cost         200
        30 Medical cost         5000
        40 Sale cost            No Record Found
        50 Manage cost          1000
        60 Telecom cost         No Record Found

6 rows selected.

In article <35d22dd0.930112_at_news.twsc.pouchen.com.tw>, violin.hsiao_at_mail.pouchen.com.tw (Violin) wrote:
> Hello,
> I think there is some misunderstanding in my last post.
>
> I wonder how to return the result:
> List all rows of master TABLE1,and count sum(AMT) of TABLE2.
> But if I use the clause:WHERE TABLE1.ACC_NO=TABLE2.ACC_NO,
> then I just could get the rows exist in both TABLE1 & TABLE2.
> So my purpose is not only count sum(AMT) of TABLE2 for each ACC_NO,
> but also list all the ACC_NO in master TABLE1 whether the ACC_NO
> exists in TABLE2.
>
> ACC_NO ACC_NM TOTAL
> ---------------------------------------------------
> 10 Training cost 500
> 20 Traffic cost 200
> 30 Medical cost 5000
> "40 Sale cost " -> the 2 ACC_NO
>
> 50 Manage cost 1000 don't exist in TABLE2,
> "60 Telecom cost " ->but I want to see!
>
> So my tables are:TABLE1 is master,TABLE2 is detail.
> And the data is:
>
> TABLE1:
> ACC_NO ACC_NM
> ---------------------------------
> 10 Training cost
> 20 Traffic cost
> 30 Medical cost
> 40 Sale cost
> 50 Manage cost
> 60 Telecom cost
>
> TABLE2:
> ACC_DATE ACC_NO AMT
> --------------------------------------------
> 960101 10 100
> 960101 20 200
> 960103 30 5000
> 960105 10 400
> 960109 50 1000
>
> I don't know if it possible,cause I try & try & try but still can't
> find the solution.
> Please give me some suggestions for the select statement,
> I'll very appriciate for your help!
> Please Cc to : violin.hsiao_at_mail.pouchen.com.tw,Thank you so much :)
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Aug 13 1998 - 09:50:52 CDT