Outer join

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Sun, 09 Nov 2008 18:20:52 -0700
Message-Id: <200811100120.mAA1Kqcx023114@mail97c0.megamailservers.com>

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:

c1    c2     c3     c4   c5    c6  c7 c8  c9 c10 c11      c1 c13 c14 
c15    pd  fy        amt1        amt2        amt3

----- ------ ------ ---- ----- --- -- --- -- --- -------- -- --- --- --- ----- --- ----------- ----------- ----------- DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR EUR 2008 3 -1,384.62 -1,384.62 -1,384.62 DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR USD 2008 3 -1,384.62 -2,041.55 -1,384.62

DEF03 112233 556677 7777 ABC01            99     AEIOU    B      USD 
USD  2008   3   -2,041.55        0.00        0.00
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      EUR 
EUR  2008   1   -5,204.55   -5,204.55   -5,204.55
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      EUR 
EUR  2008   2  -10,489.72  -10,489.72  -10,489.72
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      EUR 
USD  2008   1   -5,204.55   -7,663.18   -5,204.55
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      EUR 
USD  2008   2  -10,489.72  -15,445.06  -10,489.72
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      USD 
USD  2008   1   -7,663.18        0.00        0.00
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      USD 
USD  2008   2  -15,445.06        0.00        0.00

foo_tmp:
c1    c2     c3     c4   c5    c6  c7 c8  c9 c10 c11      c1 c13 c14 

c15 pd fy
----- ------ ------ ---- ----- --- -- --- -- --- -------- -- --- --- --- ----- ---
DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR EUR 2008 1
DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR EUR 2008 2
DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR EUR 2008 3
DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR EUR 2008 4
DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR USD 2008 1
DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR USD 2008 2
DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR USD 2008 3
DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR USD 2008 4
DEF03 112233 556677 7777 ABC01 99 AEIOU U USD USD 2008 1
DEF03 112233 556677 7777 ABC01 99 AEIOU U USD USD 2008 2
DEF03 112233 556677 7777 ABC01 99 AEIOU U USD USD 2008 3
DEF03 112233 556677 7777 ABC01 99 AEIOU U USD USD 2008 4
DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR EUR 2008 1
DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR EUR 2008 2
DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR EUR 2008 3
DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR EUR 2008 4
DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR USD 2008 1
DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR USD 2008 2
DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR USD 2008 3
DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR USD 2008 4
DEF03 112233 556677 7777 ABC01 99 AEIOU U USD USD 2008 1
DEF03 112233 556677 7777 ABC01 99 AEIOU U USD USD 2008 2
DEF03 112233 556677 7777 ABC01 99 AEIOU U USD USD 2008 3
DEF03 112233 556677 7777 ABC01 99 AEIOU U USD USD 2008 4
DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR EUR 2008 1
DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR EUR 2008 2
DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR EUR 2008 3
DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR EUR 2008 4
DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR USD 2008 1
DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR USD 2008 2
DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR USD 2008 3
DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR USD 2008 4
DEF03 112233 556677 7777 ABC01 99 AEIOU B USD USD 2008 1
DEF03 112233 556677 7777 ABC01 99 AEIOU B USD USD 2008 2
DEF03 112233 556677 7777 ABC01 99 AEIOU B USD USD 2008 3
DEF03 112233 556677 7777 ABC01 99 AEIOU B USD USD 2008 4 the content of foo_tmp is designed to have pd values of 1 to 4 for every 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 c14 
c15    pd  fy        amt1        amt2        amt3

----- ------ ------ ---- ----- --- -- --- -- --- -------- -- --- --- --- ----- --- ----------- ----------- ----------- DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR EUR 2008 1
DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR EUR 2008 2
DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR EUR 2008 3 -1,384.62 -1,384.62 -1,384.62 DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR EUR 2008 4
DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR USD 2008 1
DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR USD 2008 2
DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR USD 2008 3 -1,384.62 -2,041.55 -1,384.62 DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR USD 2008 4
DEF03 112233 556677 7777 ABC01 99 AEIOU B USD USD 2008 1
DEF03 112233 556677 7777 ABC01 99 AEIOU B USD USD 2008 2

DEF03 112233 556677 7777 ABC01            99     AEIOU    B      USD 
USD  2008   3   -2,041.55        0.00        0.00
DEF03 112233 556677 7777 ABC01            99     AEIOU    B      USD 
USD  2008   4
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      EUR 
EUR  2008   1   -5,204.55   -5,204.55   -5,204.55
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      EUR 
EUR  2008   1   -5,204.55   -5,204.55   -5,204.55
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      EUR 
EUR  2008   2  -10,489.72  -10,489.72  -10,489.72
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      EUR 
EUR  2008   2  -10,489.72  -10,489.72  -10,489.72
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      EUR 
EUR  2008   3
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      EUR 
EUR  2008   3
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      EUR 
EUR  2008   4
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      EUR 
EUR  2008   4
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      EUR 
USD  2008   1   -5,204.55   -7,663.18   -5,204.55
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      EUR 
USD  2008   1   -5,204.55   -7,663.18   -5,204.55
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      EUR 
USD  2008   2  -10,489.72  -15,445.06  -10,489.72
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      EUR 
USD  2008   2  -10,489.72  -15,445.06  -10,489.72
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      EUR 
USD  2008   3
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      EUR 
USD  2008   3
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      EUR 
USD  2008   4
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      EUR 
USD  2008   4
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      USD 
USD  2008   1   -7,663.18        0.00        0.00
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      USD 
USD  2008   1   -7,663.18        0.00        0.00
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      USD 
USD  2008   2  -15,445.06        0.00        0.00
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      USD 
USD  2008   2  -15,445.06        0.00        0.00
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      USD 
USD  2008   3
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      USD 
USD  2008   3
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      USD 
USD  2008   4
DEF03 112233 556677 7777 ABC01            99     AEIOU    U      USD 

USD 2008 4 What am I doing wrong? Why do I get duplicates if foo_bar has rows for 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

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

Received on Sun Nov 09 2008 - 19:20:52 CST