SELECT REPLACE(JUMIN_NUM, '-', '') FROM WORK WHERE WORK_CNT = 1
SELECT FLOOR(MONTHS_BETWEEN('20161231', TO_DATE(birth_ymd,'yyyymmdd'))/12) FROM ( SELECT '8803051111111', CASE WHEN SUBSTR('8803051111111',7,1) IN ('1','2','5','6') THEN '19' WHEN SUBSTR('8803051111111',7,1) IN ('3','4','7','8') THEN '20' WHEN SUBSTR('8803051111111',7,1) IN ('9','0') THEN '18' END || SUBSTR('8803051111111',1,6) birth_ymd FROM dual )
처음 쿼리문에서 구한 주민번호 리스트를 2번쨰 쿼리문 880305111111에 넣어서
만 나이를 구하고 싶은데.. 두 쿼리문을 합치려고 하는데 조언좀 부탁드리겠습니다.
SELECT JUMIN_NUM , FLOOR(MONTHS_BETWEEN('20161231', TO_DATE(BIRTH_YMD,'yyyymmdd'))/12) FROM ( SELECT JUMIN_NUM, CASE WHEN SUBSTR(JUMIN_NUM,7,1) IN ('1','2','5','6') THEN '19' WHEN SUBSTR(JUMIN_NUM,7,1) IN ('3','4','7','8') THEN '20' WHEN SUBSTR(JUMIN_NUM,7,1) IN ('9','0') THEN '18' END || SUBSTR(JUMIN_NUM,1,6) BIRTH_YMD FROM (SELECT REPLACE(JUMIN_NUM, '-', '') JUMIN_NUM FROM WORK WHERE WORK_CNT = 1) )
SELECT * FROM (SELECT JUMIN_NUM, CASE WHEN SUBSTR(JUMIN_NUM, 7, 1) IN ('1', '2', '5', '6') THEN '19' WHEN SUBSTR(JUMIN_NUM, 7, 1) IN ('3', '4', '7', '8') THEN '20' WHEN SUBSTR(JUMIN_NUM, 7, 1) IN ('9', '0') THEN '18' END || SUBSTR(JUMIN_NUM, 1, 6) BIRTH_YMD FROM (SELECT REPLACE(JUMIN_NUM, '-', '') JUMIN_NUM FROM WORK WHERE WORK_CNT = 1)) A , XMLTABLE ('if (. castable as xs:date) then () else xs:string(.)' PASSING A.BIRTH_YMD) B;