SELECT *
FROM (
(
SELECT rtrim (xmlagg (xmlelement (e, (select code_nm from SCM010 where code =lang_cd and code_div='M10') || ',')).extract ('//text()'), ',') enames
from(
SELECT(
SELECT LANG_CD FROM MTS310 WHERE DB_ID =A.STRING_ID
MINUS
SELECT LANG_CD FROM MTS630 WHERE TEXT_DB_ID =A.TEXT_DB_ID
) code_nm,A.TEXT_DB_ID FROM MTS_TEMP5 A
) WHERE TEXT_DB_ID=B.TEXT_DB_ID
)
FROM MTS_TEMP5 B
)
제가 listtag 안에 minus함수로 data를 제거후에 호출하고 싶은데 쿼리를 잘못 짠건지 에러가 나는데요
아무리 바도 구문이 잘못된건지 이런식으로는 불가능 한지 말씀좀 부탁드리겠습니다.
--* Scalar Query 사용 WITH MTS_TEMP5(STRING_ID, TEXT_DB_ID) AS ( SELECT 'MRP', 'MRP DB' FROM DUAL UNION ALL SELECT 'SCM', 'SCM DB' FROM DUAL UNION ALL SELECT 'CRM', 'CRM DB' FROM DUAL ) , MTS310(DB_ID, LANG_CD) AS ( SELECT 'MRP', 'ENG' FROM DUAL UNION ALL SELECT 'MRP', 'KOR' FROM DUAL UNION ALL SELECT 'MRP', 'CHN' FROM DUAL UNION ALL SELECT 'SCM', 'ENG' FROM DUAL UNION ALL SELECT 'SCM', 'KOR' FROM DUAL UNION ALL SELECT 'SCM', 'CHN' FROM DUAL UNION ALL SELECT 'CRM', 'ENG' FROM DUAL UNION ALL SELECT 'CRM', 'KOR' FROM DUAL UNION ALL SELECT 'CRM', 'CHN' FROM DUAL UNION ALL SELECT 'CRM', 'JPN' FROM DUAL ) , MTS630(TEXT_DB_ID, LANG_CD) AS ( SELECT 'MRP DB', 'CHN' FROM DUAL UNION ALL SELECT 'SCM DB', 'CHN' FROM DUAL UNION ALL SELECT 'CRM DB', 'CHN' FROM DUAL ) , SCM010(CODE_DIV, CODE, CODE_NM) AS ( SELECT 'M10', 'ENG', 'English' FROM DUAL UNION ALL SELECT 'M10', 'KOR', 'Korean' FROM DUAL UNION ALL SELECT 'M10', 'JPN', 'Japanese' FROM DUAL ) SELECT A.TEXT_DB_ID , ( -- LISTAGG 함수 사용 -- SELECT LISTAGG(SB.CODE_NM,',') WITHIN GROUP (ORDER BY SB.CODE_NM) -- XMLAGG 함수 사용 SELECT SUBSTR(XMLAGG(XMLELEMENT(e,',',SB.CODE_NM) ORDER BY SB.CODE_NM).Extract('//text()'),2) FROM MTS310 SA INNER JOIN SCM010 SB ON SB.CODE_DIV = 'M10' AND SB.CODE = SA.LANG_CD WHERE SA.DB_ID = A.STRING_ID AND NOT EXISTS (SELECT 'X' FROM MTS630 WHERE TEXT_DB_ID = A.TEXT_DB_ID AND LANG_CD = SA.LANG_CD) ) AS ENAMES FROM MTS_TEMP5 A ; --* Outer Join 사용 WITH MTS_TEMP5(STRING_ID, TEXT_DB_ID) AS ( SELECT 'MRP', 'MRP DB' FROM DUAL UNION ALL SELECT 'SCM', 'SCM DB' FROM DUAL UNION ALL SELECT 'CRM', 'CRM DB' FROM DUAL ) , MTS310(DB_ID, LANG_CD) AS ( SELECT 'MRP', 'ENG' FROM DUAL UNION ALL SELECT 'MRP', 'KOR' FROM DUAL UNION ALL SELECT 'MRP', 'CHN' FROM DUAL UNION ALL SELECT 'SCM', 'ENG' FROM DUAL UNION ALL SELECT 'SCM', 'KOR' FROM DUAL UNION ALL SELECT 'SCM', 'CHN' FROM DUAL UNION ALL SELECT 'CRM', 'ENG' FROM DUAL UNION ALL SELECT 'CRM', 'KOR' FROM DUAL UNION ALL SELECT 'CRM', 'CHN' FROM DUAL UNION ALL SELECT 'CRM', 'JPN' FROM DUAL ) , MTS630(TEXT_DB_ID, LANG_CD) AS ( SELECT 'MRP DB', 'CHN' FROM DUAL UNION ALL SELECT 'SCM DB', 'CHN' FROM DUAL UNION ALL SELECT 'CRM DB', 'CHN' FROM DUAL ) , SCM010(CODE_DIV, CODE, CODE_NM) AS ( SELECT 'M10', 'ENG', 'English' FROM DUAL UNION ALL SELECT 'M10', 'KOR', 'Korean' FROM DUAL UNION ALL SELECT 'M10', 'CHN', 'Chinese' FROM DUAL UNION ALL SELECT 'M10', 'JPN', 'Japanese' FROM DUAL ) SELECT A.TEXT_DB_ID -- LISTAGG 함수 사용 -- , LISTAGG(LB.CODE_NM,',') WITHIN GROUP (ORDER BY LB.CODE_NM) AS ENAMES -- XMLAGG 함수 사용 , SUBSTR(XMLAGG(XMLELEMENT(e,',',LB.CODE_NM) ORDER BY LB.CODE_NM).Extract('//text()'),2) AS ENAMES FROM MTS_TEMP5 A LEFT OUTER JOIN MTS310 LA ON LA.DB_ID = A.STRING_ID AND NOT EXISTS (SELECT 'X' FROM MTS630 WHERE TEXT_DB_ID = A.TEXT_DB_ID AND LANG_CD = LA.LANG_CD) LEFT OUTER JOIN SCM010 LB ON LB.CODE_DIV = 'M10' AND LB.CODE = LA.LANG_CD GROUP BY A.TEXT_DB_ID ;