-
Outer join - Can't use IN or OR
I have the following code, in which I would like to have the last line of the select check multiple values using the IN clause. Oracle won't let me use an IN or an OR with an outer join. How can I get around this?
Thanks,
P
SELECT xa.address_1
,...
FROM xt_address xa
,xt_phone xp
,xt_phone xf
WHERE xa.request_id = 54172882
AND xa.source_record_fk = 1704147-- p_xt_policy_pk
AND xa.source_table_name = 'POLHOLDER'
AND xa.primary_address_b <> 'Y'
AND xp.request_id(+) = xa.request_id
AND xp.source_record_fk(+) = xa.source_record_fk
AND xp.source_table_name(+) = 'POLHOLDER'
AND xp.phone_number_type_code(+) NOT LIKE '%FAX%'
AND xf.request_id(+) = xa.request_id
AND xf.source_record_fk(+) = xa.source_record_fk
AND xf.phone_number_type_code(+) LIKE '%FAX%'
AND xf.source_table_name(+) in ('POLHOLDER','RISK');
Last edited by mrpaulwass; 11-01-2002 at 12:54 PM.
-
AND
(
xf.source_table_name in ('POLHOLDER','RISK') OR
xf.source_table_name IS NULL
);
-
Chris,
How will this work if I have to have the outer join?
What is the NULL check for?
I tried this and it does work, but now I'd just like to know how.
Thanks,
P
Last edited by mrpaulwass; 11-01-2002 at 02:33 PM.
-
Okay, here's the order of precedence:
OUTER-JOIN Restrictions are performed first
OUTER-JOINs are performed next
non-OUTER-JOIN restrictions are done last
So basically, you are using the (+) to tell Oracle to perform said restriction before the join as opposed to after the join.
Consider the following examples:
CREATE TABLE T1 (C1 NUMBER, C2 CHAR(1));
CREATE TABLE T2 (C1 NUMBER);
TRUNCATE TABLE T1
INSERT INTO T1 VALUES (1,'A');
INSERT INTO T1 VALUES (2,'B');
INSERT INTO T1 VALUES (3,'C');
INSERT INTO T1 VALUES (5,'D');
INSERT INTO T2 VALUES (1);
INSERT INTO T2 VALUES (2);
INSERT INTO T2 VALUES (3);
INSERT INTO T2 VALUES (4);
REGULAR OUTER JOIN
------------------
SELECT T1.C1, T1.C2, T2.C1 C3 FROM T1, T2 WHERE T1.C1(+)=T2.C1
C1 C2 C3
1 A 1
2 A 2
3 B 3
NULL NULL 4
OUTER-JOIN RESTRICTION
----------------------
SELECT T1.C1, T1.C2, T2.C1 C3 FROM T1, T2 WHERE T1.C1(+)=T2.C1 AND T1.C2(+)= 'A'
C1 C2 C3
1 A 1
NULL NULL 2
NULL NULL 3
NULL NULL 4
NOTE: Still get 4 rows.
Restriction was done before join
NON-OUTER-JOIN RESTRICTION
--------------------------
SELECT T1.C1, T1.C2, T2.C1 C3 FROM T1, T2 WHERE T1.C1(+)=T2.C1 AND T1.C2= 'A'
C1 C2 C3
1 A 1
NOTE: Only get 1 row.
Restriction was done after join
NON-OUTER-JOIN RESTRICTION WITH 'OR IS NULL'
--------------------------------------------
SELECT T1.C1, T1.C2, T2.C1 C3 FROM T1, T2 WHERE T1.C1(+)=T2.C1 AND ( T1.C2= 'A' OR T1.C2 IS NULL )
C1 C2 C3
1 A 1
NULL NULL 4
NOTE: The 4 rows from the second example are restricted to 2: Those that started with A and those that did not join at all. Make sense?
NON-OUTER-JOIN RESTRICTION WITH MULTIPLE CRITERIA AND 'OR IS NULL'
------------------------------------------------------------------
SELECT T1.C1, T1.C2, T2.C1 C3 FROM T1, T2 WHERE T1.C1(+)=T2.C1 AND ( T1.C2 IN ('A','B') OR T1.C2 IS NULL )
C1 C2 C3
1 A 1
2 B 2
NULL NULL NULL
NOTE: This might not be what you wanted. What if you still wanted 4 rows, but you only wanted the T1 data for the rows that were A or B?
NON-OUTER-JOIN RESTRICTION WITH MULTIPLE CRITERIA AND 'OR IS NULL' ANS STILL 4 ROWS
--------------------------------------------------------------------------------------
SELECT T.C1, T.C2, T2.C1 C3 FROM (SELECT * FROM T1 WHERE T1.C2 IN ('A', 'B') ) T, T2 WHERE T.C1(+)=T2.C1
C1 C2 C3
1 A 1
2 B 2
NULL NULL 3
NULL NULL 4
NOTE: In this case, we have to manually implement the (+)IN by implementing the IN first, in an in-line view, and then doing the outer-join.
Make sense?
- Chris
Last edited by chrisrlong; 11-01-2002 at 03:15 PM.
-
Perfect..
Thank you again.
Paul
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|