0

I am working on the query below:

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

when I am running this query I am getting following error message:

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.

But when I remove the line :

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

from my query then it executes without any error message. The working query is given below:

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

I am not sure why this error occured. Can someone help me to figure out the problem? I got same questions from these links link1 and link2, but I didn't get the solution to my question from these links.

Gary_W
  • 9,933
  • 1
  • 22
  • 40
Madhusudan
  • 4,637
  • 12
  • 55
  • 86
  • 1
    Without data, this is purely a guess - but I suspect the error is occurring in the LISTAGG function which aggregates data over a group. If the query returns a great deal of data, LISTAGG might be constructing a very large list - and the removal of one join condition might mitigate the amount of data being returned such that LISTAGG no longer fails. – David W Sep 10 '15 at 16:20
  • 1
    I don't see any reference to reporter r anywhere else in your query. – Dan Bracuk Sep 10 '15 at 16:20
  • 1
    Are you getting duplicates of the values you're aggregating when you add the extra left join - from one-to-many relationships? You could temporarily take out the listagg calls and do a count instead for both queries, and see the difference. You may already be getting back more rows/values than you really want. – Alex Poole Sep 10 '15 at 16:20
  • If order is not important you can use wm_concat instead of listAgg which allows for larger list, or if you only need distinct values you can use `wm_concat(Distinct ip.race_code)` but maybe `wm_concat(distinct ip.race_Code) over (order by race_code)` may work too if order is important... https://community.oracle.com/thread/1090158?tstart=0 – xQbert Sep 10 '15 at 19:03
  • @xQbert wm_concat() doesn't work in all oracle db's. So I can't use it. I heard about TO_CLOB() is it helpful? – Madhusudan Sep 11 '15 at 03:58

2 Answers2

4

The join to table "reporter" is probably increasing the record count (this could only be the case if column "intake_id" is not a unique key of "reporter"). By increasing the record count, you are generating more strings that LISTAGG must concatenate together within each group. If the total length of concatenated strings exceeds 4000 bytes, LISTAGG will fail with the error you see.

KevinKirkpatrick
  • 1,436
  • 1
  • 10
  • 15
  • This is a well known limitation that Oracle 12 has a workaround for. https://blogs.oracle.com/datawarehousing/entry/managing_overflows_in_listagg – kevinskio Sep 10 '15 at 16:40
  • Way back before LISTAGG, I'd implemented a function very similar to Tom Kyte's "STRAGG" function (using ODCI functions - Google "STRAGG" and "ODCI" for numerous implementations). I almost immediately ran into 4K limitation, but was able to resolve with simple variant of STRAGG: STRAGG_CLOB. It worked exactly like STRAGG (and LISTAGG, for that matter), but returned a CLOB without the pesky 4K limitation. I was shocked that Oracle did not (and has not) offered a LISTAGG_CLOB version of LISTAGG. That, IMO, would be a "fix" rather than a workaround. – KevinKirkpatrick Sep 10 '15 at 21:53
0

Let's take a different approach: just like when you're aggregating sums and joining to other tables, sometimes you have to materialize the results before you additional one-to-many join so your aggregation isn't artificially inflated due to multiple records in joining tables. This approach is assuming you don't need the repetition that is occurring due to the additional table joins however.

I think by creating the inline view the results of the race_Code will fit within size limits. This could be done using a CTE as well. Simply put, provided you don't need the record duplication, you may need to materialize the listAgg results individually first then join them back in. If you have other problems with the other listAggs, you may want to create multiple CTE's and then join them all back together in the end. This approach simply uses a inline view.

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
xQbert
  • 34,733
  • 2
  • 41
  • 62