CODEWITHSUNDEEP
oracle-databasejoinclob
Dave
Dave

Reputation: 769

inconsistent datatypes: expected - got CLOB for table join

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.

Upvotes: 6

Views: 43558

Answers (3)

Krzysztof Kosmatka
Krzysztof Kosmatka

Reputation: 487

As @dcieslak has already stated, The DISTINCT keyword cannot be used for CLOB datatypes.

IF I remove the t0.name from selection, it will work. weird.

I guess there is an unique index on Product.id column. When you remove t0.name from SELECT clause, all selected columns com from the same table. So when one of those columns have unique values (t1.id in your case), then all rows will always be unique. And that implies that there is no need for any comparison of CLOB columns.

BUT if there is an unique index on Product.id, then you do not need DISTINCT keyword at all. For query:

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

you will get exactly one row for each row from Product table, which must be distinct due to distinct t1.ID values.

Upvotes: -1

dcieslak
dcieslak

Reputation: 2715

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

Upvotes: 13

cableload
cableload

Reputation: 4375

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);

Upvotes: 1

Related Questions