Re: Select Distinct with LOB fields

Comments embedded.
On Sep 5, 9:46 am, "Andy Fish" <ajf..._at_blueyonder.co.uk> wrote:
> Hi,
>
> I want to do a "Select distinct" including a table which has LOB columns and
> I see from the Oracle documentation that this is not possible.
>

In releases lower than 11g that is correct, as BLOB and BFILE support is available in SQL*Plus.

> all of my tables have a unqiue primary key which is an integer and is
> included in the select target list. is there any way to tell oracle that it
> only needs to use the PK field to test for uniqueness and doesn't have to
> compare the whole returned row?
>

Why are you then using 'select distinct'? It's redundant, as the inclusion of the primary key column ENSURES each row is unique.

> my current favourite solution is to post-filter the results in my C# code to
> remove duplicates manually. does anyone have any better solutions than that
> ?
>

You have a primary key included in the results (according to your post) so there shouldn't be any duplicates. Unless, of course, you haven't properly explained your 'problem' and expect others to read your mind with respect to your actions.

> TIA
>
> Andy

Post EXACTLY what you're doing and what errors you're seeing, along with why you think you need distinct when the primary key is included in the result set.

David Fitzjarrell Received on Wed Sep 05 2007 - 10:08:37 CDT