DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Reoprts!!

  1. #1
    Join Date
    Jun 2001
    Posts
    109
    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...

  2. #2
    Join Date
    Jun 2001
    Posts
    109
    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

  3. #3
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    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.

    David Knight
    OCP DBA 8i, 9i, 10g

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width