Home » SQL & PL/SQL » SQL & PL/SQL » Outer Join Question
Outer Join Question [message #273445] Wed, 10 October 2007 09:08 Go to next message
californiagirl
Messages: 79
Registered: May 2007
Member
Question:

Why would there be 2 outer joins in this simple query? I was thinking maybe they wanted to remove the indexes or something.Any advice would help.

Thanks


-- Get period start and end dates

SELECT s.start_date, e.end_date,
       DECODE
          (SIGN (e.end_date - s.start_date),
           -1, '*** Note:  From Period is later than To Period, no expenditures reported ***'
          ) err_msg
  FROM apps.pa_periods s, apps.pa_periods e
 WHERE s.period_name(+) = 'MAY-07' AND e.period_name(+) = 'MAY-07';

[Updated on: Wed, 10 October 2007 11:42] by Moderator

Report message to a moderator

Re: Outer Join Question [message #273448 is a reply to message #273445] Wed, 10 October 2007 09:19 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Is that the entire query or are have you omitted part of it?

Is that select perhaps a sub-query ?
Re: Outer Join Question [message #273470 is a reply to message #273445] Wed, 10 October 2007 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I try to understand but I see no meaning.
Can you explain what you want to get.

Regards
Michel
Re: Outer Join Question [message #273488 is a reply to message #273445] Wed, 10 October 2007 11:22 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I do not see any JOIN at all, just cartesian product of the PA_PERIODS rows with PERIOD_NAME = 'MAY-07'.
I just played a little and it seems, the (+) operator is ignored in this case (as CROSS JOIN is used).
SQL> CREATE TABLE pa_periods ( start_date, end_date, period_name ) AS
  2  SELECT d, last_day( d ), to_char( d, 'MON-YY' )
  3  FROM (SELECT add_months( trunc( sysdate, 'MM' ), -level-3 ) d
  4          FROM dual
  5    CONNECT BY level <= 3);

Table created.

SQL> INSERT INTO pa_periods ( start_date, end_date, period_name )
  2  SELECT start_date + 10, end_date - 10, period_name
  3  FROM pa_periods;

3 rows created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM pa_periods ORDER BY start_date;

START_DA END_DATE PERIOD_NAME
-------- -------- ---------------
01.04.07 30.04.07 APR-07
11.04.07 20.04.07 APR-07
01.05.07 31.05.07 MAY-07
11.05.07 21.05.07 MAY-07
01.06.07 30.06.07 JUN-07
11.06.07 20.06.07 JUN-07

6 rows selected.

SQL> SELECT s.start_date, e.end_date
  2  FROM pa_periods s, pa_periods e
  3  WHERE s.period_name(+) = 'MAY-07' AND e.period_name(+) = 'MAY-07';

START_DA END_DATE
-------- --------
01.05.07 31.05.07
01.05.07 21.05.07
11.05.07 31.05.07
11.05.07 21.05.07

SQL> DROP TABLE pa_periods;

Table dropped.

SQL> 
Re: Outer Join Question [message #273491 is a reply to message #273488] Wed, 10 October 2007 11:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think optimizer is messed up.
See my test:
SQL> create table t (start_date date, end_date date, period varchar2(6));

Table created.

SQL> alter session set nls_date_format='DD-MON-YY' nls_date_language=american;

Session altered.

SQL> insert into t 
  2  (select to_date('01/05/2007','DD/MM/YYYY')+5*(rownum-3),
  3          to_date('01/05/2007','DD/MM/YYYY')+5*(rownum-3)+rownum,
  4          to_char(to_date('01/05/2007','DD/MM/YYYY')+5*(rownum-3),'MON-YY')
  5   from dual
  6   connect by level < 10)
  7  /

9 rows created.

SQL> select * from t order by start_date;
START_DAT END_DATE  PERIOD
--------- --------- ------
21-APR-07 22-APR-07 APR-07
26-APR-07 28-APR-07 APR-07
01-MAY-07 04-MAY-07 MAY-07
06-MAY-07 10-MAY-07 MAY-07
11-MAY-07 16-MAY-07 MAY-07
16-MAY-07 22-MAY-07 MAY-07
21-MAY-07 28-MAY-07 MAY-07
26-MAY-07 03-JUN-07 MAY-07
31-MAY-07 09-JUN-07 MAY-07

9 rows selected.

SQL> SELECT s.start_date, e.end_date,
  2         DECODE
  3            (SIGN (e.end_date - s.start_date),
  4             -1, '*** Note:  From Period is later than To Period, no expenditures reported ***'
  5            ) err_msg
  6    FROM t s, t e
  7   WHERE s.period(+) = 'MAY-07' AND e.period(+) = 'MAY-07'
  8  order by 1, 2
  9  /
START_DAT END_DATE  ERR_MSG
--------- --------- ----------------------------------------------------------------------------
01-MAY-07 04-MAY-07
01-MAY-07 10-MAY-07
01-MAY-07 16-MAY-07
01-MAY-07 22-MAY-07
01-MAY-07 28-MAY-07
01-MAY-07 03-JUN-07
01-MAY-07 09-JUN-07
06-MAY-07 04-MAY-07 *** Note:  From Period is later than To Period, no expenditures reported ***
06-MAY-07 10-MAY-07
06-MAY-07 16-MAY-07
06-MAY-07 22-MAY-07
06-MAY-07 28-MAY-07
06-MAY-07 03-JUN-07
06-MAY-07 09-JUN-07
11-MAY-07 04-MAY-07 *** Note:  From Period is later than To Period, no expenditures reported ***
11-MAY-07 10-MAY-07 *** Note:  From Period is later than To Period, no expenditures reported ***
11-MAY-07 16-MAY-07
11-MAY-07 22-MAY-07
11-MAY-07 28-MAY-07
11-MAY-07 03-JUN-07
11-MAY-07 09-JUN-07
16-MAY-07 04-MAY-07 *** Note:  From Period is later than To Period, no expenditures reported ***
16-MAY-07 10-MAY-07 *** Note:  From Period is later than To Period, no expenditures reported ***
16-MAY-07 16-MAY-07
16-MAY-07 22-MAY-07
16-MAY-07 28-MAY-07
16-MAY-07 03-JUN-07
16-MAY-07 09-JUN-07
21-MAY-07 04-MAY-07 *** Note:  From Period is later than To Period, no expenditures reported ***
21-MAY-07 10-MAY-07 *** Note:  From Period is later than To Period, no expenditures reported ***
21-MAY-07 16-MAY-07 *** Note:  From Period is later than To Period, no expenditures reported ***
21-MAY-07 22-MAY-07
21-MAY-07 28-MAY-07
21-MAY-07 03-JUN-07
21-MAY-07 09-JUN-07
26-MAY-07 04-MAY-07 *** Note:  From Period is later than To Period, no expenditures reported ***
26-MAY-07 10-MAY-07 *** Note:  From Period is later than To Period, no expenditures reported ***
26-MAY-07 16-MAY-07 *** Note:  From Period is later than To Period, no expenditures reported ***
26-MAY-07 22-MAY-07 *** Note:  From Period is later than To Period, no expenditures reported ***
26-MAY-07 28-MAY-07
26-MAY-07 03-JUN-07
26-MAY-07 09-JUN-07
31-MAY-07 04-MAY-07 *** Note:  From Period is later than To Period, no expenditures reported ***
31-MAY-07 10-MAY-07 *** Note:  From Period is later than To Period, no expenditures reported ***
31-MAY-07 16-MAY-07 *** Note:  From Period is later than To Period, no expenditures reported ***
31-MAY-07 22-MAY-07 *** Note:  From Period is later than To Period, no expenditures reported ***
31-MAY-07 28-MAY-07 *** Note:  From Period is later than To Period, no expenditures reported ***
31-MAY-07 03-JUN-07
31-MAY-07 09-JUN-07

49 rows selected.

I have only MAY for start_date but MAY and JUN but not APR for end_date.

Regards
Michel
Re: Outer Join Question [message #273494 is a reply to message #273445] Wed, 10 October 2007 11:46 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
It seems correct to me. As you have 7 rows with 'MAY-07' in the PERIOD column, (with start dates in MAY and end dates in MAY or JUN), it results in 49 (7*7) rows with all combinations of these intervals (did not check all of them, but it seems ok).

The (+) operator did not come to play again. But as there is no join condition in this query, it is probably ignored.
Re: Outer Join Question [message #273502 is a reply to message #273494] Wed, 10 October 2007 12:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, it seems you're right.

Regards
Michel
Re: Outer Join Question [message #273538 is a reply to message #273502] Wed, 10 October 2007 15:14 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
So basically the query is just a cartesian case, with no real significance?

Californaigirl
Re: Outer Join Question [message #273586 is a reply to message #273538] Thu, 11 October 2007 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, a cartesian product limited to the rows of the specified period(s).

Regards
Michel
Re: Outer Join Question [message #273594 is a reply to message #273586] Thu, 11 October 2007 01:13 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Why do you want to know? (besides curiousity Wink). Since it's standard apps code, it's just supposed to do it's job and if it doesn't, raise an SR for that.
Re: Outer Join Question [message #273619 is a reply to message #273445] Thu, 11 October 2007 01:57 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I just tried to issue ANSI JOIN and this is the result.
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod
PL/SQL Release 10.1.0.3.0 - Production
CORE    10.1.0.3.0      Production
TNS for Compaq Tru64 UNIX: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production

SQL> SELECT * FROM pa_periods ORDER BY start_date;

START_DA END_DATE PERIOD_NAME
-------- -------- ---------------
01.04.07 30.04.07 APR-07
11.04.07 20.04.07 APR-07
01.05.07 31.05.07 MAY-07
11.05.07 21.05.07 MAY-07
01.06.07 30.06.07 JUN-07
11.06.07 20.06.07 JUN-07

6 rows selected.

SQL> SELECT s.start_date, e.end_date
  2  FROM pa_periods s FULL OUTER JOIN pa_periods e
  3  ON ( s.period_name = 'MAY-07' AND e.period_name = 'MAY-07' )
  4  ORDER BY s.start_date, e.end_date;

START_DA END_DATE
-------- --------
01.04.07
11.04.07
01.05.07 21.05.07
01.05.07 31.05.07
11.05.07 21.05.07
11.05.07 31.05.07
01.06.07
11.06.07
         20.04.07
         30.04.07
         20.06.07

START_DA END_DATE
-------- --------
         30.06.07

12 rows selected.

SQL> 

Basically when you do not specify JOIN condition between the tables, it results in cartesian product. In this case, the resultset is reduced by the WHERE condition.
I do not know what you mean by 'significance': you have requirements and if it shall be obtained by cartesian product, it really makes sense using it.
However using (+) operator in case when no real JOIN condition between two tables is specified, is messy and, as demonstrated, is ignored (at least in the version I use).
Previous Topic: where clause
Next Topic: order by
Goto Forum:
  


Current Time: Sat Dec 10 05:05:10 CST 2016

Total time taken to generate the page: 0.32927 seconds