Re: Outer join
From: Robert Freeman <robertgfreeman_at_yahoo.com>
Date: Sun, 9 Nov 2008 21:05:58 -0800 (PST)
Message-ID: <27528.48942.qm@web38902.mail.mud.yahoo.com>
Date: Sun, 9 Nov 2008 21:05:58 -0800 (PST)
Message-ID: <27528.48942.qm@web38902.mail.mud.yahoo.com>
Is is possible the NULL's in BOTH tables are causing some weirdness in the outer join?
If you remove the NULL columns, does the query work? If so, then that would make them suspect.
Just a thought...
RF
Robert G. Freeman
Author:
OCP: Oracle Database 11g Administrator Certified Professional Study Guide (Sybex)
Oracle Database 11g New Features (Oracle Press)
Portable DBA: Oracle (Oracle Press)
Oracle Database 10g New Features (Oracle Press)
Oracle9i RMAN Backup and Recovery (Oracle Press)
Oracle9i New Features (Oracle Press)
Other various titles out of print now...
Blog: http://robertgfreeman.blogspot.com
The LDS Church is looking for DBA's. You do have to be a Church member in
good standing. A lot of kind people write me, concerned I may be breaking
the law by saying you have to be a Church member. It's legal I promise! :-)
- Original Message ---- From: Wolfgang Breitling <breitliw_at_centrexcc.com> To: oracle-l_at_freelists.org Sent: Sunday, November 9, 2008 6:20:52 PM Subject: Outer join
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 -- http://www.freelists.org/webpage/oracle-lReceived on Sun Nov 09 2008 - 23:05:58 CST