Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Limit length of LISTAGG result?

David BalažicApr 11 2012 — edited Apr 11 2012
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.
This post has been answered by Frank Kulash on Apr 11 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 9 2012
Added on Apr 11 2012
3 comments
58,587 views