converting sql server query to oracle outer join issue

Question

We had the following query in sql server:

SELECT b.columnB, 
     b.displayed_name AS displayName, 
     c.type_cd, 
     c.type_desc, 
     b.month_desc AS month
FROM table1 a, table2 b, table3 c
WHERE b.region_code *= a.columnA
        AND c.program_type_cd *= a.program_type_cd

which, in oracle, got converted to:

SELECT b.columnB,
       b.displayed_name displayName,
       c.type_cd,
       c.type_desc,
       b.month_desc month
FROM   table1 a,
       table2 b,
       table3 c
WHERE b.columnB = a.columnA(+)
       AND c.type_cd = a.type_cd(+)

But while running this in oracle we get an error

"a table must be outer joined to at most one other table"

whats the best way to fix this and keep the same logic as sql server?

Answer 1

Try this once:

SELECT b.columnB,
       b.displayed_name displayName,
       c.type_cd,
       c.type_desc,
       b.month_desc month
FROM   table1 a
       LEFT JOIN table2 b ON b.columnB = a.columnA
       LEFT JOIN tablec c ON c.type_cd = a.type_cd

Answer 2

Why is table1 listed as an OUTER JOIN if you're not returning any data from it? It seems like you'd want table1 to be an inner join to table2, and then do an OUTER between 2 and 3. Like this:

SELECT b.columnB,
       b.displayed_name displayName,
       c.type_cd,
       c.type_desc,
       b.month_desc month
FROM   table1 a,
       table2 b,
       table3 c
WHERE b.columnB = a.columnA
       AND c.type_cd = a.type_cd(+)

On another note, I'd recommond switching to ANSI joins (as in Eric's example) - they're much easier to read, though functionally, they're the same thing and are executed the exact same way.