ORA-600 when I execute a SELECT statment :-( 1

[MattWoberts]

Hi,

I have a view called "MyView" and a table "MyTable". The table contains a history of versions for each row returned from MyView, and I need to join on the most recent version, so I have this SQL:

Select distinct MyView.*, MyTable.Version_NO
from MyView left outer join MyTable on MyView.Procedure_ID = MyTable.Procedure_ID and MyView.AppUser_ID = MyTable.AppUser_ID and MyTable.VersionNo = 
(Select max(VersionNo) from MyTable where Procedure_ID = MyView.ID and AppUser_ID = MyTable.AppUser_ID)

But, when I run it, I get this horrible error:
“[Oracle][ODBC][Ora]ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [], []”

Does anyone know what this means or what I might have done?

Thanks in advance!!!

 

[MattWoberts]

More information:

My view is actually 4 SQL statements UNION'd together. If I remove 3 of the SQL statements so there is no UNION, this solves the problem. So it seeems to be the fact that my view is using UNIONS that upsets Oracle - hmmmmm....

 

[hoinz]

Could be an Oracle bug;
search Metalink,
depending on your OS and Oracle version there may be a patch or a workaround.

 

[lewisp]

Can you select directly from the view? If not, can you run the views' SQL statement standalone?

 

[MattWoberts]

Yes I can select from the view directly, its only when I join the view onto the table with that specific SQL that I get the error. Also, if I remove the sub-query part of the SQL above, that also removes the error.

I'm on Oracle 9i Rel2 btw, running on Windows 2000. I would have a look on metalink, but we don't have a support contract anymore so I can't get on

Thanks

 

[lewisp]

Try changing your sub-query to this:

[/code](Select max(z.VersionNo) from MyTable z where z.Procedure_ID = MyView.ID and z.AppUser_ID = MyTable.AppUser_ID)[/code]

 

[MattWoberts]

Thanks for the suggestion, but alas it has the same result. I'm toying with just rewriting this query to amke it run, but I can't think of any other way to do this without the use of the sub-select.....

 

[MattWoberts]

On further investigation it seems like the query is unhappy because I am combining a "LEFT JOIN" with a sub query that references the parent query. If I remove the parts of the where clause in my sub query that reference the parent table, it works. If I change the "LEFT JOIN" to a "JOIN", it also works. Maybe this is some sort of Oracle limitation?

By the way I am on 9iRel2, so apologies for posting this in "Oracle 8 and 8i".

 

[lewisp]

Try it without distinct. This could be related to Oracle bug #3557906.

 

[MattWoberts]

I finally got round this by "hacking" the query: I removed the "left join", and converted to a "join", and then UNION'd the whole query with the records that are not in the joined table... Nasty!

Thanks for everyones response!