-
Website
http://awads.net/wp/ -
Original page
http://awads.net/wp/2007/06/14/when-ansi-sql-join-syntax-does-not-work-in-oracle/ -
Subscribe
All Comments -
Community
-
-
Top Commenters
-
Popular Threads
All Comments
Yes its a bug. Although I haven't had any success getting official confirmation of that.
My workaround is to continue using ANSI join syntax but to use in-line tables in my from clause.
e.g. instead of
FROM table_a a JOIN table_b b ON a.fk=b.pk
I use
FROM (SELECT fk, other_col FROM table_a) a
JOIN (SELECT pk, other_other_col FROM table_b) b ON a.fk = b.pk
Its a nasty bodge but still gives much more readable SQL (IMHO of course).
Sorry, where I said readable SQL what I should have added is that it is more readable because it still allows you to separate the join predicates from the actual where predicates.
Which is my major problem with the 'good old' Oracle syntax.
Eep definitely a bug. Does it happen when the total number of columns selected exceeds 1050, or if the total number of columns in the tables exceeds 1050 (regardless of which columns are actually specified)?
Judging from the example Eddie it is the total number of colums, regardless of how many columns are actually selected. Select null from ... tells me that it doesn't matter how many columns are actually selected ;-). I would have to try this out myself, but it appears I don't want to switch over to Ansi SQL, when there is no need for it.
Good pointer, thanks Eddie.
A dump question, but who has 1050 columns in 1 table or 7 tables? That are still 150 each!
I would question the data model...
Patrick
Let's see how many total columns exist in the tables I select from in my example above:
<pre>
APPS@sigma> SELECT COUNT (*)
</pre>2 FROM all_tab_columns
3 WHERE table_name IN
4 ('OE_ORDER_HEADERS_ALL',
5 'OE_ORDER_LINES_ALL',
6 'RA_CUSTOMERS',
7 'MTL_SYSTEM_ITEMS_B',
8 'ORG_ORGANIZATION_DEFINITIONS',
9 'RA_SALESREPS_ALL',
10 'HR_LOCATIONS_ALL'
11 );
COUNT(*)
----------
1072
The table OE_ORDER_LINES_ALL alone has 340 columns. These are all standard Oracle EBS tables.
As I already said, "interesting" data model :-)
Never had to do anything with Oracle Applications, probably SAP will not be better from a data model point of view.
Patrick
what about this...
select u1.object_name, u1.object_type, u2.object_type, u2.object_type
from all_objects u1 full outer join all_objects u2 on
(u1.object_name=u2.object_name);
from all_objects u1 full outer join all_objects u2 on
*
ERROR at line 2:
ORA-00904: "XML_SCHEMA_NAME_PRESENT"."IS_SCHEMA_PRESENT": invalid identifier
well, to make sense the query condition should contain
and u1.owner='FOO' and u2.owner='BAR'
but the bug is the same... not found on metalink yet
Thanks for pointing out another ANSI join error Laurent. Too bad there is no corresponding FULL OUTER JOIN syntax in the "good old" Oracle join syntax, so we can compare. I wonder if Oracle DB 11g had these bugs fixed.
I'm happy to say that the query that was failing in Oracle 10.2.0.3.0 interim patch 12 works fine in Oracle 11.1.0.6.0.
I haven't found the bit of documentation that stated if the 1050 limit has been increased but I suspect this it what has changed.
I suspect also that the Oracle pseudo columns are included in the limit as the number of columns in the query that was failing was only just over 1000.
Thanks