Основной синтаксис LISTAGG:

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

В вашем случае, поскольку у вас есть подзапрос в качестве результата, установленного на WM_CONCAT, вы можете поместить тот же подзапрос вместо col_name_to_be_aggregated в LISTAGG.

Я думаю, вы также можете избавиться от всех функций REPLACE, поскольку LISTAGG может принимать разделитель по вашему выбору.

Пытаться,

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)

Кроме того, я хотел бы объяснить, почему вам нужно перейти в LISTAGG в 12c. Поскольку t был удален из последней версии 12c. Следовательно, любое приложение, которое полагалось на функцию WM_CONCAT, не будет работать после обновления до версии 12c. Прочитайте Почему бы не использовать функцию WM_CONCAT в Oracle?

Для Pre-11g Release 2 вы не можете использовать LISTAGG. Существует много методов агрегации строк, посмотрите мой ответ здесь.

Подробнее о методах агрегирования строк Oracle< /а>

Например, в коде интерфейса odciaggregate в коде:

    create or replace type string_agg_type as object ( total varchar2(4000), 
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type ) return number,
 member function ODCIAggregateIterate(self IN OUT string_agg_type , value IN varchar2 ) return number, 
member function ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT varchar2, flags IN number) return number,
 member function ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type) return number ); 
/ 
create or replace type body string_agg_type is static function odciaggregateinitialize(sctx IN OUT string_agg_type) return number is begin sctx := string_agg_type(null); return odciconst.success; end; 
member function odciaggregateiterate(self IN OUT string_agg_type, value IN varchar2) return number is begin self.total := self.total || ',' || value; return odciconst.success; end; 
member function odciaggregateterminate(self IN string_agg_type, returnvalue OUT varchar2, flags IN number) return number is begin returnvalue := ltrim(self.total, ','); return odciconst.success; end; 
member function odciaggregatemerge(self IN OUT string_agg_type, ctx2 IN string_agg_type) return number is begin self.total := self.total || ctx2.total; return odciconst.success; end; 
end;
/ 
CREATE or replace FUNCTION stragg(input varchar2) RETURN varchar2 PARALLEL_ENABLE AGGREGATE USING string_agg_type; 
/


with t as ( select 'a1' val from dual union all select 'b2' val from dual ) select stragg(val) as val from t; val --------------------------- a1,b2

Мотивация для следующего подхода

У нас было несколько проблем с wm_concat (известные ошибки) или wm_concat-в-3_-преобразование-для-использования-12c.

(Не говоря уже о хорошем компактном/декларативном использовании с использованием wm_concat во многих сценариях, где другие решения могут быть довольно сложными для чтения.)

(Иногда приходится использовать xmlagg из-за > 4000 chars/clob проблем или regexp_replace обходных путей для эмуляции wm_concat( distinct ... )), например. как здесь.

Стратегия

Итак, наконец, на мой взгляд, довольно хорошая стратегия (которая может отличаться в зависимости от вашей среды/потребностей/стратегии) ​​состоит в том, чтобы

