Re: Using SetDistinct()

From: Andrus Adamchik (
Date: Fri Nov 12 2004 - 17:00:40 EST

  • Next message: Mike Kienenberger: "Re: Using SetDistinct()"

    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
    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.


    > Mike,
    > Thanks for the quick response.
    > Do you have an alternative to the SetDistinct method to achieve a
    > distinct result?
    > Thanks.

    >>>> Mike Kienenberger <> 11/12/04 03:31PM >>>
    > Brian Plotner <> 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:
    >> 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