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

Question

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.

Answer 1

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.