title: Convert WM_CONCAT to Listagg
body: <p>My DBA is upgrading my oracle db from v10 to v12.
  I have some old SP's that uses <code>wm_concat</code> and I need to change it to <code>listagg</code>.
  The problematic code is this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The expected result is:<a href="https://i.stack.imgur.com/w6dIy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/w6dIy.png" alt="enter image description here"></a></p>
  
  <p>I tried to change it but there is a problem when I'm trying to use "ROW_NUMBER()" in side "LISTAGG".</p>
  
  <p>How can I fix this?</p>