If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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:

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.

Jurij ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?

DISTINCT On Clob

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

Originally Posted by jmodic

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.