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

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?

CodePudding user response：

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;

CodePudding user response：

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;