Charater string buffer too small

Question

I have select:

select v.accs, v.currency,v.amount,v.drcr_ind, count(*) qua,wm_concat(ids) npx_IDS,
wm_concat(px_dtct) npx_DTCT 
from table v
group by accs, currency, amount, drcr_ind

but i get error ORA-06502: PL/SQL: : character string buffer too small if i'll remove one string, because sometimes (when v.accs= 3570) count(*) = 215 but when i try to skip using wm_concat for v.accs= 3570 for example this way:

select v.accs, v.currency,v.amount,v.drcr_ind, count(*) qua,wm_concat(ids) npx_IDS,
(case when v.accs = 3570 then wm_concat(px_dtct) else 'too many' end) npx_DTCT 
from table v
group by accs, currency, amount, drcr_ind

i still have the same error message. But why? How can i fix it?

Thanx

Answer 1

Why? Because you still use wm_concat for accs=3570... swap the THEN and ELSE part of your CASE expression

select v.accs, v.currency,v.amount,v.drcr_ind, count(*) qua,wm_concat(ids) npx_IDS,
       (case when v.accs = 3570 then 'too many' else wm_concat(px_dtct) end) npx_DTCT
  from table v group by accs, currency, amount, drcr_ind

Answer 2

First, as it has already been told, you have to switch then and else clauses in your query. Then, I guess you should also similarily process your second wm_concat, the one that works with ids.

select v.accs, v.currency,v.amount,v.drcr_ind, count(*) qua,
(case when v.accs = 3570 then 'too many' else wm_concat(ids) end) npx_IDS,
(case when v.accs = 3570 then 'too many' else wm_concat(px_dtct) end) npx_DTCT 
from table v
group by accs, currency, amount, drcr_ind

And, finally, why do you think that only v.accs = 3570 is able to bring 06502 error in front of you? I suppose you should handle all of them.

Answer 3

You concatenate results from a query. This query can result in a lot of rows so eventually you will run out of string length. Maybe concatenation is not the way to go here. Depends on what you want to achieve of course.