oracle 서브쿼리 rownum 조회시 ORA-00904 invalid identifier 원인파악

oracle DB를 사용하여 중이며
추가적으로 데이터를 조회해야 하는 작업이었다.

문제가 발생했던 쿼리를 임의의 테이블로 재구성 해보았고 아래와 같다.

 

SELECT 
    (SELECT Z.사용자명
     FROM (SELECT U.사용자명
           FROM 사용자_테이블  U
           WHERE U.사용자_소속 = P.상품_관리자_소속
           order by U.로그인_일자 DESC
           ) Z
    WHERE Z.ROWNUM = 1) 가장 최근에 접속한 사용자
FROM 상품_테이블 P

쿼리를 간략하게 설명하자면
상품을 관리하는 사용자의 소속에서 가장 최근에 로그인한 한 명을 추출하는 쿼리이다

해당 쿼리를 실행하였을 때 표출되는 메시지는 아래와 같다.

ORA-00904: "P"."상품_관리자_소속": invalid identifier

메시지를 그대로 읽으면 "P"."상품_관리자_소속"이 부적절한 식별자라는 것인데
오타가 없으며 테이블 스키마에도 정상적으로 생성되어있는 컬럼인것을 확인하였다.

확인 결과 해당 쿼리는 
제목에는 서브쿼리라 되어 있지만 정확하게는
스칼라 서브쿼리 안쪽에 인라인 뷰 내부에서
가장 최상단에 있는 P 테이블의 컬럼을 호출하였기 때문이었다.

상세하게 설명하면 인라인뷰의 동작 방식의 특징으로
인라인 뷰는 상관관계 서브 쿼리가 아니기 때문에 독립적으로만 사용이 가능하여
스칼라 서브쿼리 안쪽에 인라인 뷰에서는 메인 쿼리의 컬럼을 가져올 수가 없던 것이다.

조치할 수 있는 방법으로는
- 오라클의 WITH 함수
- 상위 n번째 값을 구하는 다른 방식으로 ORDER BY로 인한 인라인뷰 생성 방지
가 있을 것으로 보인다.

후자를 선택하여 조치를 진행하였다.
ROWNUM을 구하기위해 생성된 인라인 뷰 영역을
오라클에서 지원하는 KEEP (DENSE_RANK...) 함수 사용

 

SELECT 
    (SELECT MAX(U.사용자명) KEEP (DENSE_RANK FIRST ORDER BY U.로그인_일자 DESC NULLS LAST)
           FROM 사용자_테이블  U
           WHERE U.사용자_소속 = P.상품_관리자_소속
           ) 가장 최근에 접속한 사용자
FROM 상품_테이블 P