Results 1 to 3 of 3

Thread: outerjoint

  1. #1
    Join Date
    Jan 2004
    Posts
    1

    Unanswered: outerjoint

    Hi all - I am new to MsSQL and need a bit of help.
    I have a query that works perfectly in Oracle but not in MsSQL. I understand that there are different commands in MsSQL and for the life of me I cannot find them. Can someone help? Here is the query.

    Select c.*, ins.id from CASES c, INSPECTOR ins
    where c.id = &intID
    and ins.id (+) = decode(c.inspector_id,1,null,c.inspector_id)

    Cheers
    Heki

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select c.*, ins.id
    from CASES c
    LEFT OUTER JOIN
    INSPECTOR ins ON
    ins.id = decode(c.inspector_id,1,null,c.inspector_id) AND
    c.id = &intID;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sql server doesn't support DECODE

    Code:
    select c.*
         , ins.id 
      from CASES c
    left outer
      join INSPECTOR ins 
        on case when c.inspector_id = 1
                then null
                else c.inspector_id end = ins.id
       and c.id = &intID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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