Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Query 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:11:43 CDT