CODEWITHSUNDEEP
javasqleclipsedb2
Kishore_2021
Kishore_2021

Reputation: 689

DISTINCT not eliminating the duplicate records- Why?

DISTINCT is not working as usual on DB2 database query

SELECT
    DISTINCT PF1
FROM
    (
        SELECT
                DISTINCT PF1
            FROM
                RELATIONS
        UNION
        SELECT
                DISTINCT PF2
            FROM
                RELATIONS
    )
WHERE
    PF1 NOT IN (
        SELECT
                DISTINCT NAME
            FROM
                REFERENCES
            WHERE
                TYPE = 'F'
    );

This query is giving duplicate results as

C1:PROV
C1:PROV
WK:HEADER
WK:HEADER
WK:HEADER
etc

What thing/setting is responsible for this abnormal behavior??

FYI: Same query gives correct result on another machine's database.

Edited Here are the table's structure

CREATE TABLE RELATIONS (
    PF1 VARCHAR(54) NOT NULL,
    PF1TXT VARCHAR(50),
    PF2 VARCHAR(54) NOT NULL,
    SEQ DOUBLE NOT NULL,
    PF2TXT VARCHAR(50),
  UNIQUE (PF1, SEQ, PF2)
);

CREATE TABLE REFERENCES (
    NAME1 VARCHAR(54) NOT NULL,
    NAMETXT VARCHAR(50) NOT NULL,
    NAME VARCHAR(54) NOT NULL,
    TYPE VARCHAR(1) NOT NULL,
    UNIQUE (NAME1, NAMETXT, NAME, TYPE) 
);

Upvotes: 1

Views: 122

Answers (2)

bhamby
bhamby

Reputation: 15450

UNION should already be eliminating duplicates. You could re-structure your query a little bit, and it should work. I only made the REFS CTE, in case your actual code was more complicated than that, as to reduce duplication:

WITH REFS (NAME) AS (
    SELECT NAME
    FROM REFERENCES
    WHERE TYPE = 'F'
)

SELECT PF1
FROM RELATIONS
WHERE PF1 NOT IN (
    SELECT NAME FROM REFS
)

    UNION 

SELECT PF2
FROM RELATIONS
WHERE PF2 NOT IN (
    SELECT NAME FROM REFS
)

I tested this on my DB2 for LUW 9.7, and it worked correctly for me.

Upvotes: 2

TioDavid
TioDavid

Reputation: 36

Not sure, but you could give an alias to your derived table. In my test it worked.

SELECT DISTINCT PF1 FROM (
    SELECT DISTINCT PF1 FROM RELATIONS
        UNION SELECT DISTINCT PF2 FROM RELATIONS
    )  as rel -- the name to derived table
WHERE PF1 NOT IN (
    SELECT DISTINCT NAME FROM REFERENCES
        WHERE TYPE = 'F');

Upvotes: 2

Related Questions