oracle pivot和pivot xml-ora-00918:定义不明确的列

jm2pwxwz 于 2021-08-13 发布在 Java

关注(0)|答案(1)|浏览(519)

我正在尝试执行透视操作。枢轴工作正常。以防我想动态地传递查询而不是硬编码的值。根据pivotxml中的文档，它提供了错误。任何建议都会有帮助，
枢轴工作正常：

select * from
(select  column_name from cols where table_name = 'EMPLOYEES') aa
pivot
(
max(aa.column_name)
for column_name in ('EMPLOYEE_ID', 'FIRST_NAME')
);

pivot xml引发错误：

select * from
(select  column_name,  table_name from cols  where table_name = 'EMPLOYEES')
pivot xml
(
max(column_name)
for column_name in (select  column_name from cols where table_name = 'EMPLOYEES')
);

ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"

* Cause:
* Action:

尝试提供别名，但仍然没有帮助。
我们还有其他方法来定义in子句中的子查询吗

sql pivot oracle

来源：https://stackoverflow.com/questions/62083775/oracle-pivot-and-pivot-xml-ora-00918-column-ambiguously-defined

1条答案

按热度按时间

iqih9akk1#

尝试使用pl/sql语句，结果与预期一致。下面是pl/sql语句，

declare 

v_Sql1 varchar(1000);
v_Sql2 varchar(1000);

begin

select  (LISTAGG(chr(39)||column_name||chr(39), ',') WITHIN GROUP (ORDER BY column_id)) into v_Sql1 from cols where table_name = 'EMPLOYEES'; 

v_Sql2:= 'select * from
(select  column_name from cols where table_name = ''EMPLOYEES'') 
pivot
(
max(column_name)
for column_name in (' || v_Sql1 || '))'; 

dbms_output.put_line(v_Sql1);
dbms_output.put_line(v_Sql2);

execute immediate v_Sql2;

end;
/

赞(0）回复(0）举报 2021-08-13

我来回答

oracle pivot和pivot xml-ora-00918:定义不明确的列

1条答案

相关问题

热门标签

最新问答