ORACLE SQL Excluding results within Select (“Result of string concatenation is too long)

Question

For the last couple of days my head is spinning around this issue and I am really hoping you can help me with this. So simplifying the issue. This is the table I already have:

ID	Message	Time	Zone
1	A	1PM	PT
1	B	1PM	PT
1	C	1PM	PT
2	D	2AM	FR
2	E	2AM	FR
3	F	3PM	TK

I need the following output:

ID	Message	Time	Zone
2	D,E	2AM	FR
3	F	3PM	TK

I want to concatenate all the messages for the same id in the same field I tried to do this but since the messages for some IDs (example ID 1) are too big and retrieve ORA-01489 “result of string concatenation is too long”. This happens because I have hundreds of lines for the same IDs and concatenating them will make more than 4000 characters, so I want to exclude from the select everything that as a count of the same ID bigger or equal than 3 lines so that doesn’t process and doesn’t retrieve that error.

Right now the oversimplified code is something along the lines of:

    Select distinct id,
    LISTAGG(CASE when MESSAGE = ‘X’ THEN ‘Z’
    ELSE MESSAGE),
    TIME,
    ZONE
    FROM TABLE

Adding a where clause it’s not helping to solve this I think (but really I’m kind of lost here) that the solution must come within the select.

Already tried to do select clauses did not worked, already tried to trim but this is not visual it’s a matter of processing capacity so it also does nothing.

Answer 1

You can use an analytic query to get the count of rows for each ID:

select id, message, time, zone, count(*) over (partition by id) as cnt
from your_table

ID	MESSAGE	TIME	ZONE	CNT
1	A	1PM	PT	3
1	B	1PM	PT	3
1	C	1PM	PT	3
2	D	2PM	FR	2
2	E	2PM	FR	2
3	F	3PM	TK	1

And then you can use that as a subquery, excluding those where the count is higher than you want:

select id,
  listagg(case when message = 'X' then 'Z' else message end, ',')
    within group (order by message) as messages,
  time,
  zone
from (
  select id, message, time, zone, count(*) over (partition by id) as cnt
  from your_table
)
where cnt < 3
group by id, time, zone
order by id, time, zone;

ID	MESSAGES	TIME	ZONE
2	D,E	2PM	FR
3	F	3PM	TK

fiddle

In your data the time and zone values are the same for each ID. If they actually vary then you can aggregate those too, either separately or combined - fiddle. Or if 'time' is actually a date/timestamp you could combine them as a time stamp with time zone, and aggregate those. You might also want to aggregate them in the same order as the messages; or aggregate distinct values... depends how you are using the output.

You could also get the analytic sum of the lengths (or rather, byte lengths, to allow for multi-byte characters) of the messages and use that plus the count to see if it would exceed the aggregation limit, to allow more shorter messages.

And as I mentioned in a comment, you can use the on overflow truncate clause in recent versions - though you already said that isn't suitable for what you are doing.