Duplicate rows from a SELECT DISTINCT DB2 UDB v8.1.9 Linux

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob Stearns - 10 Apr 2006 02:30 GMT

I am getting duplicate rows back from a select distinct statement of the
form:

SELECT DISTINCT 'jhough', '000111', t0.bhid
FROM (SELECT lots of good stuff) t0
LEFT OUTER JOIN another_table t1 ON relevant_stuff
WHERE (lots of conditions)

After re-reading the relevant pat ofVol 1 of the SQL Reference I am
unablee to see how this is possible.

For the interested (or the terminally bored) the statement in all its
gory details is attached to prevent anyones mailer from mseeing with the
line wrapping.

Reply to this Message

Bob Stearns - 10 Apr 2006 03:46 GMT

> I am getting duplicate rows back from a select distinct statement of the
> form:
[quoted text clipped - 10 lines]
> gory details is attached to prevent anyones mailer from messing with the
> line wrapping.

That was not the problem. I only saw duplicates because I expected
duplicates. This is the full-select of an INSERT:

INSERT INTO is3.animal_sets(userid, set_name, bhid)

where all three columns comprise the primary key. I am getting the message:

DB2 SQL error: SQLCODE: -803, SQLSTATE: 23505, SQLERRMC: 1;IS3.ANIMAL_SETS
Message: One or more values in the INSERT statement, UPDATE statement,
or foreign key update caused by a DELETE statement are not valid because
the primary key, unique constraint or unique index identified by "1"
constrains table "IS3.ANIMAL_SETS" from having duplicate rows for those
columns.

The result set from ust the select without the insert is:

1 2 BHID
------ ------ -------
jhough 000111 72761
jhough 000111 76961
jhough 000111 77082
jhough 000111 77263
jhough 000111 77270
jhough 000111 177301
jhough 000111 179541
jhough 000111 179581
jhough 000111 179602
jhough 000111 179603
jhough 000111 179607
jhough 000111 180161
jhough 000111 180181
jhough 000111 182261
jhough 000111 224521
jhough 000111 224541
jhough 000111 224561
jhough 000111 224601
jhough 000111 224641
jhough 000111 285461
jhough 000111 285481

which has no duplicates (though I took 2 of the last three for
duplicates on first reading). Therefore, I wonder where the message is
coming from.

Reply to this Message

Tonkuma - 10 Apr 2006 10:58 GMT

Some selected rows must be duplicatted with the rows are already in the
table that you want to insert.

Reply to this Message

Bob Stearns - 10 Apr 2006 16:57 GMT

> Some selected rows must be duplicatted with the rows are already in the
> table that you want to insert.

Thank you. You prompted me to look in detail at the preceding DELETE
which was almost right, deleting most of the relevant rows. Previously
my expectation was overriding my observation.

Reply to this Message

Bernard Dhooghe - 11 Apr 2006 09:33 GMT

A number of problems are tackled in FixPaks related to select
distincts.

Duplicate entries returned behavior, as experienced, where fixed when
going from FP10 to FP11.

Bernard Dhooghe

Reply to this Message

Bernard Dhooghe - 11 Apr 2006 09:37 GMT

A number of problems are tackled in FixPaks related to select
distincts.

Duplicate entries returned behavior, as experienced, was fixed when
going from FP10 to FP11.

Bernard Dhooghe

Reply to this Message

Bernard Dhooghe - 11 Apr 2006 10:54 GMT

A number of problems related to select distincts are tackled in
FixPaks.

Duplicate entries returned behavior, as experienced, was fixed when
going from FP10 to FP11.

Bernard Dhooghe

Reply to this Message