안녕하세요
select *절 생략..
FROM NIC66B A,
NIC70B B,
NIC01B F,
NIC35B H,
NIC80B I
WHERE A.SSPS_DL_CRT_DT = B.SSPS_DL_CRT_DT(+)
AND A.DL_P_RNMCNO = B.DL_P_RNMCNO(+)
AND A.DL_P_RNMCNO = F.RNMCNO (+)
AND A.DL_P_RNMCNO = H.RNMCNO (+)
AND A.SSPS_DL_CRT_DT = I.SSPS_DL_CRT_DT(+)
AND A.SSPS_DL_ID = I.SSPS_DL_ID(+)
AND A.SSPS_DL_CRT_CCD NOT IN ( 'CTR', 'CAC') ) A
이러한 문장을 제가 생각했을때 아래와같이 변환
FROM NIC66B A
LEFT OUTER JOIN NIC70B B ON A.SSPS_DL_CRT_DT = B.SSPS_DL_CRT_DT
LEFT OUTER JOIN NIC70B B ON A.DL_P_RNMCNO = B.DL_P_RNMCNO
LEFT OUTER JOIN NIC01B F ON A.DL_P_RNMCNO = F.RNMCNO
LEFT OUTER JOIN NIC35B H ON A.DL_P_RNMCNO = H.RNMCNO
LEFT OUTER JOIN NIC80B I ON A.SSPS_DL_CRT_DT = I.SSPS_DL_CRT_DT
LEFT OUTER JOIN NIC80B I ON A.SSPS_DL_ID = I.SSPS_DL_ID
WHERE A.SSPS_DL_CRT_CCD NOT IN ( 'CTR', 'CAC') ) A;
했더니 오류내용 :' B.DL_P_RNMCNO"에 대한 참조가 앰비규어스합니다' 라는 메세지가떠서 아래와같이 다시 변환을해주었는데요
FROM NIC66B A
LEFT OUTER JOIN NIC70B B ON A.SSPS_DL_CRT_DT = B.SSPS_DL_CRT_DT AND A.DL_P_RNMCNO = B.DL_P_RNMCNO <- 위와다른점은 AND를 사용하여 변환
LEFT OUTER JOIN NIC01B F ON A.DL_P_RNMCNO = F.RNMCNO
LEFT OUTER JOIN NIC35B H ON A.DL_P_RNMCNO = H.RNMCNO
LEFT OUTER JOIN NIC80B I ON A.SSPS_DL_CRT_DT = I.SSPS_DL_CRT_DT AND A.SSPS_DL_ID = I.SSPS_DL_ID <- 위와다른점은 AND를 사용하여 변환
WHERE A.SSPS_DL_CRT_CCD NOT IN ( 'CTR', 'CAC') ) A;
궁금한게있는데요 3번째로 변환작업을하면 동일하게 데이터가 나오는데요 제가생각한 2번째 변환에서 저렇게 쓰면안되는건가요? 그리고 3번째제가 생각한데로 AND를 쓰면 LEFT OUER JOIN 이 AND절에도 동일하게 적용되는건가요?..
|