Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: help with ansi 92 join syntax

    I'm trying to help someone write SQL against Oracle, but since they're using an ODBC driver, I need to use ANSI 92 syntax. I can usually get it, but in this case, there some additional criteria on the inner table of the outer join, and I can't seem to figure out how to re-write the following:

    Code:
    select c.filenr,
           c.doi,
           c.emp_knowdt,
           c.insurer_no,
           c.accept,
           s.pvv_external_value as accept_desc,
           c.disabl
    from  emp_c c, 
          lookup s
    where c.accept = s.pvv_internal_value(+) and 
          'EMP' = s.pvv_table_name(+) and
          'SALARY' = s.pvv_field_name(+)and
          insurer_no = 516
    All I know is how to start off the SQL, but the additional criteria on pvv_field_name and pvv_table_name is causing an inner join effect:

    Code:
    select c.filenr,
           c.doi,
           c.emp_knowdt,
           c.insurer_no,
           c.accept,
           s.pvv_external_value as accept_desc,
           c.disabl
    from emp_c c left join lookup s
         on c.accept = s.pvv_internal_value 
    where  s.pvv_table_name = 'EMP' and
           s.pvv_field_name = 'SALARY' and
           insurer_no = 516
    -cf

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    indeed, an "inner join effect" is exactly what you get

    just move the conditions on the right table into the ON clause --
    Code:
    select c.filenr
         , c.doi
         , c.emp_knowdt
         , c.insurer_no
         , c.accept
         , s.pvv_external_value as accept_desc
         , c.disabl
      from emp_c c 
    left outer
      join lookup s
        on s.pvv_internal_value = c.accept
       and s.pvv_table_name = 'EMP' 
       and s.pvv_field_name = 'SALARY'
     where c.insurer_no = 516
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    So kind!

    Thanks
    -cf

Posting Permissions

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