Hi Brian,
From my experience, DISTINCT works correctly on all databases, and as Mike
said, it works on a result row as a whole. There is one scenario that Mike
mentioned where it may *appear* to break on Cayenne side. This is when you
have more than one record that has the same value for a primary key column
(PK as defined in Cayenne DataMap for ETL_MAP_NME table). You need to
confirm that this is indeed the case before you can implement a solution.
So try running this SQL manually and look for any PK duplicates.
If this is the case, it means that the data in the table is in
inconsistent state. Unless you can (a) fix the data or (b) define PK
differently to be truly unique, you'll have to do some manual work to fix
the result. Cayenne won't know how to do it for you.
One idea is to fetch the result using ResultIterator
(http://objectstyle.org/cayenne/userguide/perform/result-iterator.html)
and check if each row PK column(s) are a duplicate of another row, and
then take appropriate action (e.g. skip it, delete it, etc.)
Hope this helps.
Andrus
> Mike,
>
> Thanks for the quick response.
>
> Do you have an alternative to the SetDistinct method to achieve a
> distinct result?
>
> Thanks.
>
>
>>>> Mike Kienenberger <mkienen..laska.net> 11/12/04 03:31PM >>>
> Brian Plotner <BPLOTNE..ndependenthealth.com> wrote:
>> I have a table with one column that contains several rows of repeating
>>
> values and I want to use the SetDistinct method to return only the
> distinct rows.
>>
>> I pass a true to the SetDistinct method but the repeating rows are
>> still
> returned.
>>
>> The query executed in the QueryLogger is:
>>
>> SELECT DISTINCT t0.TABLE_NAME, t0.ETL_MAP_NME_KEY FROM IHA.ETL_MAP_NME
>> t0
>>
>> Does anyone have any ideas as to why this is happening?
>>
>> Any feedback would be greatly appreciated.
>
> I'm no expert, but I suspect distinct is working on the TABLE_NAME,
> ETL_MAP_MNE_KEY pair rather than just on TABLE_NAME. Since key is
> probably unique for every row, each pair is also unique.
>
> The behavior of distinct may also vary between databases.
>
> But again, this is just guesswork on my part.
>
> -Mike
This archive was generated by hypermail 2.0.0 : Fri Nov 12 2004 - 17:00:42 EST