Oracle Error: inconsistent datatypes

2
votes

I am using the following query in my ORACLE(10g) DB.

SELECT * from student_table where student_no like '%STUDENT%' INTERSECT SELECT * from student_table where student_no in ('STUDENT1234','STUDENT5678')

I got error like: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB

Any Idea how to resolve this Error?

2
selectoracle10gintersectionora-00932
You are aware that, error notwithstanding, the intersect of those two will just be the result of the second query, aren't you? At least I think so. I could be wrong, It wouldn't be the first time :-) - paxdiablo
@paxdiablo: You are correct. If the comparisons or strings were different this could done with an "AND" in the where clause instead of an INTERSECT. - redcayuga

2 Answers

4
votes

I guess student_table contains at least one column with datatype clob.

You shoul not select * then, but only the non-clob columns.

1
votes

You can't do an INTERSECT when the result set includes any LOB.

In this case, however, you don't need the intersect anyway:

SELECT * from student_table
where student_no like '%STUDENT%'
and student_no in ('STUDENT1234','STUDENT5678');

And, as pointed out earlier, the first condition is redundant anyway in this particular instance:

SELECT * from student_table where student_no in ('STUDENT1234','STUDENT5678');