Distinct select with CLOB field

[luciffer]

Can anyone tell me why i got the following error:

ORA-00932: inconsistent datatypes: expected - got CLOB

when i try to do a

Select Distinct(id), text
From paragraphs
where id IN
(select paragraph_id
from menuitems
where section_id = 10);

Is there some way to return the CLOB field with distinct IDs?

[jmodic]

Is this what you want?

select p.id, p.text from paragraphs p
where p.id in
(select distinct m.paragraph_id
 from menuitems m
 and m.section_id = 10);

[luciffer]

you are correct that your query would work and i realize that, but i want to know why you can't have select the distinct(id) and the clob field in the same select?

Or how to do it without getting the inconsistent datatypes error.

[jmodic]

Originally posted by luciffer
you are correct that your query would work and i realize that, but i want to know why you can't have select the distinct(id) and the clob field in the same select?

Or how to do it without getting the inconsistent datatypes error.

It is bacuse DISTINCT is an operator that is applicable *to the whole row* in a resultset, not to a particular *column*!

Your query was:

Select Distinct(id), text from ...

but that doesn't mean that the distinct was ment only for the ID column, it rather said: give me the distinct combination of all id+text values from my table. And since clobs can not be (directly) compared to each other oracle returned ORA-00932: inconsistent datatypes error.

DISTINCT is an operator, not a function, and is allways applied to all the columns in the query's columnb list. That's also the reason why it has to be specified before the column list. Try changing your query to

Select text, Distinct(id) from ...

and you'll get error due to invalid syntax.

BTW, it is rather unfortunate that the SQL parser didn't reject your query for invalid syntax - I would expect it to do so. In my opinion, it should be treatened as invalid syntax because there is no space between DISTINCT and the left patrenthesis in '(id)'. Remember, DISTINCT is not a function, so those parenthesis around 'id' actualy have no meaning at all, so there should be a space between the DISTINCT and the left parenthesis.

[luciffer]

Thanks for the explanation, that clears it all up.

[rajeshalwaysin]

CAn some one explain how to get the distinct records,when a row as clob memeber.

It is bacuse DISTINCT is an operator that is applicable *to the whole row* in a resultset, not to a particular *column*!

Your query was:

Select Distinct(id), text from ...

but that doesn't mean that the distinct was ment only for the ID column, it rather said: give me the distinct combination of all id+text values from my table. And since clobs can not be (directly) compared to each other oracle returned ORA-00932: inconsistent datatypes error.

DISTINCT is an operator, not a function, and is allways applied to all the columns in the query's columnb list. That's also the reason why it has to be specified before the column list. Try changing your query to

Select text, Distinct(id) from ...

and you'll get error due to invalid syntax.

BTW, it is rather unfortunate that the SQL parser didn't reject your query for invalid syntax - I would expect it to do so. In my opinion, it should be treatened as invalid syntax because there is no space between DISTINCT and the left patrenthesis in '(id)'. Remember, DISTINCT is not a function, so those parenthesis around 'id' actualy have no meaning at all, so there should be a space between the DISTINCT and the left parenthesis.