데이터베이스 질문과 답변
by 백태클 [2016.08.26 14:01:10]
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
;
) 버튼을 클릭하여 작성 하시면 됩니다.