将WM_CONCAT转换为Listagg [英] Convert WM_CONCAT to Listagg

问题描述

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

My DBA is upgrading my oracle db from v10 to v12. I have some old SP's that uses wm_concat and I need to change it to listagg. The problematic code is this:

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

预期结果是:

我尝试更改它，但是当我尝试在"LISTAGG"侧使用"ROW_NUMBER()"时出现问题.

I tried to change it but there is a problem when I'm trying to use "ROW_NUMBER()" in side "LISTAGG".

我该如何解决?

推荐答案

LISTAGG 的基本语法是:

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

在您的情况下，由于您将子查询的结果集设置为WM_CONCAT，因此可以在 LISTAGG 中将相同的子查询替换为col_name_to_be_aggregated.

In your case, since you have a sub-query as result set to WM_CONCAT, you could put the same sub-query in place of col_name_to_be_aggregated in LISTAGG.

我认为您也可以摆脱所有 REPLACE 函数，因为LISTAGG可以接受您选择的定界符.

I think you can also get rid of all the REPLACE functions, since, LISTAGG can accept the delimiter of your choice.

尝试

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，将无法正常工作.阅读 为什么不使用WM_CONCAT函数在Oracle中?

---

Also, I would like to explain why you need to move to LISTAGG in 12c. Since t has been removed from the latest 12c version. Therefore, any application which has had been relying on WM_CONCAT function will not work once upgraded to 12c. Read Why not use WM_CONCAT function in Oracle?

对于11g之前的版本2，您不能使用LISTAGG.有很多字符串聚合技术，请在 此处 中查看我的答案.

For pre-11g Release 2, you can't use LISTAGG. There are many string aggregation techniques, have a look at my answer here.

有关> Oracle字符串聚合技术的详细信息 strong>

这篇关于将WM_CONCAT转换为Listagg的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！