안녕하세요. 오라클 쿼리를 풀다가 도저히 안되는부분이있어서 질문좀 드립니다 ㅜ 일단 기본조회 쿼리는 아래와같습니다. 코드테이블에서 코드를 가져와서 코드명과 이름을 매핑시켜줘서 뽑을려고하는 과정입니다. SELECT MODEL_CD, ORGAN_NM, SIX_CD, C.CODE_NM SIX_CD_NM, USE_CD, D.CODE_NM USE_CD_NM, EQ_STATUS_CD, E.CODE_NM EQ_STATUS_CD_NM, OPEN_YN FROM TB_DOMESTIC_EQ_BASE B LEFT JOIN TB_CODE C ON B.SIX_CD = C.CODE AND C.TYPE_CD = 'TC145' LEFT JOIN TB_CODE D ON B.USE_CD = D.CODE AND D.TYPE_CD = 'TC146' LEFT JOIN TB_CODE E ON B.EQ_STATUS_CD = E.CODE AND E.TYPE_CD = 'TC144'
MODEL_CD | ORGAN_NM | SIX_CD | SIX_CD_NM | USE_CD | USE_CD_NM | EQ_STATUS_CD | EQ_STATUS_CD_NM |
20121227000000000241 | (주)정관디스플레이 | 03,02 | null | 03 | 교육 | 03 | 품절 |
20121227000000001370 | (주)휴텍이일 | 04 | IT | 04 | 계측 | 04 | 기타 |
20121227000000006442 | 포벨 | 06 | ST | 03 | 교육 | 04 | 기타 |
20121227000000000171 | 삼성에스디아이(주) 소재부문 | 01,05 | null | 02 | 시험 | 04 | 기타 |
결과 목록은 위와같습니다.
그런데 저기 six_cd 부분에 데이터가 xx,xx,xx 이런식으로 복수로 들어가는 컬럼이있어서 저부분은 매핑이 되질않더라구요.
케이스문으로 이리저리해봐도 답이안나와서.. 고수님들의 조언좀 부탁드리겠습니다. 03,02 = ET,CT / 01,05 = BT,ST 로 데이터가 나오기를 원합니다 ㅜ
SELECT MODEL_CD, ORGAN_NM, SIX_CD, NVL(C.CODE_NM,(SELECT LISTAGG(A.CODE_NM,',') WITHIN GROUP(ORDER BY INSTR(','||B.SIX_CD||',',','||A.CODE||',')) FROM TB_CODE A WHERE INSTR(','||B.SIX_CD||',',','||A.CODE||',') > 0 AND A.TYPE_CD = 'TC145')) SIX_CD_NM, USE_CD, D.CODE_NM USE_CD_NM, EQ_STATUS_CD, E.CODE_NM EQ_STATUS_CD_NM, OPEN_YN FROM TB_DOMESTIC_EQ_BASE B LEFT JOIN TB_CODE C ON B.SIX_CD = C.CODE AND C.TYPE_CD = 'TC145' LEFT JOIN TB_CODE D ON B.USE_CD = D.CODE AND D.TYPE_CD = 'TC146' LEFT JOIN TB_CODE E ON B.EQ_STATUS_CD = E.CODE AND E.TYPE_CD = 'TC144'
WITH tb_domestic_eq_base(model_cd, organ_nm, six_cd, use_cd, eq_status_cd, open_yn) AS ( SELECT '20121227000000000241', '(주)정관디스플레이' , '03,02', '03', '03', 'Y' FROM dual UNION ALL SELECT '20121227000000001370', '(주)휴텍이일' , '04' , '04', '04', 'Y' FROM dual UNION ALL SELECT '20121227000000006442', '포벨' , '06' , '03', '04', 'Y' FROM dual UNION ALL SELECT '20121227000000000171', '삼성에스디아이(주) 소재부문', '01,05', '02', '04', 'Y' FROM dual ) , tb_code(type_cd, code, code_nm) AS ( SELECT 'TC145', '01', 'BT' FROM dual UNION ALL SELECT 'TC145', '02', 'CT' FROM dual UNION ALL SELECT 'TC145', '03', 'ET' FROM dual UNION ALL SELECT 'TC145', '04', 'IT' FROM dual UNION ALL SELECT 'TC145', '05', 'ST' FROM dual UNION ALL SELECT 'TC145', '06', 'ST' FROM dual UNION ALL SELECT 'TC146', '04', '계측' FROM dual UNION ALL SELECT 'TC146', '03', '교육' FROM dual UNION ALL SELECT 'TC146', '02', '시험' FROM dual UNION ALL SELECT 'TC144', '03', '품절' FROM dual UNION ALL SELECT 'TC144', '04', '기타' FROM dual ) SELECT model_cd , organ_nm , six_cd , LISTAGG(DECODE(c.type_cd, 'TC145', c.code_nm), ',') WITHIN GROUP(ORDER BY INSTR(b.six_cd, c.code)) six_cd_nm , use_cd , MIN(DECODE(c.type_cd, 'TC146', c.code_nm)) use_cd_nm , eq_status_cd , MIN(DECODE(c.type_cd, 'TC144', c.code_nm)) eq_status_cd_nm , open_yn FROM tb_domestic_eq_base b LEFT JOIN tb_code c ON ( (c.type_cd = 'TC145' AND INSTR(b.six_cd, c.code) > 0) OR (c.type_cd = 'TC146' AND b.use_cd = c.code) OR (c.type_cd = 'TC144' AND b.eq_status_cd = c.code) ) GROUP BY model_cd, organ_nm, six_cd, use_cd, eq_status_cd, open_yn ;