将 WM_CONCAT 转换为 Listagg

我的 DBA 正在将我的 oracle 数据库从 v10 升级到 v12。 我有一些旧的 SP，正在使用wm_concat我需要将其更改为listagg。 有问题的代码是这样的：

Select  registration_id,package_set_id,
        REPLACE(REPLACE(WM_CONCAT(REPLACE( (case when ROW_NUMBER() over (partition by product_id,product_detail_set_id,registration_id,product_family_id,application_id,package_Set_id,
               legal_status order by packset_country)=1 then legal_status else null end), ',' , '#')) OVER (PARTITION BY PRODUCT_ID,  PRODUCT_DETAIL_SET_ID,
               REGISTRATION_ID  ,PRODUCT_FAMILY_ID,APPLICATION_ID,PACKAGE_SET_ID   ORDER BY Packset_country  ), ',' , ' | '), '#', ',') as legal_status,

        (REPLACE(REPLACE(WM_CONCAT(REPLACE(ev_code, ',' , '#')) OVER (PARTITION BY PRODUCT_ID,  PRODUCT_DETAIL_SET_ID,
               REGISTRATION_ID  ,PRODUCT_FAMILY_ID,APPLICATION_ID,PACKAGE_SET_ID   ORDER BY ev_code  ), ',' , ' | '), '#', ',')) EV_CODES,

         min(marketed_date) over (PARTITION BY PRODUCT_ID,  PRODUCT_DETAIL_SET_ID,REGISTRATION_ID  ,PRODUCT_FAMILY_ID,APPLICATION_ID,PACKAGE_SET_ID) as marketed_date,

         (REPLACE(REPLACE(WM_CONCAT(REPLACE(Packset_country, ',' , '#')) OVER (PARTITION BY PRODUCT_ID,  PRODUCT_DETAIL_SET_ID, REGISTRATION_ID ,PRODUCT_FAMILY_ID,
                APPLICATION_ID,PACKAGE_SET_ID   ORDER BY Packset_country, reg_packset_country_id ), ',' , ' | '), '#', ',')) REGISTRATION_PACKSET_COUNTRIES,
         ROW_NUMBER() OVER (PARTITION BY PRODUCT_ID,  PRODUCT_DETAIL_SET_ID,REGISTRATION_ID  ,PRODUCT_FAMILY_ID,APPLICATION_ID,PACKAGE_SET_ID  
                ORDER BY Packset_country desc ,reg_packset_country_id)  ROW_NUM,     
         REPLACE(REPLACE(WM_CONCAT(REPLACE( (case when currently_marketed_in_country='Y' then packset_country end), ',' , '#')) OVER (PARTITION BY PRODUCT_ID,  PRODUCT_DETAIL_SET_ID,
                REGISTRATION_ID  ,PRODUCT_FAMILY_ID,APPLICATION_ID,PACKAGE_SET_ID  ORDER BY packset_country ,currently_marketed_in_country,reg_packset_country_id ), ',' , ' | '), '#', ',') as CURRENTLY_MARKETED_COUNTRIES
from radw_dwh.dw202_fact_reg_pack_countries

The expected result is:

我尝试更改它，但是当我尝试在“LISTAGG”旁边使用“ROW_NUMBER()”时出现问题。

我怎样才能解决这个问题？

的基本语法LISTAGG is:

LISTAGG(col_name_to_be_aggregated, ',') WITHIN GROUP (ORDER BY col)

在您的情况下，因为您有一个子查询作为结果集WM_CONCAT，你可以用相同的子查询代替col_name_to_be_aggregated in LISTAGG.

我想你也可以摆脱所有REPLACE函数，因为 LISTAGG 可以接受分隔符你的选择。

Try,

LISTAGG
(
  CASE
  WHEN ROW_NUMBER() OVER (PARTITION BY product_id,
                                       product_detail_set_id,
                                       registration_id,
                                       product_family_id,
                                       application_id,
                                       package_Set_id, 
                                       legal_status 
                                       order by packset_country)=1 THEN
    legal_status
  ELSE
    NULL
  END), ',') WITHIN GROUP (ORDER BY required_col)

另外，我想解释一下为什么在 12c 中需要迁移到 LISTAGG。由于 t 已从最新的 12c 版本中删除。因此，任何一直依赖WM_CONCAT函数的应用程序一旦升级到12c将无法工作。读为什么不在Oracle中使用WM_CONCAT函数？ http://lalitkumarb.com/2015/04/29/why-not-use-wm_concat-function-in-oracle/

对于 11g 之前的版本 2，您不能使用 LISTAGG。字符串聚合技术有很多，看看我的回答here https://stackoverflow.com/a/30272927/3989608.

有关更多详细信息Oracle 字符串聚合技术 http://lalitkumarb.com/2015/10/12/oracle-string-aggregation-techniques/