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.
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...
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?
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.
Reply With Quote
Bookmarks