0

I have table products and I would like to create strings:

http://localhost/app/feeds/send?type=myType&ids=1,2,3,...,100
http://localhost/app/feeds/send?type=myType&ids=101,102,103,...,200

I created sql query:

select concat('http://localhost/app/feeds/send?type=myType' || CHR(38) || 'ids=' , product_id) from products where isin like 'AC%' and status in
('Active', 'Created', 'Live')
and
((date>to_date('07.05.2021','dd.MM.yyyy') or date is null));

and it seems to be nearly what I need but result is:

http://localhost/app/feeds/send?type=myType&ids=1
http://localhost/app/feeds/send?type=myType&ids=2
http://localhost/app/feeds/send?type=myType&ids=3

how can I change this query to return 100 ids with delimiter ,?

Michu93
  • 5,058
  • 7
  • 47
  • 80

2 Answers2

2

You can use the LISTAGG function in Oracle.

select 'http://localhost/app/feeds/send?type=myType' || CHR(38) || 'ids=' ||

  LISTAGG(product_id,',') WITHIN GROUP (ORDER BY product_id)

from products where isin like 'AC%' and status in
('Active', 'Created', 'Live')
and
((pdate>to_date('07.05.2021','dd.MM.yyyy') or pdate is null));

DB Fiddle

etsuhisa
  • 1,698
  • 1
  • 5
  • 7
  • do you also know how to group by 100 rows? Without it ORA-01489: result of string concatenation is too long. I can add `and ROWNUM <= 100;` but then there are only 100 rows, not like in a loop – Michu93 May 05 '21 at 08:35
  • I used this answer: https://stackoverflow.com/a/40754382/4952262 – Michu93 May 05 '21 at 09:34
1

Drop the Concat function, you don't need it.

Add a Group By clause using whatever fields separate ids 1 to 100 from 101 to 200.

Then have your query return a column using ListAgg() aggregate function like ListAgg(id,','). The first parameter is the field you are aggregating (id), and the second is the separator to use between two records.

Chris Maurer
  • 2,339
  • 1
  • 9
  • 8