Oracle SQL IN-Clause throws Error with Subselect

Question

so here's my Problem: I got this SQL-Statement

SELECT a.ID,a.CONTENTOF 
FROM MLMDATA.PV_STORAGE a
WHERE 1=1  
AND ID IN (113312,114583,114581,113472,114585,114580,113314)
AND a.SEQ = (SELECT MAX(b.SEQ) FROM MLMDATA.PV_STORAGE b where a.ID = b.ID)

But my Problem is now, that I'm getting an Error:

ORA-00600: Internal Error Code, Arguments: [qernsRowP], [1], [], [], [], [], [], [], [], [], [], []

I can't seem to find a way to select these ID's where only the highest SEQUENZE is selected.... I already created a View, showing only the highest SEQ, but that doesn't work eighter... I'm kinda frustrated, because as far as I know that SQL Worked before and suddenly doesn't work.

Anyone got an Idea on what the Problem could be?

Can you please provide some sample data and expected output? — Md. Suman Kabir, Oct 17 '17 at 10:14
It seems you have some real problem there. Can you please try replace the IN clause with just filering one ID? ex: and ID = 113312 — Renato Afonso, Oct 17 '17 at 14:16
@RenatoAfonso I can use my above SQL without any problems if there is only one ID in the brackets e.g. "AND ID IN (113312)" — Sunrunner, Oct 18 '17 at 08:49
And if you add just another value in the IN clause, you get that error? You might need to check with your DBA if you have some corrupted data files. — Renato Afonso, Oct 18 '17 at 09:31
@RenatoAfonso yes, if I just add another value to the IN clause, the statement breaks down. — Sunrunner, Oct 20 '17 at 07:16

Md. Suman Kabir · Accepted Answer · 2017-10-17T10:28:43.683

0

Please try this query :

SELECT a.ID,a.CONTENTOF FROM MLMDATA.PV_STORAGE a
INNER JOIN
    (SELECT ID, CONTENTOF, MAX(SEQ) AS SEQ FROM MLMDATA.PV_STORAGE 
    where ID IN (113312,114583,114581,113472,114585,114580,113314)
    GROUP BY ID, CONTENTOF) b 
ON a.id = b.id AND a.SEQ = b.SEQ

edited Oct 17 '17 at 10:28

answered Oct 17 '17 at 09:59

Md. Suman Kabir

5,243
5
25
43

This is just a shortened version of my Query... I tried it and I'm getting the same error message as before. :-( – Sunrunner Oct 17 '17 at 10:09
@Sunrunner - I forgot to add the group by clause, can you please check the updated query ? – Md. Suman Kabir Oct 17 '17 at 10:15
@Md.SumanKabir Have you tried the query you posted. I would say you try the stuff and then post it. How does adding group by is going to resolve the issue. – XING Oct 17 '17 at 10:22
@XING his query works great for my cause! Thank you so much, I would have never used the INNER JOIN instead of two where clauses. – Sunrunner Oct 17 '17 at 10:41
1

@Sunrunner my comment was on his previous version of answer. If the latest version works then inplace of saying thanks you can accept and mark this anwer as accpted. Read this https://stackoverflow.com/help/someone-answers – XING Oct 17 '17 at 11:28
@XING, I though so, didn't mean to be mean there :-) Md.SumanKabir Thank you so much, I've marked your answer as the right one :-) – Sunrunner Oct 17 '17 at 13:47

score 0 · Answer 2 · answered Oct 17 '17 at 10:19

Try a window function:

select id, contentof
from (
  select id, contentof, 
         row_number() over (partition by id order by seq desc) as rn
  FROM MLMDATA.PV_STORAGE a
  where id IN (113312,114583,114581,113472,114585,114580,113314)
) 
where rn = 1;

Oracle SQL IN-Clause throws Error with Subselect

2 Answers2