 ora-00928 Please Help! [message #417015] |
Wed, 05 August 2009 13:38  |
codeMonkey
Messages: 10
Registered: August 2009
|
Junior Member |
|
|
I am supporting an application and one of its queries recently started giving ora-00928. There has been a recent update to the database and normally I would attribute my problems to the update, but this does not occur in our mirrored development environment. The query uses all tables and no views. If there is something wrong with this, I'm not seeing it. Please Help!
select * from animal
LEFT OUTER JOIN
(item_event JOIN scheduled_event
ON item_event.scheduled_event_id = scheduled_event.ID)
ON (animal.animal_id = item_event.animal_id);
ORA-00928: missing SELECT keyword |
|
|
|
| Re: ora-00928 Please Help! [message #417018 is a reply to message #417015] |
Wed, 05 August 2009 13:43  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
That query will definitely not work in development, since it is missing the select keyword in the nested query.
You
And then you
it with the nested query
(item_event JOIN scheduled_event
ON item_event.scheduled_event_id = scheduled_event.ID)
ON (animal.animal_id = item_event.animal_id)
And there is no select keyword in the nested query. The nested query by itself must return a result set.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: ora-00928 Please Help! [message #417035 is a reply to message #417031] |
Wed, 05 August 2009 14:48 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Just experimented a little, the syntax of the first query indeed seems to be OK in general.
Can you run the parts
and
select * from item_event
JOIN scheduled_event
ON item_event.scheduled_event_id = scheduled_event.ID
On their own on the production system?
|
|
|
|
| Re: ora-00928 Please Help! [message #417036 is a reply to message #417031] |
Wed, 05 August 2009 14:54 |
codeMonkey
Messages: 10
Registered: August 2009
|
Junior Member |
|
|
Your query resulted in "ORA-00904: "ITEM_EVENT"."ANIMAL_ID": invalid identifier" because the outer query doesn't know about the inner query's tables.
Tweaked it slightly and it runs:
select count(*) from animal
left outer join
(select * from item_event
join scheduled_event
on item_event.scheduled_event_id = scheduled_event.ID
) a
ON (animal.animal_id = a.animal_id)
|
|
|
|
| Re: ora-00928 Please Help! [message #417038 is a reply to message #417035] |
Wed, 05 August 2009 15:23 |
codeMonkey
Messages: 10
Registered: August 2009
|
Junior Member |
|
|
Both of your queries run as expected.
Assuming the query syntax is OK, what else could be causing this to fail on one machine and not the other? Might there be a patch that addresses it? Being as it's a production system I am hesitant to install patches with sound reasoning for it.
|
|
|
|
| Re: ora-00928 Please Help! [message #417039 is a reply to message #417038] |
Wed, 05 August 2009 15:29 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Well, it still IS on version 9.2.0.1.0.
The "1" releases of course usually have a number of bugs that are addressed in the later patch sets. The upgrade to 9.2.0.8.0 at least should be rather painless, if you can arrange for some downtime to do it.
What was that "recent upgrade" you mentioned? Was it an upgrade in the Oracle version, or was it an upgrade of the "application" and thus the table structure?
You could check all_objects/ _tables / etc... if there are any discrepancies between development and production.
|
|
|
|
| Re: ora-00928 Please Help! [message #417040 is a reply to message #417039] |
Wed, 05 August 2009 15:38 |
codeMonkey
Messages: 10
Registered: August 2009
|
Junior Member |
|
|
The update was for the application and table structure.
I can take a look at updating but I'm not posative it will help since I don't see the problem on another system with the same version.
|
|
|
|
|
|
| Re: ora-00928 Please Help! [message #417043 is a reply to message #417041] |
Wed, 05 August 2009 15:58 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
I did a little digging and it MIGHT be bug 768478
Can you try running
ALTER SESSION DISABLE QUERY REWRITE;
ALTER SESSION set Star_Transformation_Enabled = False;
and then retry the query on production?
|
|
|
|
| Re: ora-00928 Please Help! [message #417045 is a reply to message #417043] |
Wed, 05 August 2009 16:13 |
codeMonkey
Messages: 10
Registered: August 2009
|
Junior Member |
|
|
THOMASG IS MY NEW PERSONAL HERO!!!
The query does run with these commands. In fact, it works with just the Star_Transformation_Enabled line. What should my next step be. Is there a way to make this stick? What are the ramifications of turning off Star Transformation?
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
.
