ORA-00923:使用over (分区)和Oracle中的Xmlagg

Question

我对下面的表单有一个查询，我需要它按照某一列进行分区，但是当我这样放置它时，它会给我错误: from关键字在预期的地方找不到。

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')

问题是什么，以及如何修复它，以便它在xmlagg中正常工作，就像下面的清单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')

Answer 1

您似乎希望选择它包含的表名和列。

在本例中，LISTAGG不需要OVER子句，因为您必须使用GROUP BY (如果您也想获取表名)，所以它将完成分区工作；此外，GROUP BY --反过来--使DISTINCT变得不必要。

就像这样：

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版本如下所示；正如上面所述，不需要像GROUP BY那样进行特殊分区：

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>