Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

database - Error ORA-00932 when using a select with union and CLOB fields

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.

I'm getting this error:

ORA-00932: inconsistent datatypes: expected - got CLOB

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

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.

Thank you very much.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...