DISTINCT changes data to eliminate duplicates?

[RD]

This is odd:

I have an ODBC connection to an Oracle db. In this one table I see that almost
every single record is duplicated resulting in nearly half a million rows. I
thought I'd run a SELECT DISTINCT query against it so I could work with a
smaller pool of records. No dice. I still see two records but two of the
fields have "magically" changed data!

There are 33 fields but I'll just demo with the first 4 as all the fields
contain duplicate data (both before and after DISTINCT). The first 4 fields
are: ID, ADR_TYP_ID, EFF_BGN_DT and ADR_TYP_CD. Before DISTINCT these fields
are duplicated. After DISTINCT, ADR_TYP_ID and ADR_TYP_CD get changed in one of
the records! I have never seen this before.

ID ADR_TYP_ID EFF_BGN_DT ADR_TYP_CD
1183 1870 11/30/1998 HA
1183 1870 11/30/1998 HA

After DISTINCT
ID ADR_TYP_ID EFF_BGN_DT ADR_TYP_CD
1183 1870 11/30/1998 HA
1183 1871 11/30/1998 ML

BTW ... DISTINCTROW returns fully duplicated records exactly as they appear in
the table.

Anyone else ever see this? Knows what might cause it? How to fix it?

Thanks for any help,
RD

 

[louisjohnphillips]

This is odd:

I have an ODBC connection to an Oracle db. In this one table I see that almost
every single record is duplicated resulting in nearly half a million rows. I
thought I'd run a SELECT DISTINCT query against it so I could work with a
smaller pool of records. No dice. I still see two records but two of the
fields have "magically" changed data!

There are 33 fields but I'll just demo with the first 4 as all the fields
contain duplicate data (both before and after DISTINCT). The first 4 fields
are: ID, ADR_TYP_ID, EFF_BGN_DT and ADR_TYP_CD. Before DISTINCT these fields
are duplicated. After DISTINCT, ADR_TYP_ID and ADR_TYP_CD get changed in one of
the records! I have never seen this before.

ID ADR_TYP_ID EFF_BGN_DT ADR_TYP_CD
1183 1870 11/30/1998 HA
1183 1870 11/30/1998 HA

After DISTINCT
ID ADR_TYP_ID EFF_BGN_DT ADR_TYP_CD
1183 1870 11/30/1998 HA
1183 1871 11/30/1998 ML

BTW ... DISTINCTROW returns fully duplicated records exactly as they appear in
the table.

Anyone else ever see this? Knows what might cause it? How to fix it?

Thanks for any help,
RD

Yes, this is odd. A single table has 500,000 rows where the data is
duplicated. Shouldn't each row in the table somehow be unique?

Is it possible the "one table" is really the product more than one
table? Maybe a Cartesion product exists between two tables. That
would account for what appears to be duplicates that cannot be
eliminated by a DISTINCT.

 

[RD]

Yes, this is odd. A single table has 500,000 rows where the data is
duplicated. Shouldn't each row in the table somehow be unique?

Is it possible the "one table" is really the product more than one
table? Maybe a Cartesion product exists between two tables. That
would account for what appears to be duplicates that cannot be
eliminated by a DISTINCT.

Nope. It's just a table. This turns out to be an Access bug, I think. I can
open up a view of this table using Benthic software's Golden32 and the records
resolve correctly. In Access, even when I select HA for the ADR_TYP_CD at least
one ML shows up (and vice versa).

I ended up having to export a recorset from Benthic and import that into Access
in order to accurately display the data.

If I learn any more about this I'll post it in here.

Regards,
RD