Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query Question

Re: Query Question

From: Jeff Guttadauro <jeff109_at_NOSPAM.netscape.net>
Date: Thu, 28 Oct 1999 13:29:47 GMT
Message-ID: <38184c33.932530@news>


Hi, Arthur.

        Is it possible that no Payment History record with a Caps_Code of F5 for this particular key exists yet? That would do it, since the join of the Payment History table and your subquery would find no rows where both the key and caps code fields match in that case. I think an outer join would work here, but you should be selecting your payment_history_key from the audit trail instead of from payment history in that case. I would also not alias the audit trail table and your whole subquery with the same value to avoid confusion. I would try something like this...

select at2.payment_history_key, at2.caps_code,

   decode(ph.amt_check,null,0,ph.amt_check) + at2.sumcol from payment_history ph,

   (select at1.payment_history_key, at1.caps_code,

              sum(adj_amt_check) sumcol     from audit_trail at1
    group by payment_history_key, caps_code) at2  where

    ph.payment_history_key(+) = at2.payment_history_key and     ph.caps_code(+) = at2.caps_code  

I haven't tested this, but it should be close anyways.

HTH,
-Jeff

On Wed, 27 Oct 1999 20:11:43 GMT, amerar_at_ci.chi.il.us wrote:

>
>
>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 Thu Oct 28 1999 - 08:29:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US