By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,611 Members | 2,296 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,611 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.

SELECT DISTINCT 'jhough', '000111', t0.bhid
FROM (SELECT tal.namex AS locname, tac.color_desc AS color, tad.disposal_desc AS disposal, ta.tag2 AS tag,
ta.old_tag2 AS old_tag, tc.namex AS sire_name, tc.tattoo AS sire_tattoo, tc.assoc AS sire_assoc,
tc.prefix AS sire_prefix, tc.regnum AS sire_regnum, tce.herd_id AS sire_id, tcl.namex AS sire_locnam,
td.namex AS dam_name, td.tattoo AS dam_tattoo, td.assoc AS dam_assoc, td.prefix AS dam_prefix,
td.regnum AS dam_regnum, tde.herd_id AS dam_id, tdl.namex AS dam_locnam, mgs.namex AS mgs_name,
mgs.tattoo AS mgs_tattoo, mgs.assoc AS mgs_assoc, mgs.prefix AS mgs_prefix, mgs.regnum AS mgs_regnum,
mgse.herd_id AS mgs_id, mgsl.namex AS mgs_locnam, te.namex AS recip_name, te.tattoo AS recip_tattoo,
te.assoc AS recip_assoc, te.prefix AS recip_prefix, te.regnum AS recip_regnum,
tee.herd_id AS recip_id, tel.namex AS recip_locnam, ta.controller, ta.tattoo, ta.tattoo_loc,
ta.assoc, ta.prefix, ta.regnum, ta.sex, ta.birth_date, ta.namex, ta.activex, ta.category,
ta.electronic_id, ta.commentx notes, ta.disposal_date, ta.hps, ta.mating, ta.breed_1, ta.pct_1,
ta.breed_2, ta.pct_2, ta.breed_3, ta.pct_3, ta.breed_4, ta.pct_4, ta.Assoc_Pct, ta.blood_typed,
ta.blood_case_numb, ta.dna_tested, ta.dna_case_numb, ta.bangs_id, ta.recipient, ta.donor,
ta.commercial,
CASE
WHEN emb.bhid IS NULL AND
imp.bhid IS NULL AND
(ta.mating <> 'E' or ta.mating IS NULL) AND
(birth.embryo_transfer <> 'Y' or birth.embryo_transfer IS NULL) THEN 'N'
WHEN ta.birth_date IS NOT NULL AND
(emb.bhid IS NOT NULL OR
imp.bhid IS NOT NULL OR
ta.mating = 'E' OR
birth.embryo_transfer = 'Y') THEN 'W'
WHEN ta.birth_date IS NULL AND
(emb.bhid IS NOT NULL OR
imp.bhid IS NOT NULL OR
ta.mating = 'E' OR
birth.embryo_transfer = 'Y') THEN 'Y'
END AS embryo, ta.sire_code, ta.breedcode, ta.new_date, ta.new_user, ta.bhid, mgs.bhid AS mgs_bhid,
ta.location, ta.sire_bhid, ta.dam_bhid, ta.recip_bhid, ta.color AS color_code, ta.bh_disposal_code,
DAYS(CURRENT_DATE) - DAYS(ta.birth_date) AS age_days,
CAST(ROUND(((DAYS(CURRENT_DATE) - DAYS(ta.birth_date))/30.4375), 2) AS DECIMAL(8,2)) AS age_mos,
CAST(ROUND(((DAYS(CURRENT_DATE) - DAYS(ta.birth_date))/365.25 ), 2) AS DECIMAL(8,2)) AS age_yrs
FROM is3.animals ta
LEFT OUTER JOIN is3.color_defn tac ON ta.color=tac.bh_color_code
LEFT OUTER JOIN is3.disposal_defn tad ON ta.bh_disposal_code=tad.bh_disposal_code
LEFT OUTER JOIN is3.locations tal ON ta.location=tal.loc_id
JOIN is3.animals tc ON tc.bhid=ta.sire_bhid
LEFT OUTER JOIN is3.locations tcl ON tc.location=tcl.loc_id
LEFT OUTER JOIN is3.animals_priv tce ON tce.herd_owner_id=1 AND tce.bhid=tc.bhid
JOIN is3.animals td ON td.bhid=ta.dam_bhid
LEFT OUTER JOIN is3.locations tdl ON td.location=tdl.loc_id
LEFT OUTER JOIN is3.animals_priv tde ON tde.herd_owner_id=1 AND tde.bhid=td.bhid
JOIN is3.animals te ON te.bhid=ta.recip_bhid
LEFT OUTER JOIN is3.locations tel ON te.location=tel.loc_id
LEFT OUTER JOIN is3.animals_priv tee ON tee.herd_owner_id=1 AND tee.bhid=te.bhid
JOIN is3.animals mgs ON mgs.bhid=td.sire_bhid
LEFT OUTER JOIN is3.locations mgsl ON mgs.location=mgsl.loc_id
LEFT OUTER JOIN is3.animals_priv mgse ON mgse.herd_owner_id=1 AND mgse.bhid=mgs.bhid
LEFT OUTER JOIN is3.embryos emb on emb.bhid=ta.bhid
LEFT OUTER JOIN is3.et_implants imp on imp.bhid=ta.bhid
LEFT OUTER JOIN is3.birth birth on birth.bhid=ta.bhid ) t0
LEFT OUTER JOIN is3.animals_priv t1 ON t1.herd_owner_id=1 AND t1.bhid=t0.bhid
WHERE ((t0.location IN (SELECT loc_id FROM is3.locations
WHERE leftx BETWEEN (SELECT leftx FROM is3.locations WHERE loc_id=111111111) AND
(SELECT rightx FROM is3.locations WHERE loc_id=111111111))))
AND coalesce(t0.activex,'')<>'N'
AND (t0.controller=0 OR t0.controller=1)
AND (lower(t0.MGS_ID)<>'y' OR lower(t1.HERD_SIRE)='y')
Apr 10 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Bob Stearns wrote:
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 part of Vol 1 of the SQL Reference I am
unable 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 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.

