Error ORA-00932 when using a select with union and CLOB fields
database, oracle, plsql
Solution
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.
Problem
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.