inconsistent datatypes: expected - got CLOB for table join

Question

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.

Answer 1

The DISTINCT keyword cannot be used for CLOB datatypes. The workaround is :

SELECT a.*
     , b.clob
 FROM  (SELECT DISTINCT
               ... /* columns list wihtout clob columns */
         FROM  ...
        ) a
 JOIN
       table_with_clobs b
  ON   ...

Going to your sample it would be:

SELECT Po.ID, Po.DESCRIPTION, Po.NAME, PC.CatName 
  FROM
   ( SELECT DISTINCT t1.ID, t0.name CatName 
       FROM Product t1 
       LEFT OUTER JOIN Category t0 
         ON t0.ID = t1.categoryId 
    ) PC
    join Product PO
    on PO.ID = PC.ID

Answer 2

If your clob column does not contain more than 4000 characters, you could try this..

SELECT DISTINCT t1.ID, to_char(t1.DESCRIPTION), t1.NAME FROM Product t1 
LEFT OUTER JOIN Category t0 ON (t0.ID = t1.categoryId);