30 August 2008

Oracle ANSI 92 JOIN syntax error Lately I’m working a lot with Oracle APPS, doing mainly ad-hoc reporting. One of my nightmares is an Oracle bug related to ANSI 92 syntax: “ORA-01445: cannot select ROWID from, or sample, a join without a key-preserved table” Unfortunately, even if the bug was solved by Oracle, it seems the update was missed on some servers and the bug haunts my queries almost on a daily basis. Having an SQL Server background and, for code clearness, I prefer ANSI 92 JOIN syntax: SELECT A.column1, B.column2 FROM table1 A JOIN table2 B ON A.column1 = B.column2 instead of using the old fashioned writing: SELECT A.column1, B.column2 FROM table1 A , table2 B WHERE A.column1 = B.column2 In theory the two queries should provide the same output and have, hopefully, similar performance, but that’s not in scope of this post. The problem with ANSI 92 syntax is that, on some Oracle installations, when the number of joins exceeds a certain limit, usually greater than 7, I get the above error. I can avoid that by using a subquery or a view which would allow me to split the logic and limit the number of direct joins to a table. That can be helpful sometimes, but most of the times it’s cumbersome because it requires more development time and awkward design solutions.

No comments: