SQL Question

Hello,

Is this possible? I want to sub-total by the key field and caps_code field in the Audit_Trail BEFORE adding to the amt_check field in my Patmeny_History table. Look at what happens:

TABLE A (Payment History):

KEY              CAPS_CODE      AMT
2998425              F4              2688.00

TABLE B (Audit Trail):
KEY              CAPS_CODE      AMT
2998425              F4             - 2688.00
2998425              F4               2688.00
2998425              F4             - 2688.00
2998425              F5               2688.00

Here is the query:

select ph.payment_history_key, amt_check + at.sumcol  from payment_history ph,
   (select at.payment_history_key, at.caps_code, sum(adj_amt_check) sumcol

    from audit_trail at
    group by payment_history_key, caps_code) at  where
 ph.payment_history_key = at.payment_history_key and  ph.caps_code = at.caps_code;

Here is the result:

PAYMENT_HISTORY_KEY AMT_CHECK+AT.SUMCOL
------------------- -------------------

            2832930                  68
            2907008                   0
            2907443                 120
            2907451                   0
            2950065                   0
            2998425                   0
            3027797                   0
            3075475                   0

It is not breaking on the caps code. I should have two enteries for key 2998425 since the caps code is different.

The output for 2998425 should look like this:

PAYMENT_HISTORY_KEY AMT_CHECK+AT.SUMCOL
------------------- -------------------

            2998425                   0
            2998425                 2668

Since the CAPS_CODE is different.

I also tried an outer join, but that did not work either.

Do you have any other suggestions?

Arthur
amerar_at_ci.chi.il.us

Thanks, and please send a copy to e-mail.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 27 1999 - 15:12:49 CDT