时间戳

首页 > 解决方案 > 为什么我得到以下错误 LISTAGG 函数：“字符串连接的结果太长”？*

sql - 为什么我得到以下错误 LISTAGG 函数：“字符串连接的结果太长”？*

问题描述

我正在使用这个查询

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 个字符。

标签： sqloracleoracle11g

解决方案

正如其他评论员已经说过的，在 Oracle 12.2 之前无法避免此类错误（其中 List_agg 具有新选项“ON OVERFLOW 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

推荐阅读