데이터베이스 질문과 답변
by 제로나인 [SQL Query] ORA-00932 [2015.01.22 09:36:46]
WITH T AS
(
SELECT COMP_NAME NAME, WM_CONCAT(UINDEX) S_POS, WM_CONCAT(U_UNIT_CNT) U_SIZE, ROWCOUNT U_LIMIT FROM(
SELECT PLACE.UINDEX, PLACE.U_UNIT_CNT, B.COMP_NAME, B.ROWCOUNT FROM
(SELECT UINDEX, U_UNIT_CNT, PARENT_COMP_ID FROM IBS_INFO_PLACE WHERE UINDEX IS NOT NULL AND U_UNIT_CNT IS NOT NULL) PLACE,
(SELECT CM.COMP_ID, CM.COMP_NAME, CM.ASSET_ID, SP.ROWCOUNT FROM IBS_COMPONENT_MASTER CM,
(SELECT ASSET_ID FROM IBS_ASSET_MASTER WHERE SKT_CODE = '559' AND MAPPING_YN = 'Y') AM,
(SELECT ROWCOUNT, COMP_ID FROM IBS_INFO_SPACE WHERE ROWCOUNT IS NOT NULL AND ROWCOUNT < 50) SP,
TB_RACK TR,
IBS_MODEL_MASTER MM,
IBS_CODE CODE WHERE AM.ASSET_ID = CM.ASSET_ID
AND CM.LOCATION_ID IS NOT NULL AND TR.RACK_ID = AM.ASSET_ID AND CM.MODEL_ID = MM.MODEL_ID AND CM.COMP_ID = SP.COMP_ID
AND CODE.CODE_ID = MM.TYPE_ID AND CODE.CODE_ID = 'CD00031100') B
WHERE PLACE.PARENT_COMP_ID = B.COMP_ID
ORDER BY PLACE.UINDEX) EX
GROUP BY COMP_NAME,ROWCOUNT
)SELECT NAME, U_LIMIT, REGEXP_REPLACE(REPLACE((REGEXP_REPLACE(ALLIST,''''||REPLACE(S_POS,',','''|''')||'''','')),'''',''),',+',',') RESULT
FROM
(
SELECT A.NAME, B.POS S_POS, U_SIZE, U_LIMIT, (SELECT LISTAGG(''''||TO_CHAR(LEVEL)||'''',',') WITHIN GROUP(ORDER BY LEVEL) FROM DUAL CONNECT BY LEVEL <= U_LIMIT) ALLIST
FROM T A, (SELECT NAME, LISTAGG ((SELECT LISTAGG(TO_CHAR(POS+LEVEL-1),',') WITHIN GROUP(ORDER BY LEVEL) FROM DUAL CONNECT BY LEVEL <= LEN),',') WITHIN GROUP(ORDER BY POS) POS FROM
(SELECT NAME, LV, REGEXP_SUBSTR(S_POS,'[^,]+',1,LV) POS , REGEXP_SUBSTR(U_SIZE,'[^,]+',1,LV)LEN FROM T, (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 1) X)
WHERE POS + LEN > 0
GROUP BY NAME) B
WHERE A.NAME = B.NAME
);
이런 쿼리가 있는데요
쿼리를 개발DB에서 돌릴땐 아무 문제가 없었는데
운영DB에 올리고나니 아래와 같은 에러가 발생합니다.
뭐가 문제인지를 도저히 모르겠어서 도움좀 부탁드립니다..ㅜㅜ
문제가 되는 21행은 LISTAGG ((SELECT LISTAGG(TO_CHAR(POS+LEVEL-1),',') WITHIN GROUP(ORDER BY LEVEL) FROM DUAL CONNECT BY LEVEL <= LEN),',') WITHIN GROUP(ORDER BY POS) POS
이부분입니다..
CLOB 데이터타입을 쓰는 컬럼은 한군대도 없습니다.
ORA-00932: 일관성 없는 데이터 유형: NUMBER이(가) 필요하지만 CLOB임
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
21행, 61열에서 오류 발생
자 답입니다.
오류 나는 부분부터 하위에 있는 POS와 LEN을 TO_NUMBER로 변환하니깐 문제없이 잘되네요..
해결은 했지만..
개발DB와 운영DB 모두 테이블의 데이터 타입도 동일하고 같은 쿼리인데
안됐던 이유가 뭔지는 모르겠네요.. 혹시 아시는분 계시면 가르침좀 부탁드립니다..(_ _ ) 꾸벅;
에러메시지 그대로겠지요? 해당 항목이 CLOB 인거죠.
wm_concat 이 공식적인 함수는 아닌데요.
대부분 버전에서 VARCHAR2 로 동작하는데(4000Byte 이상 에러 발생)
일부 버전에서는 CLOB 이 지원되는 듯 하네요.(CLOB 사용시 4000Byte 이상 가능)
에러는 잡으셨지만...
쿼리가 너무 복잡하네요.
불필요한 테이블이 조인된건 아닌지 모르겠네요?
전혀 사용되지 않는 테이블 조인이 너무 많네요.
답변 감사합니다.
쿼리에서 불필요한 테이블의 조인이 어떤부분인가요?..
모두 필요하다고 생각되어 넣었는데.. 제가 잘못 작성한 부분이 많은가보네요 ㅠ;;
도움을 주신다면 참고하여 고쳐보도록하겠습니다!.
1. 여러행의 자료를
2. 하나로 합쳤다가
3. 이걸 다시 여러개로 분리해서
4. 가공하고
5. 이걸 다시 하나로 합치는
매우 비효율적인 작업을 하고 있네요.
2,3 의 과정 없이 바로 1 에서 4로 넘어갈 수 있으리라 생각되네요.
조건절이나, 조회절에서 전혀 사용되지 않는 테이블이 있구요.
조인절로만 사용이 된 경우입니다.
예를 들면 다음과 같은 경우입니다.
SELECT e.empno, e.deptno, e.ename
FROM emp e
, dept d
WHERE e.deptno = d.deptno
;
사원과 부서를 조인하는데 부서는 조인조건으로만 사용되고 아무데서도 사용을 안합니다.
이 쿼리가 맞는지 여부는 쿼리만 봐서는 모릅니다.
두 테이블의 관계와 실제 데이터가 어떻게 들어잇는지에 따라 다르죠.
1. 사원에 부서코드가 정확하게 다 들어 있다면?
- 부서정보는 불필요하게 조인된 것입니다.
2. 사원에 부서코드가 정확하게 다 들어 있지 않아 정확한 검증용으로 조인했을 경우에만 이미가 있는거죠.
또 다른 확인 방법은 조인을 했을 때와 안했을 때 결과 차이입니다.
결과가 똑같다면 쓸데 없는 조인입니다.
감사합니다 마농님!!
알려주신 사항들 확인해서 쿼리 재작성 해보도록 하겠습니다. ^^
맛점 되세요~!! 많이 배우고갑니닷!
) 버튼을 클릭하여 작성 하시면 됩니다.