데이터베이스 질문과 답변

tibero db 쿼리를 오라클로 변환 0 2 3,306

by 통쓰 [SQL Query] [2023.10.04 09:40:25]

안녕하세요....

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

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

<티베로 쿼리>

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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 뜹니다..헤헤.. ㅠㅠ

 

by 마농 [2023.10.04 10:11:08]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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
     , SUBSTRB(b.prod_cl_cntn, 1, 2000)            AS prod_length
     , SUBSTR(NVL(a.frst_rgdt, a.last_mddt), 1, 8) AS updt_dt
     , SUBSTR(a.frst_rgdt, 1, 8)                   AS frst_rgdt
  FROM cntl_impr_prtc a
  LEFT OUTER JOIN
       (SELECT infm_id
             , LISTAGG(txrt_cd, ',') WITHIN GROUP(ORDER BY txrt_cd) prod_cl_cntn
          FROM (SELECT DISTINCT m.infm_id
                     , TRIM(m.txrt_cd) 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.bzns_clcd = 'TB400'
                   AND m.usyn      = 'Y'
                   AND i.mapp_yn   = 'Y'
                )
         GROUP BY infm_id
        ) b
    ON a.infm_id = b.infm_id
 WHERE a.usyn      = 'Y'
   AND a.infm_stcd = '20'
;

 

by 통쓰 [2023.10.04 10:36:24]

정말 감사합니다!!!ㅜㅜㅜ

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입