where (id, level) in
(select substr(id,1,29) id,
to_char(max(to_number(decode(level,'PassData','1','1R','2')))) level

-->

where (substr(id,1,29), level) in
(select substr(id,1,29) id,
to_char(max(to_number(decode(level,'PassData','1','1R','2')))) level

그래도 o row 인데요^^;

메인쿼리의 id값이랑 서브쿼리에서 가공한 id값의 길이가 같은가요?

저는 이것이 의문시 됩니다.

혹시 몰라서

메인 쿼리와, 서브쿼리의 길이를 id를 똑같은 조건으로 해보았습니다.

그래도 row=0

select substr(id,1,29), level,date
from test

where (substr(id,1,29), level) in
(select substr(id,1,29) ,
to_char(max(to_number(decode(level,'PassData','1','1R','2')))) level
from test

group by substr(id,1,29))
order by substr(id,1,29)

넵

켁 당연히 나올수가 없는거 아닌가요?

원 test 테이블엔 level 구분이

PassData 와 1R 밖에 없는데

decode 로 변경시킨건 1과 2잖아요..

그러니 in 조건에 맞는게 없을텐데... 쩝... 제가 잘못파악하고 있는건지..

select substr(id,1,29), level,date
from test

where (substr(id,1,29), level) in  <--- 이부분을
(select substr(id,1,29) ,
to_char(max(to_number(decode(level,'PassData','1','1R','2')))) level
from test

group by substr(id,1,29))
order by substr(id,1,29)

where (substr(id,1,29),to_char(to_number(decode(level,'PassData','1','1R','2')))) in 

요렇게 바꿔보시죠

level값의 최대값이 1R라면

select substr(id,1,29) as id,

          max(decode(level,'1R','2')) as level,

          date

 from test

where level = '1R'

group by substr(id,1,29)

이렇게 하면 되지 않나요?

아래와 같이 한번 해보시져...

select id,
       decode(level,'PassData','1','1R','2') as level,
       date
  from test
 where (id, decode(level,'PassData','1','1R','2')) in
       (select substr(id,1,29) as id,
               to_char(max(to_number(decode(level,'PassData','1','1R','2')))) as level
          from test
         group by substr(id,1,29))
 order by substr(id,1,29)