I have a query of the form below and I need it to partition by a certain column but when I place it like this, it gives me the error : from keyword not found where expected

Select distinct t_name,  rtrim(xmlagg(xml element(e, text, ',').extract('//text()') order by c_id).getclobval(), ',' ) over (partition by t_name) col_list from all_cls where schema ='a' and table in ('tableA' , 'tableB')

What is the issue and how do I fix it so it functions properly with xmlagg as it does have for the below query with list agg :

Select distinct t_name,  listagg(text ',' ) within group(order by c_id) over (partition by t_name) col_list from all_cls where schema ='a' and table in ('tableA' , 'tableB')

1 Answers

0
Littlefoot On Best Solutions

It seems that you want to select table name and columns it contains.

LISTAGG, in this case, doesn't require OVER clause because you'll have to use GROUP BY (if you want to fetch table name as well) so it'll do the partitioning job; also, GROUP BY - in turn - makes DISTINCT unnecessary.

Something like this:

SQL> select table_name,
  2    listagg(column_name, ',' ) within group(order by column_id) col_list
  3  from all_tab_cols
  4  where owner = 'SCOTT'
  5    and table_name in ('DEPT' , 'BONUS')
  6  group by table_name;

TABLE_NAME COL_LIST
---------- --------------------------------------------------
BONUS      ENAME,JOB,SAL,COMM
DEPT       DEPTNO,DNAME,LOC

SQL>

XMLAGG version would be as follows; just as above, no special partitioning is necessary as GROUP BY does it:

SQL> select table_name,
  2    rtrim(xmlagg(xmlelement(e, column_name || ',').extract('//text()')
  3      order by column_id).getclobval(), ',' ) col_list
  4  from all_tab_cols
  5  where owner = 'SCOTT'
  6    and table_name in ('DEPT' , 'BONUS')
  7  group by table_name;

TABLE_NAME COL_LIST
---------- --------------------------------------------------
BONUS      ENAME,JOB,SAL,COMM
DEPT       DEPTNO,DNAME,LOC

SQL>