Outjoin - Can't use IN or OR
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Outjoin - Can't use IN or OR

  1. #1
    Join Date
    Nov 2001
    Location
    New Brunswick, NJ
    Posts
    67

    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.

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    AND
    (
    xf.source_table_name in ('POLHOLDER','RISK') OR
    xf.source_table_name IS NULL
    );
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #3
    Join Date
    Nov 2001
    Location
    New Brunswick, NJ
    Posts
    67
    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.

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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.
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  5. #5
    Join Date
    Nov 2001
    Location
    New Brunswick, NJ
    Posts
    67
    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
  •  


Click Here to Expand Forum to Full Width