SQL错误: ORA-01489:字符串连接结果太长

Question

我正在处理以下查询：

select ip.intake_id,
       ip.estimated_years,
       ip.gender_code,
       LISTAGG(ip.race_code, ',') WITHIN GROUP (ORDER BY ip.race_code) as race_code,
       eth.ethnicity_code, 
       i.living_arrangements,
       p.dep_actv_military_flag,
       LISTAGG(ale.allegation_super_type_code) WITHIN GROUP (ORDER BY ale.allegation_super_type_code) as maltreatment_type_code,
       LISTAGG(ale.initial_report_disp_code) WITHIN GROUP (ORDER BY ale.initial_report_disp_code) as maltreatment_dispo_lvl,
       ip.deceased_flag,
       LISTAGG(ch.characteristic_code, ',') WITHIN GROUP (ORDER BY ch.characteristic_code) as chara_codes,
       LISTAGG(ich.intake_characteristic_code, ',') WITHIN GROUP (ORDER BY ich.intake_characteristic_code) as intake_chara_codes,
       pe.removed_date,cm.petition_submitted_flag,cm.created_date,atr.person_id
from intake i inner join intake_participant ip on i.intake_id = ip.intake_id
       left outer join reporter r ON i.intake_id=r.intake_id
       left outer join ethnicity eth on eth.person_id = ip.person_id
       left outer join person p on p.person_id  = ip.person_id
       left outer join allegation ale on ale.intake_id = i.intake_id
       left outer join characteristic ch on ch.person_id = ip.person_id
       left outer join intake_characteristic ich on ich.intake_id = i.intake_id
       left outer join placement_episode pe on pe.child_id = ip.person_id
       left outer join complaint cm on cm.petitioner_id = ip.person_id
       left outer join attorney atr on atr.person_id = ip.person_id
       left outer join intake_participant_role apr on apr.intake_participant_id = ip.intake_participant_id
group by ip.intake_id,ip.estimated_years,ip.gender_code,eth.ethnicity_code,i.living_arrangements,p.dep_actv_military_flag,
    ip.deceased_flag,pe.removed_date,cm.petition_submitted_flag,cm.created_date,atr.person_id

当我运行此查询时，我收到以下错误消息：

Error report:
SQL Error: ORA-01489: result of string concatenation is too long
01489. 00000 -  "result of string concatenation is too long"
*Cause:    String concatenation result is more than the maximum size.
*Action:   Make sure that the result is less than the maximum size.  

但当我删除这行代码时：

left outer join reporter r ON i.intake_id=r.intake_id 

在我的查询中，它执行时没有任何错误消息。工作查询如下：

select ip.intake_id,
       ip.estimated_years,
       ip.gender_code,
       LISTAGG(ip.race_code, ',') WITHIN GROUP (ORDER BY ip.race_code) as race_code,
       eth.ethnicity_code, 
       i.living_arrangements,
       p.dep_actv_military_flag,
       LISTAGG(ale.allegation_super_type_code) WITHIN GROUP (ORDER BY ale.allegation_super_type_code) as maltreatment_type_code,
       LISTAGG(ale.initial_report_disp_code) WITHIN GROUP (ORDER BY ale.initial_report_disp_code) as maltreatment_dispo_lvl,
       ip.deceased_flag,
       LISTAGG(ch.characteristic_code, ',') WITHIN GROUP (ORDER BY ch.characteristic_code) as chara_codes,
       LISTAGG(ich.intake_characteristic_code, ',') WITHIN GROUP (ORDER BY ich.intake_characteristic_code) as intake_chara_codes,
       pe.removed_date,cm.petition_submitted_flag,cm.created_date,atr.person_id
from intake i inner join intake_participant ip on i.intake_id = ip.intake_id
       left outer join ethnicity eth on eth.person_id = ip.person_id
       left outer join person p on p.person_id  = ip.person_id
       left outer join allegation ale on ale.intake_id = i.intake_id
       left outer join characteristic ch on ch.person_id = ip.person_id
       left outer join intake_characteristic ich on ich.intake_id = i.intake_id
       left outer join placement_episode pe on pe.child_id = ip.person_id
       left outer join complaint cm on cm.petitioner_id = ip.person_id
       left outer join attorney atr on atr.person_id = ip.person_id
       left outer join intake_participant_role apr on ipr.intake_participant_id = ip.intake_participant_id
