Is there any way to write a select statement for distinct when there is a CLOB field? 2

[kernal]

SELECT DISTINCT id, description FROM table" doesn't work because description is a CLOB field.

Thanks for your help!

 

[carp]

It may be a bit limiting, but you might try:

SELECT DISTINCT id, dbms_lob.substr(description,500,1) FROM table;

For this to be useful, you will need to know how long a string in your CLOB has to be to determine distinctness. This example assumes 500 characters will be sufficient.

 

[kernal]

Hi carp,

Sorry for the late reply. So far, the field has 3,054 characters but it could increase and we need to show all of the CLOB.

Thanks

 

[Beilstwh]

Personally I would have a column in the table with the clob to hold a hash value that could be used to check for a distinct value in the clob. Maintain the hash column by having a trigger on insert or update of the clob column and use DBMS_CRYPTO.HASH to calculate the hash. I would not use a virtual column because everytime you looked at the data it would have to fire the DBMS_CRYPTO.HASH. By using a trigger and an actual column it would only fire when necessary

Bill
Lead Application Developer
New York State, USA

 

[john parker]

I also face the same problem. From Contact Arlo support, I find a solution. Still, I can't solve the problem.