Outer join with correlated subquery
399383Sep 16 2003 — edited Sep 17 2003Can an outer join be combined with a correlated subquery? I have 2 tables A and B, and they have columns acct_num and sub_acct in common. A lists account info. B lists transaction history, containing columns for transaction_date and transaction_code. I want to list all A rows, but B needs to be restricted to only the most recent transaction code for each account in A. The code is similar to this:
select
A.acct_num, A.sub_acct,
B.transaction_date, B.transaction_code
from
A, B c
where
A.acct_num = B.acct_num(+) and
A.sub_acct = B.sub_acct(+) and
B.transaction_date = (
select max(transaction_date) from B d
where d.transaction_code = c.transaction_code
);
The outer join works if I exclude the correlated subquery in the last 4 lines above. However, with the correlated subquery included, I get back only a number of rows equal to the rows returned by the subquery.
My solution was to create a table storing the results of the above query, and doing a pure outer join of that new table with A. My other option is to replace the correlated subquery in the WHERE clause with a similar non-correlated subquery grouped by transaction_code in the FROM clause.
Is there a more efficient way to do this?
Thanks for any help!