Hi,
I ran this below statement in 12C and 11g Data bases.
select * from temp0101 a ,temp0101 b ,temp0101 c where 1 = 1 and a.sno = b.sno(+) and c.sno = b.sno(+);
in 12 C it executed successfully but in 11i it throwed below error.
ORA-01417: a table may be outer joined to at most one other table
01417. 00000 - "a table may be outer joined to at most one other table"
*Cause: a.b (+) = b.b and a.c (+) = c.c is not allowed
*Action: Check that this is really what you want, then join b and c first
in a view.May in know why 12c Executed while 11g not?
That's because it's fixed in 12c!
In previous releases of Oracle Database, in a query that performed outer joins of more than two pairs of tables, a single table could be the null-generated table for only one other table. Beginning with Oracle Database 12c, a single table can be the null-generated table for multiple tables.
Prior to Oracle Database 12c, having multiple tables on the left hand side of an outer join was illegal and resulted in an ORA-01417 error. The only way to execute such a query was to translate it into ANSI syntax. In Oracle Database 12c, the native syntax for a LEFT OUTER JOIN has been expanded to allow multiple tables on the left hand side
https://docs.oracle.com/database/121/NEWFT/chapter12101.htm#NEWFT138 As the docs indicated, the workaround for this in 11.2 is to use ANSI joins instead:
select * from temp0101 a ,temp0101 b ,temp0101 c
where 1 = 1
and a.sno = b.sno(+)
and c.sno = b.sno(+);
ORA-01417: a table may be outer joined to at most one other table
select * from temp0101 a
left join temp0101 b
on a.sno = b.sno
left join temp0101 c
on c.sno = b.sno;
SNO NAME SNO NAME SNO NAME
1 Santosh 1 Santosh 1 Santosh
2 Santu 2 Santu 2 Santu