Outer join on multi where clause tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Outer join on multi where clause tables

  1. #1
    Join Date
    Mar 2001
    Posts
    63
    OK, so my title is confusing...

    Anyway, I am trying to blend in several views into a single view. Before, the views broke down kind of like this:

    View1

    Select p.plat_id,
    a.thisandthat,
    a.somethingorother,
    b.thisandthat,
    b.somethingorother
    from
    platform p,
    otherview1 a,
    otherview2 b
    where
    p.plat_id = a.plat_id(+)
    and p.plat_id = b.plat_id(+)

    the "otherviews" went like:

    select a.plat_id,
    a.thisandthat,
    b.somethingorother
    from table1 a,
    table2 b
    where a.org_id=b.org_id
    and a.code="only thing different for both views";


    And life was good... however, the REAL view I am blending involved 12 views, and this is causing a distribution nightmare. Therefore, I was just going to make everything into one big query. I can get it to parse fine, but the problem I am having is with foreign keys, since the "otherviews" involved multiple tables.

    This is what I have right now:

    select p.plat_id,
    a1.thisandthat a1_thisandthat,
    b1.somethingorother b1_somethingorother,
    a2.thisandthat a2_thisandthat,
    b2.somethingorother b2_somethingorother
    from
    platform p,
    table1 a1,
    table2 b1,
    table1 a2,
    table2 b2
    where
    p.plat_id = a1.plat_id(+)
    and a1.org_id = b1.org_id
    and a1.code="only thing different for both views"
    and p.plat_id = a2.plat_id(+)
    and a2.org_id = b2.org_id
    and a2.code="only thing different for both views";

    The data comes out fine except for the outer joins. Wherever there is missing data (remember this is done over 12 different tables), it will discard the whole record. Even though I have an outer join indicator on the main linkage, it still throws out the record.

    Does anyone have any idea how I can resolve this?




  2. #2
    Join Date
    Apr 2001
    Posts
    37
    You need much more (+). Try 2 variants (I'm not sure which is correct)

    1)
    select p.plat_id,
    a1.thisandthat a1_thisandthat,
    b1.somethingorother b1_somethingorother,
    a2.thisandthat a2_thisandthat,
    b2.somethingorother b2_somethingorother
    from platform p,
    table1 a1,
    table2 b1,
    table1 a2,
    table2 b2
    where p.plat_id = a1.plat_id(+)
    and a1.org_id = b1.org_id(+)
    and a1.code(+) ="only thing different for both views"
    and p.plat_id = a2.plat_id(+)
    and a2.org_id = b2.org_id(+)
    and a2.code(+)="only thing different for both views";

    2)
    select p.plat_id,
    a1.thisandthat a1_thisandthat,
    b1.somethingorother b1_somethingorother,
    a2.thisandthat a2_thisandthat,
    b2.somethingorother b2_somethingorother
    from platform p,
    table1 a1,
    table2 b1,
    table1 a2,
    table2 b2
    where p.plat_id = a1.plat_id(+)
    and a1.org_id(+) = b1.org_id
    and a1.code(+) ="only thing different for both views"
    and p.plat_id = a2.plat_id(+)
    and a2.org_id(+) = b2.org_id
    and a2.code(+)="only thing different for both views";
    inosov
    Brainbench MVP for Oracle DBA

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    The first one

    - Chris


  4. #4
    Join Date
    Mar 2001
    Posts
    63
    Thanks for the reply guys, that seems to be working for the most part.

    Next question though, some of the views use an IN clause for the a1.code, and I can't use an outer join for an IN or OR clause.

    Any ways around that?

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Huh?

  6. #6
    Join Date
    Mar 2001
    Posts
    63
    Nevermind, I don't think it matters that I use the IN clause, but to clarify anyway... I was able to use an outer join when the a1.code used an explicit comparison (=), but when I use an IN clause (a1.code IN ('this', 'or', 'that')), then I can't use the outer join.


  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Ahhh...


    Well, for completeness sake, I can show you how to do that anyway.

    If you ever have an issue where you can't *directly* use an outer join, you must 'wrap' that inside an in-line view. To wit:

    select p.plat_id,
    a1.thisandthat a1_thisandthat,
    b1.somethingorother b1_somethingorother,
    a2.thisandthat a2_thisandthat,
    b2.somethingorother b2_somethingorother
    from platform p,
    (select thisandthat, org_idm plat_id from table1 where code in (a,b,c,d)) a1
    table2 b1,
    (select thisandthat, org_idm plat_id from table1 where code in (a,b,c,d)) a2
    table2 b2
    where p.plat_id = a1.plat_id(+)
    and a1.org_id = b1.org_id(+)
    and p.plat_id = a2.plat_id(+)
    and a2.org_id = b2.org_id(+)

    Of course, as soon as you do this, you change the plan significantly, so you will have that to worry about, but this is the basic solution

    - Chris

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