1. 공통코드 설정
2. 0을 채운 3자리 IP를 만들어 범위 체크
SELECT T.*
FROM (SELECT T.IP_NM
, REGEXP_REPLACE(REGEXP_REPLACE(T.IP_FR, '(\d+(\.|$))', '00\1'), '(\d{3}(\.|$))|.', '\1') AS IP_FR
, REGEXP_REPLACE(REGEXP_REPLACE(T.IP_TO, '(\d+(\.|$))', '00\1'), '(\d{3}(\.|$))|.', '\1') AS IP_TO
, REGEXP_REPLACE(REGEXP_REPLACE(T.IP_RM, '(\d+(\.|$))', '00\1'), '(\d{3}(\.|$))|.', '\1') AS IP_RM
FROM (SELECT T.DTL_CD_NM AS IP_NM
, MAX(DECODE(T.ATTR_CD, 'IP_FR', T.DTL_CD_ATTR_VAL)) AS IP_FR
, MAX(DECODE(T.ATTR_CD, 'IP_TO', T.DTL_CD_ATTR_VAL)) AS IP_TO
, '10.253.233.12' AS IP_RM
FROM (SELECT DL.SYS_ID
, DL.DTL_CD
, DL.DTL_CD_NM
, AD.ATTR_CD
, AD.DTL_CD_ATTR_VAL
FROM ESACDDT DT
JOIN ESACDDL DL ON DL.SYS_ID = DT.SYS_ID
AND DL.GRP_CD = DT.GRP_CD
AND DL.DTL_CD = DT.DTL_CD
AND DL.LANG_CD = 'ko_KR'
JOIN ESADTAT AD ON AD.SYS_ID = DL.SYS_ID
AND AD.GRP_CD = DL.GRP_CD
AND AD.DTL_CD = DL.DTL_CD
--AND AD.ATTR_CD IN ('IP_FR', 'IP_TO')
WHERE DT.SYS_ID = 'QA'
AND DT.GRP_CD = 'C006'
AND DT.USE_YN = 'Y') T
GROUP BY T.DTL_CD_NM) T
) T
WHERE T.IP_RM BETWEEN T.IP_FR AND T.IP_TO;
3. 중간집합
4. 결과집합
