I use listaggcombined with case when as aggretation as follows:

Select 
date as "Some Date",
sum(case when status = 'active' then 1 else 0 end) as "active id numbers",
listagg(case when status = 'active' then id_number END, ',') within group (order by id_number) as "List of active id numbers"
from mytable
group by date;

Result:

It works like a charm, unless the result is too long, as listaggis limited to 4000 bytes.

I cannot use on overflow truncate to truncate the listagg resultset, as I really need the full result.

In LISTAGG function: "result of string concatenation is too long" it was suggested to generally replace listagg with xmlagg, to receive a CLOB instead, which would work for me.

However, I do not manage to correctly replace my listagg with xmlagg, as the case when statement does not seem to work.

This does not work: (error: 'missing keyword')

Select 
date as "Some Date",
sum(case when status = 'active' then 1 else 0 end) as "active id numbers",
RTRIM(XMLAGG(XMLELEMENT(E,case when status = 'active' then id_number,',').EXTRACT('//text()') ORDER BY id_number).GetClobVal(),',') AS LIST
from mytable
group by date;

Could you advice me on how to work out the listagg limitations in my case? Will xmlagg work at all with the case when statement?

Thanks in advance

CodePudding user response：

A simple option might be to use your current query - with CASEs but without aggregations - as a "source" for query which actually does aggregations on values that are already prepared through CASE. Something like this:

WITH
   temp
   AS
      -- your current query, without aggregations
      (SELECT datum,
              CASE WHEN status = 'active' THEN 1 ELSE 0 END status,
              CASE WHEN status = 'active' THEN id_number END id_number
         FROM mytable)
  SELECT datum,
         SUM (status) AS active_id_numbers,
         RTRIM (
            XMLAGG (XMLELEMENT (e, id_number, ',').EXTRACT ('//text()')
                    ORDER BY id_number).getclobval (),
            ',') AS list
    FROM temp
GROUP BY datum

CodePudding user response：

You are missing the END keyword for the CASE expression:

Select date_column as "Some Date",
       sum(case when status = 'active' then 1 else 0 end) as "active id numbers",
       RTRIM(
         XMLAGG(
           XMLELEMENT(
             E,
             case when status = 'active' then id_number END,  -- Here
             ','
           ).EXTRACT('//text()')
           ORDER BY id_number
         ).GetClobVal(),
         ','
       ) AS LIST
from   mytable
group by date_column;

However, you could just add a WHERE filter:

Select date_column as "Some Date",
       COUNT(*) as "active id numbers",
       RTRIM(
         XMLAGG(
           XMLELEMENT(
             E,
             id_number,
             ','
           ).EXTRACT('//text()')
           ORDER BY id_number
         ).GetClobVal(),
         ','
       ) AS LIST
from   mytable
where  status = 'active'
group by date_column;

db<>fiddle here