为什么会出现以下错误LISTAGG函数:“字符串连接的结果太长"?* [英] Why do I get the following error LISTAGG function: “result of string concatenation is too long"?*

问题描述

我正在使用此查询

HR--Any baby with a HR<80 
AS
(SELECT fm.y_inpatient_dat, h.pat_id, h.pat_enc_csn_id, 
LISTAGG(meas_value, '; ') WITHIN GROUP (ORDER BY fm.recorded_time) abnormal_HR_values
from
ip_flwsht_meas fm
join pat_enc_hsp h on fm.y_inpatient_dat = h.inpatient_data_id
where fm.flo_meas_id in ('8' ) and (to_number(MEAS_VALUE) <'80')
AND fm.recorded_time between (select start_date from dd) AND (select end_date from dd)
group by fm.y_inpatient_dat,h.pat_id, h.pat_enc_csn_id)

，我收到以下错误消息:

ORA-01489:字符串连接的结果太长

我已经在线研究了如何设置大小限制，但是我似乎无法使其起作用.有人可以建议如何设置限制，以使其不超过50个字符.

解决方案

正如其他评论员已经说过的那样，在Oracle 12.2(List_agg具有新选项"ON FORFLOW TRUNCATE")之前，无法避免这种错误.

在早期版本的oracle中，如果连接长度超过4000字节的字符串，则会出现该错误.您无法阻止它.

如果您仍需要在以前的版本中执行此操作，则必须编写自己的函数来执行此操作，并且需要相应地修改查询:

此自定义功能可能会解决您的问题

 create or replace type TAB_STRINGS is table of varchar2(4000) 
 /
 create or replace function My_list_agg(strings in TAB_STRINGS,
                      separator  in varchar2,
                      max_len    integer) return varchar2 deterministic is
   result varchar2(32000);
   tmp    varchar2(32000);
 begin
   result := null;
   if strings is not null then
       for idx in strings.first .. strings. last loop
         tmp := strings(idx);
         if tmp is not null then
           if result is null then
             exit when length(tmp) > max_len;
             result := tmp;
           else
             exit when(length(result) + length(separator) + length(tmp)) > max_len;
             result := result || separator || tmp;
           end if;
         end if;
       end loop;
   end if;
   return result;
 end;
 /

您需要使用CAST/COLLECT运算符才能使用它.
这是一个用法示例:

   select table_name,
          My_list_agg(  
                 -- first argument: array of strings to be concatenated
                 cast ( collect (column_name order by column_name) as TAB_STRINGS),
                 -- second (optional) argument: the separator
                 ',',
                 -- third argument (optional): the maximum length of the string to be returned
                 1000   
          ) as column_list
   from user_tab_columns t
   group by table_name
   order by table_name

I am using this query

HR--Any baby with a HR<80 
AS
(SELECT fm.y_inpatient_dat, h.pat_id, h.pat_enc_csn_id, 
LISTAGG(meas_value, '; ') WITHIN GROUP (ORDER BY fm.recorded_time) abnormal_HR_values
from
ip_flwsht_meas fm
join pat_enc_hsp h on fm.y_inpatient_dat = h.inpatient_data_id
where fm.flo_meas_id in ('8' ) and (to_number(MEAS_VALUE) <'80')
AND fm.recorded_time between (select start_date from dd) AND (select end_date from dd)
group by fm.y_inpatient_dat,h.pat_id, h.pat_enc_csn_id)

and I get the following error:

ORA-01489: result of string concatenation is too long

I have researched online how to set a size limit, but I can't seem to make it work. Can someone please advise how to set a limit so it does not exceed the 50 characters.

解决方案

As other commentators already said, there is no way to avoid such error until Oracle 12.2 (where List_agg has the new option "ON OVERFLOW TRUNCATE").

In previous versions of oracle, if you concatenate strings longer than 4000 bytes, you get that error. you have NO way of preventing it.

If you still need to do that in previous versions, you have to write your own function for doing it and you need to modify your query accordingly:

This custom function might solve your problem

 create or replace type TAB_STRINGS is table of varchar2(4000) 
 /
 create or replace function My_list_agg(strings in TAB_STRINGS,
                      separator  in varchar2,
                      max_len    integer) return varchar2 deterministic is
   result varchar2(32000);
   tmp    varchar2(32000);
 begin
   result := null;
   if strings is not null then
       for idx in strings.first .. strings. last loop
         tmp := strings(idx);
         if tmp is not null then
           if result is null then
             exit when length(tmp) > max_len;
             result := tmp;
           else
             exit when(length(result) + length(separator) + length(tmp)) > max_len;
             result := result || separator || tmp;
           end if;
         end if;
       end loop;
   end if;
   return result;
 end;
 /

you need to use the CAST/COLLECT operator to use it.
this is an usage example:

   select table_name,
          My_list_agg(  
                 -- first argument: array of strings to be concatenated
                 cast ( collect (column_name order by column_name) as TAB_STRINGS),
                 -- second (optional) argument: the separator
                 ',',
                 -- third argument (optional): the maximum length of the string to be returned
                 1000   
          ) as column_list
   from user_tab_columns t
   group by table_name
   order by table_name

这篇关于为什么会出现以下错误LISTAGG函数:“字符串连接的结果太长"?*的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！