Outer join

From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Sun, 09 Nov 2008 18:20:52 -0700

Can someone show me the errors of my way, please.

I have the following two tables:

SQL> desc foo_bar
 Name              Null?    Type
 ----------------- -------- ------------
 COL_01            NOT NULL VARCHAR2(5)
 COL_02            NOT NULL VARCHAR2(10)
 COL_03            NOT NULL VARCHAR2(10)
 COL_04            NOT NULL VARCHAR2(10)
 COL_05            NOT NULL VARCHAR2(8)
 COL_06            NOT NULL VARCHAR2(6)
 COL_07            NOT NULL VARCHAR2(5)
 COL_08            NOT NULL VARCHAR2(5)
 COL_09            NOT NULL VARCHAR2(10)
 COL_10            NOT NULL VARCHAR2(15)
 COL_11            NOT NULL VARCHAR2(10)
 COL_12            NOT NULL VARCHAR2(4)
 COL_13            NOT NULL VARCHAR2(3)
 COL_14            NOT NULL VARCHAR2(3)
 FY                NOT NULL NUMBER(38)
 PD                NOT NULL NUMBER(38)
 COL_15            NOT NULL VARCHAR2(3)
 AMOUNT_1          NOT NULL NUMBER(26,3)
 AMOUNT_2          NOT NULL NUMBER(26,3)
 AMOUNT_3          NOT NULL NUMBER(26,3)

and

SQL> desc foo_tmp
 Name              Null?    Type
 ----------------- -------- ------------
 COL_01            NOT NULL VARCHAR2(5)
 COL_02            NOT NULL VARCHAR2(10)
 COL_03            NOT NULL VARCHAR2(10)
 COL_04            NOT NULL VARCHAR2(10)
 COL_05            NOT NULL VARCHAR2(8)
 COL_06            NOT NULL VARCHAR2(6)
 COL_07            NOT NULL VARCHAR2(5)
 COL_08            NOT NULL VARCHAR2(5)
 COL_09            NOT NULL VARCHAR2(10)
 COL_10            NOT NULL VARCHAR2(15)
 COL_11            NOT NULL VARCHAR2(10)
 COL_12            NOT NULL VARCHAR2(4)
 COL_13            NOT NULL VARCHAR2(3)
 COL_14            NOT NULL VARCHAR2(3)
 FY                NOT NULL NUMBER(38)
 PD                NOT NULL NUMBER(38)
 COL_15            NOT NULL VARCHAR2(3)

with the following contents

foo_bar:


foo_tmp:

c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c1 c13 c14c15 pd fy----- ------ ------ ---- ----- --- -- --- -- --- -------- -- --- ------ ----- ---DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 1DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 2DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 1DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 2DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 1DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 2DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 1DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 2DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 1DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 2DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 1DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 2DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU B EUREUR 2008 1DEF03 112233 556677 7777 ABC01 99 AEIOU B EUREUR 2008 2DEF03 112233 556677 7777 ABC01 99 AEIOU B EUREUR 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU B EUREUR 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU B EURUSD 2008 1DEF03 112233 556677 7777 ABC01 99 AEIOU B EURUSD 2008 2DEF03 112233 556677 7777 ABC01 99 AEIOU B EURUSD 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU B EURUSD 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU B USDUSD 2008 1DEF03 112233 556677 7777 ABC01 99 AEIOU B USDUSD 2008 2DEF03 112233 556677 7777 ABC01 99 AEIOU B USDUSD 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU B USDUSD 2008 4

the content of foo_tmp is designed to have pd values of 1 to 4 forevery combination of col_01 to col_15 and fy


I then build the outer join

SELECT B.COL_01, B.COL_02, B.COL_03, B.COL_04, B.COL_05, B.COL_06,B.COL_07, B.COL_08, B.COL_09, B.COL_10, B.COL_11, B.COL_12, B.COL_13, B.COL_14,B.COL_15, B.PD, B.FY

, A.AMOUNT_1, A.AMOUNT_2, A.AMOUNT_3
FROM FOO_BAR A
, FOO_TMP B
WHERE A.COL_01(+) = B.COL_01
  AND A.COL_02(+) = B.COL_02
  AND A.COL_03(+) = B.COL_03
  AND A.COL_04(+) = B.COL_04
  AND A.COL_05(+) = B.COL_05
  AND A.COL_06(+) = B.COL_06
  AND A.COL_07(+) = B.COL_07
  AND A.COL_08(+) = B.COL_08
  AND A.COL_09(+) = B.COL_09
  AND A.COL_10(+) = B.COL_10
  AND A.COL_11(+) = B.COL_11
  AND A.COL_12(+) = B.COL_12
  AND A.COL_13(+) = B.COL_13
  AND A.COL_14(+) = B.COL_14
  AND A.COL_15(+) = B.COL_15
  AND A.FY(+)     = B.FY
  AND A.PD(+)     = B.PD

and get this result:

c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c1 c13 c14c15 pd fy amt1 amt2 amt3----- ------ ------ ---- ----- --- -- --- -- --- -------- -- --- ------ ----- --- ----------- ----------- -----------DEF03 112233 556677 7777 ABC01 99 AEIOU B EUREUR 2008 1DEF03 112233 556677 7777 ABC01 99 AEIOU B EUREUR 2008 2DEF03 112233 556677 7777 ABC01 99 AEIOU B EUREUR 2008 3 -1,384.62 -1,384.62 -1,384.62DEF03 112233 556677 7777 ABC01 99 AEIOU B EUREUR 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU B EURUSD 2008 1DEF03 112233 556677 7777 ABC01 99 AEIOU B EURUSD 2008 2DEF03 112233 556677 7777 ABC01 99 AEIOU B EURUSD 2008 3 -1,384.62 -2,041.55 -1,384.62DEF03 112233 556677 7777 ABC01 99 AEIOU B EURUSD 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU B USDUSD 2008 1DEF03 112233 556677 7777 ABC01 99 AEIOU B USDUSD 2008 2DEF03 112233 556677 7777 ABC01 99 AEIOU B USDUSD 2008 3 -2,041.55 0.00 0.00DEF03 112233 556677 7777 ABC01 99 AEIOU B USDUSD 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 1 -5,204.55 -5,204.55 -5,204.55DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 1 -5,204.55 -5,204.55 -5,204.55DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 2 -10,489.72 -10,489.72 -10,489.72DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 2 -10,489.72 -10,489.72 -10,489.72DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 1 -5,204.55 -7,663.18 -5,204.55DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 1 -5,204.55 -7,663.18 -5,204.55DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 2 -10,489.72 -15,445.06 -10,489.72DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 2 -10,489.72 -15,445.06 -10,489.72DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 1 -7,663.18 0.00 0.00DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 1 -7,663.18 0.00 0.00DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 2 -15,445.06 0.00 0.00DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 2 -15,445.06 0.00 0.00DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 4

What am I doing wrong? Why do I get duplicates if foo_bar has rowsfor different pd's with the rest of the columns the same.


PS, columns that don't seem to have a value have a blank.


Regards

Wolfgang Breitling
Centrex Consulting Corporation

http://www.centrexcc.com

--
//www.freelists.org/webpage/oracle-l

Follow-Ups:
- Re: Outer join
  - From: Robert Freeman
- Re: Outer join
  - From: Nigel Thomas

References:
- purge_audit_template
  - From: Ray Stell

Outer join

Other related posts: