Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Outer join with correlated subquery

399383Sep 16 2003 — edited Sep 17 2003
Can 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!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 15 2003
Added on Sep 16 2003
4 comments
710 views