group by ip.intake_id,ip.estimated_years,ip.gender_code,eth.ethnicity_code,i.living_arrangements,p.dep_actv_military_flag,
    ip.deceased_flag,pe.removed_date,cm.petition_submitted_flag,cm.created_date,atr.person_id

我不确定为什么会发生这个错误。有人能帮我解决这个问题吗？我从这些链接link1和link2得到了同样的问题，但我没有从这些链接中得到我的问题的解决方案。

Answer 1

连接到表"reporter“可能会增加记录计数(只有当列"intake_id”不是“reporter”的唯一键时才会出现这种情况)。通过增加记录计数，可以生成更多的字符串，LISTAGG必须在每个组中将这些字符串连接在一起。如果连接字符串的总长度超过4000字节，LISTAGG将失败，并显示您看到的错误。

Answer 2

让我们采用一种不同的方法:就像您聚合和并连接到其他表时一样，有时您必须在附加的一对多连接之前实体化结果，以便您的聚合不会由于连接表中的多个记录而人为膨胀。但是，这种方法假定您不需要由于额外的表连接而发生的重复。

我认为通过创建内联视图，race_Code的结果将符合大小限制。这也可以使用CTE来完成。简单地说，如果您不需要记录复制，您可能需要首先逐个物化listAgg结果，然后再将它们连接起来。如果其他listAggs有其他问题，您可能需要创建多个CTE，然后最终将它们连接在一起。这种方法简单地使用内联视图。

select B.intake_id,
       B.estimated_years,
       B.gender_code,
       B.race_code,
       eth.ethnicity_code, 
       B.living_arrangements,
       p.dep_actv_military_flag,
       LISTAGG(ale.allegation_super_type_code) WITHIN GROUP (ORDER BY ale.allegation_super_type_code) as maltreatment_type_code,
       LISTAGG(ale.initial_report_disp_code) WITHIN GROUP (ORDER BY ale.initial_report_disp_code) as maltreatment_dispo_lvl,
       B.deceased_flag,
       LISTAGG(ch.characteristic_code, ',') WITHIN GROUP (ORDER BY ch.characteristic_code) as chara_codes,
       LISTAGG(ich.intake_characteristic_code, ',') WITHIN GROUP (ORDER BY ich.intake_characteristic_code) as intake_chara_codes,
       pe.removed_date,cm.petition_submitted_flag,cm.created_date,atr.person_id
from (SELECT ip.intake_id,
       ip.estimated_years,
       ip.gender_code,
       ip.deceased_flag,
       ip.person_id,
       ip.intake_participant_id,
       LISTAGG(ip.race_code, ',') WITHIN GROUP (ORDER BY ip.race_code) as race_code, i.living_arrangements 
      FROM intake i 
      INNER JOIN intake_participant ip on i.intake_id = ip.intake_id
      GROUP BY ip.intake_id, ip.estimated_years, ip.gender_code, 
               i.living_arrangements, ip.deceased_flag, ip.person_id,                
               ip.intake_participant_id) B
left outer join ethnicity eth on eth.person_id = B.person_id
left outer join person p on p.person_id  = B.person_id
left outer join allegation ale on ale.intake_id = B.intake_id
left outer join characteristic ch on ch.person_id = B.person_id
left outer join intake_characteristic ich on ich.intake_id = i.intake_id
left outer join placement_episode pe on pe.child_id = B.person_id
left outer join complaint cm on cm.petitioner_id = B.person_id
left outer join attorney atr on atr.person_id = B.person_id
left outer join intake_participant_role apr on ipr.intake_participant_id = B.intake_participant_id
GROUP BY  ip.intake_id,ip.estimated_years,ip.gender_code, eth.ethnicity_code,i.living_arrangements,p.dep_actv_military_flag, ip.deceased_flag,pe.removed_date,cm.petition_submitted_flag, cm.created_date,atr.person_id