Is there a way to limit the length of the result of LISTAGG()?
I have two reasons for that:
- VARCHAR2 limit of 4000 bztes
- I only need the first 10 elements (rows)
The query I use is:
select count(order_id) numOfOrders, order.store_id, store.PhoneNr,
LISTAGG( order_Id, ', ' ) WITHIN group ( ORDER BY Order_Id) as first_ten_orders
from t_orders order
inner join store on store.store_id=order.store_id and (store.PhoneNr is not null )
where order_status_id in (1,2,6) and order_date!=shipped_date
group by order.store_id, store.PhoneNr;
So it is a list of non shipped orders, grouped by stores (only those with phone numbers), listing the store ID, phone number, number of unshipped orders and the list of order IDs.
I want to limit the list of order IDs, for example to first ten items.
How can I do that?
One way is to use a (custom) function to trim the result string, but then I would still have the first problem of VARCHAR2 length overflow in case of many orders.