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



Quote Originally Posted by jmodic View Post
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:
Code:
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
Code:
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.