ORA-00932 (inconsistent datatypes: expected - got CLOB) error that I do not understand

Question

I started out with a query as such:

    SELECT A.*
      FROM TABLE_A A
INNER JOIN TABLE_B B
        ON A.YEAR = B.YEAR
     WHERE A.ID IN (SELECT ID FROM TABLE_B)

The above query gave me duplicate records, so I added the DISTINCT keyword as such:

    SELECT DISTINCT A.*
      FROM TABLE_A A
INNER JOIN TABLE_B B
        ON A.YEAR = B.YEAR
     WHERE A.ID IN (SELECT ID FROM TABLE_B)

This second query gave me the following error:

ORA-00932: inconsistent datatypes: expected - got CLOB

932. 00000 - "inconsistent datatypes: expected %s got %s"

I finally corrected the query and eliminated the duplicate records by moving the condition in the WHERE clause to the INNER JOIN like this:

    SELECT A.*
      FROM TABLE_A A
INNER JOIN TABLE_B B
        ON A.YEAR = B.YEAR
       AND A.ID = B.ID

I just don't understand why the second query did not work. Why does adding the DISTINCT keyword here suddenly cause an issue?

Answer 1

It's not a particularly enlightning error message, but it's because of the restrictions on LOBs, among which are:

• You cannot specify LOB columns in the ORDER BY clause of a query, the GROUP BY clause of a query, or an aggregate function.

• You cannot specify a LOB column in a SELECT... DISTINCT or SELECT... UNIQUE statement or in a join. However, you can specify a LOB attribute of an object type column in a SELECT... DISTINCT statement, a query that uses the UNION, or a MINUS set operator if the object type of the column has a MAP or ORDER function defined on it.

You're hitting the second of those, but the first comes up from time to time here too.

You can fake the same situation with something like:

SQL> select distinct col from (select to_clob('test') as col from dual);

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

You would be able to use distinct if you listed only non-CLOB columns, instead of using A.*, which is including a CLOB column from table_a.

You can work around this, but correcting the query to eliminate the duplicates was the right thing to do. Having to add distinct to a query is often a sign that something is wrong and you're fixing the symptoms rather than the cause.

Answer 2

Solution is to use the function to_char() around each returned field, as Distinct/Group By/etc. cannot deal with a clob or even a substring of a clob.