Re: Outer join
- From: Robert Freeman <robertgfreeman@xxxxxxxxx>
- To: breitliw@xxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Sun, 9 Nov 2008 21:05:58 -0800 (PST)
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@xxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
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-l
Other related posts: