使用带有联合和 CLOB 字段的选择时出现 ORA-00932 错误

2021-12-24 00:00:00 database oracle plsql

首先，这不是这个 问题.如果是，抱歉，我无法通过阅读来解决我的问题.

First of all, this isn't a duplicate of this question. If it is, sorry but I couldn't solve my problem by reading it.

我收到此错误:

ORA-00932: inconsistent datatypes: expected - got CLOB

当我尝试执行这个 SELECT 语句时:

When I try to execute this SELECT statement:

SELECT TXT.t_txt 
  FROM CITADM.tb_avu_txt_grc GR  
 INNER JOIN CITADM.tb_avu_txt TXT   
    ON (GR.e_txt = TXT.e_txt and GR.u_txt = TXT.u_txt)  
 WHERE  TXT.u_lin_ord = 1
UNION
SELECT TXT.t_txt 
  FROM CITADM.tb_avu_txt_grc_cvd GRC  
 INNER JOIN CITADM.tb_avu_txt TXT  
    ON (GRC.e_txt = TXT.e_txt and GRC.u_txt = TXT.u_txt)  
 WHERE  TXT.u_lin_ord = 2

所选字段(t_txt) 是CLOB 数据类型.如您所见，它是同一个表的同一列.这个声明属于一个更大的声明，我已经隔离了我遇到这个问题的部分.

The selected field(t_txt) is of CLOB datatype. As you can see, it's the same column of the same table. This statement belongs to a bigger one, I've isolated the part where I'm having this problem.

非常感谢.

推荐答案

我认为问题在于使用了 UNION 而不是 UNION ALL.UNION 运算符将组合两个集合并消除重复项.由于无法比较 CLOB 类型，因此无法进行重复消除部分.

I believe the problem is the use of UNION instead of UNION ALL. The UNION operator will combine the two sets and eliminate duplicates. Since CLOB types cannot be compared, the duplicate elimination part is not possible.

使用 UNION ALL 不会尝试进行重复消除(反正你可能没有重复)所以它应该可以工作.

Using UNION ALL won't attempt to do duplicate elimination (you probably don't have duplicates anyways) so it should work.

相关文章