1. create two functions
   • create function wm_concat_32767(... (working on varchar(32767) which is possible since Oracle 12c) and
     • depending on MAX_STRING_SIZE of your db you may want to adopt this to "4000" or others
   • create function wm_concat_clob(... сначала в вашей базе данных в системной схеме.
   • Они должны быть основаны на коде, подобном предоставленному ответом Dart XKey (возможно, скопированным из < a href="https://web.archive.org/web/20150906040401/https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402" отн. ="nofollow noreferrer">asktom/Tom Kyte)
2. create public synonym wm_concat for sys.wm_concat_32767
   • (I would generally let the wm_concat point to the likely faster wm_concat_32767 rather than wm_concat_clob)
   • это позволяет легко повторно использовать/мигрировать существующий код/использование ‹ 11,2g на основе wm_concat(varchar(4000)).
   • можно было бы уже знать имя функции и, таким образом, легко привыкнуть к ней.
   • (проблема, заключающаяся в том, что онлайн-документация об этом может вводить в заблуждение, - это то, с чем я мог бы жить, поскольку преимущества для меня в целом перевесили бы недостатки)
   • it will even allow the use of wm_concat( distinct ... ) queries with compact declarative syntax
     • as opposed to the listagg/xmlagg/regexp_replace-based workarounds mentioned above
3. create public synonym wm_concat_clob for sys.wm_concat_clob
4. предоставить надлежащие права публичного исполнения для wm_concat_*функций

Код

Итак, наконец, принятый код, который мы используем (возможно, со временем я обновлю все упомянутое выше):

wm_concat_32767(...) создание:

• в зависимости от MAX_STRING_SIZE вашей базы данных вы можете принять это значение "4000" или другие значения

_

create or replace type  string_agg_type  as object ( 
  total varchar2(32767), 
  static function ODCIAggregateInitialize(sctx IN OUT string_agg_type ) return number,
  member function ODCIAggregateIterate(self IN OUT string_agg_type , value IN varchar2 ) return number, 
  member function ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT varchar2, flags IN number) return number,
  member function ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type) return number 
); 
/ 
create or replace type body  string_agg_type  is static function odciaggregateinitialize(sctx IN OUT string_agg_type) return number is begin sctx := string_agg_type(null); return odciconst.success; end; 
  member function odciaggregateiterate(self IN OUT string_agg_type, value IN varchar2) return number is begin self.total := self.total || ',' || value; return odciconst.success; end; 
  member function odciaggregateterminate(self IN string_agg_type, returnvalue OUT varchar2, flags IN number) return number is begin returnvalue := ltrim(self.total, ','); return odciconst.success; end; 
  member function odciaggregatemerge(self IN OUT string_agg_type, ctx2 IN string_agg_type) return number is begin self.total := self.total || ctx2.total; return odciconst.success; end; 
end;
/ 
CREATE or replace FUNCTION  wm_concat_32767(input varchar2) RETURN varchar2 PARALLEL_ENABLE AGGREGATE USING string_agg_type; 
/

wm_concat_clob(...) создание на основе этого кода от Мишеля Кадо:

create or replace type  stragg_type4  as object (
  result CLOB,
  static function ODCIAggregateInitialize (sctx IN OUT stragg_type4) return number,
  member function ODCIAggregateIterate (self IN OUT stragg_type4, value IN varchar2) return number,
  member function ODCIAggregateTerminate (self IN stragg_type4, returnValue OUT CLOB, flags IN number) return number,
  member function ODCIAggregateMerge (self IN OUT stragg_type4, ctx2 IN stragg_type4) return number
);
/

create or replace type body  stragg_type4  is

  static function ODCIAggregateInitialize (sctx IN OUT stragg_type4) return number is begin
    sctx := stragg_type4 (null);
    dbms_lob.createtemporary (lob_loc => sctx.result, cache   => TRUE, dur => dbms_lob.call);
    return ODCIConst.Success;
  end;

  member function ODCIAggregateIterate (self IN OUT stragg_type4, value IN varchar2) return number is begin
    self.result := self.result || ',' || value;
    return ODCIConst.Success;
  end;

  member function ODCIAggregateTerminate (self IN stragg_type4, returnValue OUT CLOB, flags IN number) return number is begin
    returnValue := ltrim (self.result, ',');
    return ODCIConst.Success;
  end;

  member function ODCIAggregateMerge (self IN OUT stragg_type4, ctx2 IN stragg_type4) return number is begin
    self.result := self.result || ctx2.result;
    return ODCIConst.Success;
  end;

end;
/

sho err

CREATE or replace FUNCTION wm_concat_clob(input varchar2) RETURN CLOB PARALLEL_ENABLE AGGREGATE USING stragg_type4;
/