|
방법 1
SELECT XMLAGG(XMLELEMENT(a,ctrt_id,',')) CTRT_ID-- ORDER BY CTRT_ID)
FROM (SELECT TC.CTRT_ID AS CTRT_ID
FROM TBB_RSS_CT TRC
,TBB_TGT_P TT
,TBB_CTRT_P TC
WHERE TRC.RSS_SEQ_NO IN ('1000045669')
AND TT.BILL_SEQ_NO = TRC.BILL_SEQ_NO
AND TT.BILL_YYMM = TRC.BILL_YYMM
AND TT.BILL_SEQ_NO = TC.BILL_SEQ_NO
AND TT.BILL_YYMM = TC.BILL_YYMM
)
위의 쿼리에서 , 이값을 없애주기 위해서
SELECT replace(ctrt_id, ',', ''''||',')
FROM
(
SELECT replace(ctrt_id, '', '''') CTRT_ID
FROM
(
SELECT XMLAGG(XMLELEMENT(a,ctrt_id,',')) CTRT_ID-- ORDER BY CTRT_ID)
FROM (SELECT TC.CTRT_ID AS CTRT_ID
FROM TBB_RSS_CT TRC
,TBB_TGT_P TT
,TBB_CTRT_P TC
WHERE TRC.RSS_SEQ_NO IN ('1000045669')
AND TT.BILL_SEQ_NO = TRC.BILL_SEQ_NO
AND TT.BILL_YYMM = TRC.BILL_YYMM
AND TT.BILL_SEQ_NO = TC.BILL_SEQ_NO
AND TT.BILL_YYMM = TC.BILL_YYMM
AND ROWNUM < 200)
)
)
이런식으로 했더니 replace가 최고 길이가 varchar2 4000바이트인지 ORA-19011: 문자열 버퍼가 너무 작음
이렇게 뜨는데요 어떻게 방법이 없을까요?
방법 2
SELECT MAX(SYS_CONNECT_BY_PATH(CTRT_ID, ','))
FROM (
SELECT ROWNUM CNT,
CTRT_ID
FROM
(
SELECT --TT.BILL_YYMM AS BILL_YYMM,
-- TT.BILL_SEQ_NO AS BILL_SEQ_NO,
TC.CTRT_ID AS CTRT_ID
FROM TBB_RSS_CT TRC
,TBB_TGT_P TT
,TBB_CTRT_P TC
WHERE TRC.RSS_SEQ_NO IN ('1000045669')
AND TT.BILL_SEQ_NO = TRC.BILL_SEQ_NO
AND TT.BILL_YYMM = TRC.BILL_YYMM
AND TT.BILL_SEQ_NO = TC.BILL_SEQ_NO
AND TT.BILL_YYMM = TC.BILL_YYMM
ORDER BY CTRT_ID
)
) X
START WITH CNT = 1
CONNECT BY PRIOR CNT = CNT - 1
AND PRIOR CNT = CNT-1
이 방법으로 했을때도 ORA-01489: 문자열 연결의 결과가 너무 깁니다
이러한 오류가 뜹니다..
해결방법이 없을까요 ㅠㅠ
제가 초보라서 그런데 많은 도움 부탁드리겠습니다.
|
