CODEWITHSUNDEEP
sql-serveroracle-database
Omnipresent
Omnipresent

Reputation: 30404

converting sql server query to oracle outer join issue

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?

Upvotes: 1

Views: 451

Answers (2)

SqlRyan
SqlRyan

Reputation: 33914

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.

Upvotes: 0

Eric Petroelje
Eric Petroelje

Reputation: 60508

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

Upvotes: 1

Related Questions