I have a query that was written in Postgresql. What is the Oracle equivalent of this query?

Question

I have a query written in Postgresql as I have given below. Can you write the Oracle equivalent of this query?

My query;

SELECT array_to_string(ARRAY( SELECT view_cus_result.channel
       FROM view_cus_result
      GROUP BY view_cus_result.channel))

How would this query be written in Oracle?

In Postgres, the `array_to_string` function requires a [second argument](https://www.postgresql.org/docs/14/functions-array.html) — trincot, Mar 30 '22 at 19:57
https://stackoverflow.com/questions/4326868/equivalent-to-postgresql-array-array-to-string-functions-in-oracle-9i — Ergest Basha, Mar 30 '22 at 20:00
Removed mysql tag, since that brand of SQL database does not appear to be considered in this question. — Bill Karwin, Mar 30 '22 at 20:02

score 1 · Answer 1 · answered Mar 30 '22 at 20:07

The given SQL lacks a second argument for array_to_string. So I'll assume you have this:

SELECT array_to_string(ARRAY( SELECT view_cus_result.channel
       FROM view_cus_result
      GROUP BY view_cus_result.channel), ',');

In Oracle you can use listagg:

SELECT listagg(channel, ',') WITHIN GROUP (ORDER BY channel)
FROM (SELECT DISTINCT channel FROM view_cus_result);

In version 19c and later, you can use the distinct keyword:

SELECT listagg(DISTINCT channel, ',') WITHIN GROUP (ORDER BY channel)
FROM view_cus_result;

score 1 · Answer 2 · answered Mar 30 '22 at 20:13

The subquery gets you a distinct list of channels. You make this an array and turn that array into a string. I am surprised, though, that works for you, because ARRAY_TO_STRING is lacking the delimiter parameter which is not optional.

A simpler way of writing this in PostgreSQL (with a comma as delimiter) is:

select string_agg(distinct channel, ',')
from view_cus_result;

In Oracle it is LISTAGG instead of STRING_AGG and the order is not optional:

select listagg(distinct channel, ',') within group (order by channel)
from view_cus_result;

I have a query that was written in Postgresql. What is the Oracle equivalent of this query?

2 Answers2