안녕하세요....

티베로 db에서 오라클 쿼리로 변환 과정에서 서브쿼리를 이중으로 사용하지 못하는데, 

listagg에 단일 행 2개 이상이 자꾸 떠서...도움 요청드립니다....

<티베로 쿼리>

SELECT B.INFO_ID
     , B.CNTRY_CD
     , B.INFO_NM
     , CASE WHEN B.PROD_LENGTH > 2000 
            THEN SUBSTR( B.PROD_CL_CNTN, 1, 1000)
            ELSE B.PROD_CL_CNTN
             END AS PROD_LENGTH
     , B.UPDT_DT     
     , SUBSTR( B.FRST_RGDT, 0, 8) AS FRST_RGDT
 FROM (
		  SELECT A.INFO_ID        /* 정보_아이디 */
		       , A.CNTRY_CD       /* 국가코드 */
		       , A.INFO_NM        /* 정보_명 */
		       , A.PROD_CL_CNTN   /* 제품_분류_내용 */ 
		       , LENGTH ( A.PROD_CL_CNTN) AS PROD_LENGTH
		       , A.UPDT_DT
               , A.FRST_RGDT
		   FROM(
		   		SELECT A.INFM_ID                 AS INFO_ID
					 , NVL( A.NATN_CD, A.FTA_NATN_CD) AS CNTRY_CD
					 , NVL( A.INFM_NM, A.INFM_NM_EU)  AS INFO_NM   
					 , (
						SELECT  LISTAGG(TRIM( B.TXRT_CD), ',') WITHIN GROUP (ORDER BY TXRT_CD)  AS TXRT_CD
						  FROM (
								SELECT DISTINCT (TRIM (M.TXRT_CD)) AS TXRT_CD
								  FROM INFM_ITMLS_LIST I
							INNER JOIN INFM_MAPP_TXRT_CD M
									ON I.INFM_ID = M.INFM_ID
								   AND TRIM (I.TRGT_ITMLS_CD) = TRIM (M.TRGT_ITMLS_CD)
								 WHERE M.INFM_ID = A.INFM_ID
								   AND M.BZNS_CLCD = 'TB400'
								   AND M.USYN = 'Y'
								   AND I.MAPP_YN = 'Y'
								 ) B
						)  AS PROD_CL_CNTN 
					   , NVL( SUBSTR( FRST_RGDT, 1, 8), SUBSTR( LAST_MDDT, 1, 8)) AS UPDT_DT 	
                       , FRST_RGDT 
					FROM CNTL_IMPR_PRTC A 
				   WHERE A.USYN = 'Y'
					 AND A.INFM_STCD = '20'
		   ) A
 )B

<오라클 쿼리 작업중..>

     	 SELECT REGEXP_REPLACE( LISTAGG( A.PROD_CL_CNTN, ',') WITHIN GROUP (ORDER BY A.PROD_CL_CNTN), '([^,]+)(,\1)+', '\1')  AS HS_CD
     	   FROM(
			  SELECT A.IM_RQISIT_ID                    AS INFO_ID
				   , NVL( A.CNTRY_CD, A.FTA_CNTRY_CD)  AS CNTRY_CD
				   , NVL( A.INFO_NM, A.INFO_ORG_NM)    AS INFO_NM 
				   , (
			   	  		SELECT DISTINCT( TRIM (M.HS_CD)) AS HS_CD
						  FROM TB_IM_RQISIT_ITEM I
					INNER JOIN TB_IM_RQISIT_MAPP_TRGT M
							ON I.IM_RQISIT_ID = M.IM_RQISIT_ID
						   AND TRIM (I.TRGT_ENTRY_CD) = TRIM (M.TRGT_ENTRY_CD)
						 WHERE M.IM_RQISIT_ID = A.IM_RQISIT_ID
						   AND M.JOB_CL_CD = 'TB400'
						   AND M.USE_YN = 'Y'
						   AND I.MAPP_YN = 'Y'
				   	 ) AS PROD_CL_CNTN
                    , REGI_DT  
	 		     FROM TB_IM_RQISIT A 
		  	    WHERE A.USE_YN = 'Y'
			      AND A.INFO_STAT_CD = '20'
	 			) A

DISTINCT를 하고 listagg로 , 로 나열하는 과정에서 ORA-01427 뜹니다..헤헤.. ㅠㅠ