Oracle XE 11. a very simple join query gave me the following error:
ORA-00932: inconsistent datatypes: expected - got CLOB
Tables:
Product
----------------------------------
id, name, description, categoryId
Catetory
------------------
id, name
The product description is CLOB.
SQL> desc Product;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(19)
NAME NOT NULL VARCHAR2(30 CHAR)
CATEGORYID NUMBER(19)
DESCRIPTION CLOB
SQL> desc Category;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(19)
NAME NOT NULL VARCHAR2(30 CHAR)
Query:
SELECT DISTINCT t1.ID, t1.DESCRIPTION, t1.NAME, t0.name FROM Product t1
LEFT OUTER JOIN Category t0 ON (t0.ID = t1.categoryId);
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected – got CLOB
IF I remove the t0.name from selection, it will work. weird.
SELECT DISTINCT t1.ID, t1.DESCRIPTION, t1.NAME FROM Product t1
LEFT OUTER JOIN Category t0 ON (t0.ID = t1.categoryId);
Thanks.
Best Answer
The
DISTINCT
keyword cannot be used forCLOB
datatypes. The workaround is :Going to your sample it would be: