Full Outer Joins (and subqueries) [message #302370] |
Mon, 25 February 2008 06:42  |
moshea
Messages: 51 Registered: February 2008 Location: Dublin, Ireland
|
Member |
|
|
Hi,
Perhaps someone can help me out. I understand that Full Outer Joins effectively return rows equivalent to combining those which would be returned from two left and right outer join queries.
However, where I'm a little vague is how this should apply when using subqueries.
Specifically I'm wondering In the examples I've shown here, is it expected behaviour that
a) The Full Outer Join returns a different resultset depending on which "half" of the query is first? (ie it2 or dim)
b) When I replace the use of the subquery with a table, I get a different result-set?
I can return the desired result-set (ie the same resultset as when using tables and not subqueries) using the union of right and left outer join queries, but am interested in knowing where exactly my understanding of FULL OUTER JOINS is failing me.
SQL>
SQL>
SQL> SELECT COUNT ( * )
2 FROM ( SELECT a.transaction_no_in_orig_system
3 FROM a_transactions a
4 , a_mmdeposits mm
5 , a_funds fd
6 WHERE a.sec_ref = mm.sec_ref
7 AND a.portfolio = fd.por_assets(+)
8 AND a.leg_no = mm.leg_no
9 AND a.transaction_cancellation_flag = 'Active'
10 AND a.transaction_origin_name = 'IT2'
11 AND a.instrument_type = 'Deposit'
12 AND ( mm.maturity_date >= TRUNC ( SYSDATE ) )
13 AND ( a.sec_short_name LIKE 'SDP%'
14 OR a.sec_short_name LIKE 'SLT%'
15 OR a.sec_short_name LIKE 'DPI%' ) ) dim
16 FULL OUTER JOIN
17 ( SELECT trade_id
18 FROM it2_mm_trades_load ) it2
19 ON ( dim.transaction_no_in_orig_system = it2.trade_id )
20 /
COUNT(*)
----------
537
SQL> --Change the order of the subqueries ie it2 before dim.
SQL> SELECT COUNT ( * )
2 FROM ( SELECT trade_id
3 FROM it2_mm_trades_load ) it2
4 FULL OUTER JOIN
5 ( SELECT a.transaction_no_in_orig_system
6 FROM a_transactions a
7 , a_mmdeposits mm
8 , a_funds fd
9 WHERE a.sec_ref = mm.sec_ref
10 AND a.portfolio = fd.por_assets(+)
11 AND a.leg_no = mm.leg_no
12 AND a.transaction_cancellation_flag = 'Active'
13 AND a.transaction_origin_name = 'IT2'
14 AND a.instrument_type = 'Deposit'
15 AND ( mm.maturity_date >= TRUNC ( SYSDATE ) )
16 AND ( a.sec_short_name LIKE 'SDP%'
17 OR a.sec_short_name LIKE 'SLT%'
18 OR a.sec_short_name LIKE 'DPI%' ) ) dim
19 ON ( dim.transaction_no_in_orig_system = it2.trade_id )
20 /
COUNT(*)
----------
402
SQL> drop table dim
2 /
Table dropped.
SQL> CREATE TABLE dim
2 AS
3 SELECT a.transaction_no_in_orig_system
4 FROM a_transactions a
5 , a_mmdeposits mm
6 , a_funds fd
7 WHERE a.sec_ref = mm.sec_ref
8 AND a.portfolio = fd.por_assets(+)
9 AND a.leg_no = mm.leg_no
10 AND a.transaction_cancellation_flag = 'Active'
11 AND a.transaction_origin_name = 'IT2'
12 AND a.instrument_type = 'Deposit'
13 AND ( mm.maturity_date >= TRUNC ( SYSDATE ) )
14 AND ( a.sec_short_name LIKE 'SDP%'
15 OR a.sec_short_name LIKE 'SLT%'
16 OR a.sec_short_name LIKE 'DPI%' )
17 /
Table created.
SQL> drop table it2;
Table dropped.
SQL> CREATE TABLE it2 AS
2 SELECT trade_id
3 FROM it2_mm_trades_load
4 /
Table created.
SQL> SELECT COUNT ( * )
2 FROM dim FULL OUTER JOIN it2
3 ON ( dim.transaction_no_in_orig_system = it2.trade_id )
4 /
COUNT(*)
----------
403
SQL> SELECT COUNT ( * )
2 FROM it2 FULL OUTER JOIN dim
3 ON ( dim.transaction_no_in_orig_system = it2.trade_id )
4 /
COUNT(*)
----------
403
SQL>
Any enlightenment, would be much appreciated.
Thanks,
Michael
|
|
|
Re: Full Outer Joins (and subqueries) [message #302411 is a reply to message #302370] |
Mon, 25 February 2008 09:29   |
moshea
Messages: 51 Registered: February 2008 Location: Dublin, Ireland
|
Member |
|
|
Perhaps I'm clutching at straws, but I'm actually getting very similiar behaviour to what is described here.
I'm usually loathe to shout "BUG!!!", particularly when I'm not 100% on the what the expected behaviour should be.
I'm currently searching asktom & metalink for any additional info.
EDIT : According to metalink article 4582764.8, it looks like my combination of oracle style outer join syntax and ANSI syntax, together with an inline view, is bust in oracle version < v11.
Cheers,
Michael
PS : If you're wondering why I posted here, before completing my search of the forums and metalink etc ... well then ... errrm, move along, nothing to see here!!
[Updated on: Mon, 25 February 2008 10:10] Report message to a moderator
|
|
|
|
Re: Full Outer Joins (and subqueries) [message #302687 is a reply to message #302370] |
Tue, 26 February 2008 10:58   |
moshea
Messages: 51 Registered: February 2008 Location: Dublin, Ireland
|
Member |
|
|
When I re-wrote the query, such that the inline view was using soley ANSI syntax, I still got an incorrect result-set.
My assumption is, that because those a_*'s in the inline view, are views (belonging to a third party package), which themselves use native oracle syntax for their joins, that thats why the problem persists.
|
|
|
|