SELECT DISTINCT 'jhough', '000111', t0.bhid
FROM (SELECT tal.namex AS locname, tac.color_desc AS color, tad.disposal_desc AS disposal, ta.tag2 AS tag,
ta.old_tag2 AS old_tag, tc.namex AS sire_name, tc.tattoo AS sire_tattoo, tc.assoc AS sire_assoc,
tc.prefix AS sire_prefix, tc.regnum AS sire_regnum, tce.herd_id AS sire_id, tcl.namex AS sire_locnam,
td.namex AS dam_name, td.tattoo AS dam_tattoo, td.assoc AS dam_assoc, td.prefix AS dam_prefix,
td.regnum AS dam_regnum, tde.herd_id AS dam_id, tdl.namex AS dam_locnam, mgs.namex AS mgs_name,
mgs.tattoo AS mgs_tattoo, mgs.assoc AS mgs_assoc, mgs.prefix AS mgs_prefix, mgs.regnum AS mgs_regnum,
mgse.herd_id AS mgs_id, mgsl.namex AS mgs_locnam, te.namex AS recip_name, te.tattoo AS recip_tattoo,
te.assoc AS recip_assoc, te.prefix AS recip_prefix, te.regnum AS recip_regnum,
tee.herd_id AS recip_id, tel.namex AS recip_locnam, ta.controller, ta.tattoo, ta.tattoo_loc,
ta.assoc, ta.prefix, ta.regnum, ta.sex, ta.birth_date, ta.namex, ta.activex, ta.category,
ta.electronic_id, ta.commentx notes, ta.disposal_date, ta.hps, ta.mating, ta.breed_1, ta.pct_1,
ta.breed_2, ta.pct_2, ta.breed_3, ta.pct_3, ta.breed_4, ta.pct_4, ta.Assoc_Pct, ta.blood_typed,
ta.blood_case_numb, ta.dna_tested, ta.dna_case_numb, ta.bangs_id, ta.recipient, ta.donor,
ta.commercial,
CASE
WHEN emb.bhid IS NULL AND
imp.bhid IS NULL AND
(ta.mating <> 'E' or ta.mating IS NULL) AND
(birth.embryo_transfer <> 'Y' or birth.embryo_transfer IS NULL) THEN 'N'
WHEN ta.birth_date IS NOT NULL AND
(emb.bhid IS NOT NULL OR
imp.bhid IS NOT NULL OR
ta.mating = 'E' OR
birth.embryo_transfer = 'Y') THEN 'W'
WHEN ta.birth_date IS NULL AND
(emb.bhid IS NOT NULL OR
imp.bhid IS NOT NULL OR
ta.mating = 'E' OR
birth.embryo_transfer = 'Y') THEN 'Y'
END AS embryo, ta.sire_code, ta.breedcode, ta.new_date, ta.new_user, ta.bhid, mgs.bhid AS mgs_bhid,
ta.location, ta.sire_bhid, ta.dam_bhid, ta.recip_bhid, ta.color AS color_code, ta.bh_disposal_code,
DAYS(CURRENT_DATE) - DAYS(ta.birth_date) AS age_days,
CAST(ROUND(((DAYS(CURRENT_DATE) - DAYS(ta.birth_date))/30.4375), 2) AS DECIMAL(8,2)) AS age_mos,
CAST(ROUND(((DAYS(CURRENT_DATE) - DAYS(ta.birth_date))/365.25 ), 2) AS DECIMAL(8,2)) AS age_yrs
FROM is3.animals ta
LEFT OUTER JOIN is3.color_defn tac ON ta.color=tac.bh_color_code
LEFT OUTER JOIN is3.disposal_defn tad ON ta.bh_disposal_code=tad.bh_disposal_code
LEFT OUTER JOIN is3.locations tal ON ta.location=tal.loc_id
JOIN is3.animals tc ON tc.bhid=ta.sire_bhid
LEFT OUTER JOIN is3.locations tcl ON tc.location=tcl.loc_id
LEFT OUTER JOIN is3.animals_priv tce ON tce.herd_owner_id=1 AND tce.bhid=tc.bhid
JOIN is3.animals td ON td.bhid=ta.dam_bhid
LEFT OUTER JOIN is3.locations tdl ON td.location=tdl.loc_id
LEFT OUTER JOIN is3.animals_priv tde ON tde.herd_owner_id=1 AND tde.bhid=td.bhid
JOIN is3.animals te ON te.bhid=ta.recip_bhid
LEFT OUTER JOIN is3.locations tel ON te.location=tel.loc_id
LEFT OUTER JOIN is3.animals_priv tee ON tee.herd_owner_id=1 AND tee.bhid=te.bhid
JOIN is3.animals mgs ON mgs.bhid=td.sire_bhid
LEFT OUTER JOIN is3.locations mgsl ON mgs.location=mgsl.loc_id
LEFT OUTER JOIN is3.animals_priv mgse ON mgse.herd_owner_id=1 AND mgse.bhid=mgs.bhid
LEFT OUTER JOIN is3.embryos emb on emb.bhid=ta.bhid
LEFT OUTER JOIN is3.et_implants imp on imp.bhid=ta.bhid
LEFT OUTER JOIN is3.birth birth on birth.bhid=ta.bhid ) t0
LEFT OUTER JOIN is3.animals_priv t1 ON t1.herd_owner_id=1 AND t1.bhid=t0.bhid
WHERE ((t0.location IN (SELECT loc_id FROM is3.locations
WHERE leftx BETWEEN (SELECT leftx FROM is3.locations WHERE loc_id=111111111) AND
(SELECT rightx FROM is3.locations WHERE loc_id=111111111))))
AND coalesce(t0.activex,'')<>'N'
AND (t0.controller=0 OR t0.controller=1)
AND (lower(t0.MGS_ID)<>'y' OR lower(t1.HERD_SIRE)='y')
Apr 10 '06 #2

P: n/a
Some selected rows must be duplicatted with the rows are already in the
table that you want to insert.

Apr 10 '06 #3

P: n/a
Tonkuma wrote:
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.
Apr 10 '06 #4

P: n/a
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

Apr 11 '06 #5

P: n/a
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

Apr 11 '06 #6

P: n/a
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

Apr 11 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.