36

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.

Community
  • 1
  • 1
gabsferreira
  • 3,089
  • 7
  • 37
  • 61

2 Answers2

72

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.

Eric Petroelje
  • 59,820
  • 9
  • 127
  • 177
-1

As I had duplicates, I couldn't use UNION ALL. This solution work perfectly, thank you!

BTW: This is imho the only correct answer, because UNION ALL and UNION are semantically different. If don't have duplicates at all, using UNION imposes an unnecessary sort overhead.

Friedrich
  • 82
  • 1
  • 4