CODEWITHSUNDEEP
sqloracle-databasestring-aggregation
Hugo Sanches
Hugo Sanches

Reputation: 1

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

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.

Upvotes: 0

Views: 58

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

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.

Upvotes: 3

Related Questions