R: Query Question

Why do you expect such a result? the inner query would behave as you say. But the whole result depends also on the join conditions in the outer where clause. In table A you have not an entry 2998425, F5. With outer join it should work ... but you have to treat the null values.

Your query shoul become:

 select nvl(ph.payment_history_key, at.payment_history_key),

          nvl(amt_check,0) + 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
     at.payment_history_key = ph.payment_history_key and
     at.caps_code = ph.caps_code (+);

Please tell me if it works!
Riccardo

<
> 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:>
> 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:
Received on Thu Oct 28 1999 - 09:19:46 CDT