I have a query in which I want to group by some fields and aggregate the last field within a CSV string. If you come from SQL Server like me, you would use FOR XML PATH(''). But in Oracle 12c that's a different story :

Table definition

CREATE TABLE HCF (
ID NUMBER,
HCF_DATE DATE,
HCF_TYPE_1 NUMBER,
HCF_TYPE_2 NUMBER)

Sample data

ID  HCF_DATE    HCF_TYPE_1 HCF_TYPE_2
272 27/02/18    1          1
279 28/02/18    15         2
280 28/02/18    15         2
283 28/02/18    5          1

The query I'm using

WITH CTE_HCF AS (
SELECT HCF_DATE, HCF_TYPE_1, HCF_TYPE_2, COUNT(ID)
FROM HCF
GROUP BY HCF_DATE, HCF_TYPE_1, HCF_TYPE_2
HAVING COUNT(ID) > 0
)

SELECT a.*, b.*
FROM CTE_HCF a
CROSS APPLY (
    SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(ORDRE_ID,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS ids
    FROM ( SELECT HCF_DATE, HCF_TYPE_1, HCF_TYPE_2, ID,
            ROW_NUMBER() OVER (PARTITION BY HCF_DATE ORDER BY HCF_TYPE_1, HCF_TYPE_2) AS curr,
            ROW_NUMBER() OVER (PARTITION BY HCF_DATE ORDER BY HCF_TYPE_1, HCF_TYPE_2) -1 AS prev
            FROM CTE_HCF
            WHERE HCF_DATE = a.HCF_DATE AND HCF_TYPE_1 = a.HCF_TYPE_1 AND HCF_TYPE_2 = a.HCF_TYPE_2
        )
    CONNECT BY prev = PRIOR curr
    AND HCF_DATE = PRIOR HCF_DATE
    AND HCF_TYPE_1 = PRIOR HCF_TYPE_1
    AND HCF_TYPE_2 = PRIOR HCF_TYPE_2
    START WITH curr = 1 ) b

The error

ORA-00904: "a"."HCF_TYPE_2" :  invalid identifier

The desired output

HCF_DATE    HCF_TYPE_1 HCF_TYPE_2 IDS
27/02/18    1          1          272
28/02/18    15         2          279,280
28/02/18    5          1          283

I think that the problem is that fields from the original query are not visible in the sub query of the CROSS APPLY sub query.

PS : I have tried other ways described in this article but I fail for multiple reasons like for LISTAGG it exceeds the xK number of characters. And I don't have sufficient privileges to create a function and XMLAGG might bring down your Oracle instance.

Update Oracle version is : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0

2 Answers

0
Fourat On Best Solutions

Finally I managed to ask the DBA to create a function that concatenates id values in CSV, which I personally think is the best approach to return a CLOB value.

The function :

create or replace FUNCTION concatenate_list (p_cursor IN  SYS_REFCURSOR)
  RETURN  CLOB
IS
  l_return  CLOB; 
  l_temp    CLOB;
BEGIN
  LOOP
    FETCH p_cursor
    INTO  l_temp;
    EXIT WHEN p_cursor%NOTFOUND;
    l_return := l_return || ',' || l_temp;
  END LOOP;
  RETURN LTRIM(l_return, ',');
END;

The query

WITH CTE_HCF AS (
SELECT HCF_DATE, HCF_TYPE_1, HCF_TYPE_2, COUNT(ID)
FROM HCF
GROUP BY HCF_DATE, HCF_TYPE_1, HCF_TYPE_2
HAVING COUNT(ID) > 0
)

SELECT a.*
  , concatenate_list(CURSOR(SELECT id FROM HCF WHERE HCF_DATE = a.HCF_DATE AND HCF_TYPE_1 = a.HCF_TYPE_1 AND HCF_TYPE_2 = a.HCF_TYPE_2)) AS CSV
FROM CTE_HCF a

Source

PS : If you don't need a CLOB then LISTAGG is your best choice.

1
Chris Saxon On

If you're blowing out the character limit for listagg, how you handle this depends on what you want to display.

If you're on 12.2, you can use the on overflow truncate clause to trim characters that exceed the limit.

select hcf_date, hcf_type_1, hcf_type_2, 
       listagg ( id, ',' 
         on overflow truncate 
       ) within group ( 
         order by id
       ) csv
from   hcf
group  by hcf_date, hcf_type_1, hcf_type_2;

HCF_DATE               HCF_TYPE_1   HCF_TYPE_2   CSV       
27-FEB-2018 00:00:00                1             1 272        
28-FEB-2018 00:00:00                5             1 283        
28-FEB-2018 00:00:00               15             2 279,280 

If you're on 12.1 there are some other workarounds.

You can use row pattern matching (match_recognize) to find the length of the CSV for each extra row. And return those within the string limit:

I've added a couple of extra rows and set the character limit to 10 to show the principle:

insert into hcf values ( 281, to_date('28/02/18', 'dd/mm/yy'), 15, 2);
insert into hcf values ( 282, to_date('28/02/18', 'dd/mm/yy'), 15, 2);

with grps as ( 
  select *
  from   hcf match_recognize (
    partition by hcf_date, hcf_type_1, hcf_type_2
    order by id
    measures 
      sum(lengthb(s.id) + lengthb(';')) as len
    all rows per match
    after match skip past last row
    pattern (s+)
    define 
      s as 1=1
  )
)
  select hcf_date, hcf_type_1, hcf_type_2,
         listagg ( id, ',' ) 
           within group ( 
             order by id
           ) csv
  from   grps
  where  len <= 10
  group  by hcf_date, hcf_type_1, hcf_type_2;

HCF_DATE               HCF_TYPE_1   HCF_TYPE_2  CSV       
27-FEB-2018 00:00:00            1            1  272        
28-FEB-2018 00:00:00            5            1  283        
28-FEB-2018 00:00:00           15            2  279,280    

Or you could split the rows into separate groups when you hit the character limit. And show them as separate CSVs:

with grps as ( 
  select *
  from   hcf match_recognize (
    partition by hcf_date, hcf_type_1, hcf_type_2
    order by id
    measures 
      match_number() as grp
    all rows per match
    after match skip past last row
    pattern (s csv*)
    define csv as 
      lengthb(s.id) + sum(lengthb(csv.id) + lengthb(';')) < = 10
  )
)
  select hcf_date, hcf_type_1, hcf_type_2,
         listagg ( id, ',' ) 
           within group ( 
             order by id
           ) csv
  from   grps
  group  by hcf_date, hcf_type_1, hcf_type_2, grp;

HCF_DATE               HCF_TYPE_1   HCF_TYPE_2  CSV       
27-FEB-2018 00:00:00            1            1  272        
28-FEB-2018 00:00:00            5            1  283        
28-FEB-2018 00:00:00           15            2  279,280    
28-FEB-2018 00:00:00           15            2  281,282  

If you want to return the whole CSV list longer than the varchar2 limit, you need to return a clob. Which you can do with XML:

select hcf_date, hcf_type_1, hcf_type_2,
       substr (
          xmlcast ( 
            xmlagg (
              xmlelement(s, ',' || id)
              order by id
            ) as clob
          ), 2
        ) csv
from    hcf
group  by hcf_date, hcf_type_1, hcf_type_2;  

HCF_DATE               HCF_TYPE_1   HCF_TYPE_2   CSV               
27-FEB-2018 00:00:00            1             1  272                
28-FEB-2018 00:00:00            5             1  283                
28-FEB-2018 00:00:00           15             2  279,280,281,282