Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Posts
    11

    Question Unanswered: Problem Convert SQL Outer Join to Oracle Outer Join

    I am hoping someone can help me with this problem. I am trying to convert SQL Server's outer join to Oracle outer join. Right now I have a SQL statement like this.

    Select m.MID, s.TestProc, s.Result, s.Tester, s.Package
    From MISSION m Left Outer Join TEST_STATUS s
    On m.MID = s.Mission
    And s.Package=4167

    This statement will give me the result set as so

    aaa NULL NULL NULL NULL
    bbb NULL NULL NULL NULL
    ccc 100001 Passed jlin 4167
    ddd NULL NULL NULL NULL
    eee NULL NULL NULL 4167
    fff NULL NULL NULL NULL
    ggg NULL NULL NULL 4167
    hhh NULL NULL NULL 4167
    iii 100001 No Data cmlush 4167
    jjj NULL NULL NULL NULL
    kkk NULL NULL NULL 4167
    lll NULL NULL NULL 4167
    mmm NULL NULL NULL NULL
    ZZZ NULL NULL NULL NULL

    I convert the SQL Statement to Oracle Syntax as so:

    Select m.MID, s.TestProc, s.Test_Date, s.Package
    From MISSION m, TEST_STATUS s
    Where m.MID = s.Mission (+)
    And s.Package=4167

    But the result set I get is as such

    ccc 100001 Passed jlin 4167
    eee NULL NULL NULL 4167
    ggg NULL NULL NULL 4167
    hhh NULL NULL NULL 4167
    iii 100001 No Data cmlush 4167
    kkk NULL NULL NULL 4167
    lll NULL NULL NULL 4167

    The result is like I did not perform any outer join at all. I try to switch the (+) operator around, but I am still getting the same result set. Did I do anything wrong, or is there something about Oracle outer join that I am missing? I am on Oracle 8.1.7. I would appreciate any advice on this problem. Thank you.

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    try

    Select m.MID, s.TestProc, s.Test_Date, s.Package
    From MISSION m, TEST_STATUS s
    Where m.MID = s.Mission (+)
    And NVL(s.Package,4167)=4167

    if you use 'And s.Package=4167' then you invalidate the outer join as you are saying s.* is null and s.Package=4167 which are mutually exclusive in Oracle.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •