Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    9

    Unanswered: outer join in where clause

    Hi all,

    I'v a Oracle query which I want to copy in SQL Server.
    the query in Oracle is as follows
    select *
    from table1 a
    , table2 b
    where a.code(+) = b.code
    and a.property(+)='TEST'

    The query in SQL Server
    select *
    from table1 right outer join table2 on table1.code = table2.code
    where a.property = 'TEST' (here I don't know what to put)

    =*, a.property(+) doesn't work


    Thanx
    Lambik

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: outer join in where clause

    I think you just have to add an AND to your ON clause. Try this (not tested, but recollected vaguely from something I tried once before):

    Code:
    select  *
    from table1 a right outer join table2 b on 
        a.code = b.code and 
        a.property = 'TEST'
    Looking at it carefully, I think you might have meant to put LEFT OUTER JOIN, but I'm not positive. I always get confused with the Oracle notation. In any case, it should be a simple fix.

    Regards,

    hmscott


    Originally posted by Lambik
    Hi all,

    I'v a Oracle query which I want to copy in SQL Server.
    the query in Oracle is as follows
    select *
    from table1 a
    , table2 b
    where a.code(+) = b.code
    and a.property(+)='TEST'

    The query in SQL Server
    select *
    from table1 right outer join table2 on table1.code = table2.code
    where a.property = 'TEST' (here I don't know what to put)

    =*, a.property(+) doesn't work


    Thanx
    Lambik

  3. #3
    Join Date
    Nov 2003
    Posts
    48
    In the original query,
    I doubt the "(+)" in "a.property(+)='TEST'" is necessary or even semantically correct? If you remove the it from the query and the
    results are still correct, then you can rewrite the query in SQL Server as

    Select *
    from table1 a, table2 b
    where a.code =* b.code
    and a.property ='TEST'
    Shianmiin

  4. #4
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Hi there!

    The notation "(+)" defines the outer join in Oracle. So, if you want to have the same results in Oracle and SQL Server you have to convert the "(+)" to an outer join.

    Greetings,
    Carsten

  5. #5
    Join Date
    May 2003
    Posts
    26
    Code:
    where a.code(+) = b.code
    and a.property(+)='TEST'
    I think what Shianmiin meant was that the both of the (+) notations in this WHERE clause refer to the same table. The first will automatically bring back all non-matching rows on table a making the second one redundant.

Posting Permissions

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