Reoprts!!

Hi all ,
I need a report which displays the subtracted amount from two column from 2 different tables whcih are not related.

For example:
table A and B.
Table A has amt_paid column and table B has total_amt column....amt_paid should be substracted from total amount and displayed in the reports. These 2 tables (A&B) has REv_Acct_code & date as common columns. Teh user enteres the date for that entered date it should subtract the (total_amount)-(amount_paid) and display along with rev_acct_code.

Rev_acct_code total_amt-amt_paid

10001 40 (which is total_amt(80)-amoutn_pd(40))

so on...

select
v.TC_LINE_REV_ACCT_NO ,
v.totamtpd,
p.pw_amount,
v.totamtpd - p.pw_amount amount
from vw_payin_line v, payin_withdrawal p
where v.tc_tran_date='&pdate'
and p.pw_payin_date='&pdate'
and v.tc_tran_date=p.pw_payin_date
/

this is what I worte.....vw_payin_line table & payin_withdrawal table are not related....but they do have a common field between them rev_acct_code & dates are common.....so the above query should give me 13 records....becoz there are 13 records....but this query gives me 39 records.....i.e everything gets repeated...

TC_LINE_REV_ACCT_NO TOTAMTPD PW_AMOUNT AMOUNT
---------------------------------------- ---------- ---------- ----------
10000-2430-22111 243.71 0 243.71
10000-2430-27112 35 0 35
10000-2430-27131 4450.3 0 4450.3
10000-2430-27131-44 45.5 0 45.5
10000-2430-27132 417.9 0 417.9
10000-2430-27133 105.7 0 105.7
10000-2430-27134 2711.54 0 2711.54
10000-2430-27135 1729.81 0 1729.81
10000-2430-27136 1880.61 0 1880.61
10000-2430-27212 50 0 50
10000-2430-27214 250 0 250

TC_LINE_REV_ACCT_NO TOTAMTPD PW_AMOUNT AMOUNT
---------------------------------------- ---------- ---------- ----------
53000-3420-24806 37.9 0 37.9
72013-2430-5230 236.15 0 236.15
10000-2430-22111 243.71 0 243.71
10000-2430-27112 35 0 35
10000-2430-27131 4450.3 0 4450.3
10000-2430-27131-44 45.5 0 45.5
10000-2430-27132 417.9 0 417.9
10000-2430-27133 105.7 0 105.7
10000-2430-27134 2711.54 0 2711.54
10000-2430-27135 1729.81 0 1729.81
10000-2430-27136 1880.61 0 1880.61

TC_LINE_REV_ACCT_NO TOTAMTPD PW_AMOUNT AMOUNT
---------------------------------------- ---------- ---------- ----------
10000-2430-27212 50 0 50
10000-2430-27214 250 0 250
53000-3420-24806 37.9 0 37.9
72013-2430-5230 236.15 0 236.15



so on.....becoz there are 3 records in payin table with that criteria...& 13 records in vw_payin with that criteria...its mutiplying & diaplying whcih is wrong....13*3=39 records....
Is this becoz the tables r not related?

Let me know..pleeeeeeeeeease!!
Thanks
saritha

If the tables are not huge and the column names are not too long, could you put the table structure on the thread?

If the tables have many columns could you just include the relevant columns? If the column names are too long or not descriptive, could you make changes so that they are?

Without much thinking, I believe you can solve this with a temporary table, but that might be an expensive option.