Home » SQL & PL/SQL » SQL & PL/SQL » Full Outer Joins (and subqueries)
Full Outer Joins (and subqueries) [message #302370] Mon, 25 February 2008 06:42 Go to next message
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 Go to previous messageGo to next message
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!! Smile

[Updated on: Mon, 25 February 2008 10:10]

Report message to a moderator

Re: Full Outer Joins (and subqueries) [message #302477 is a reply to message #302411] Mon, 25 February 2008 19:38 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
So what happens when you rewrite the long inline view with INNER JOIN syntax?
Re: Full Outer Joins (and subqueries) [message #302687 is a reply to message #302370] Tue, 26 February 2008 10:58 Go to previous messageGo to next message
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.

Re: Full Outer Joins (and subqueries) [message #302719 is a reply to message #302687] Tue, 26 February 2008 16:49 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It certainly seems buggy to me.
Previous Topic: URGENT:Problem with API call in stored procedure
Next Topic: Union All improvement
Goto Forum:
  


Current Time: Fri Feb 14 15:58:36 CST 2025