CODEWITHSUNDEEP
sqloracle-databaseoracle-fusion-middlewareoracle-fusion-apps
Seeker
Seeker

Reputation: 11

Is there a way to eliminate duplicate data without using 'distinct' or 'unique' clause?

I cannot use Distinct or Unique clause due to Organisation restrictions. Is there a way to modify the code so that no duplicate values are shown? My code is listed below:

`SELECT PER_PERSON_NAMES_F.TITLE,
               PER_PERSON_NAMES_F.FIRST_NAME,
               PER_PERSON_NAMES_F.LAST_NAME,
               PER_PERSON_NAMES_F.FULL_NAME,
               PER_PERSON_NAMES_F.DISPLAY_NAME,
FROM PER_PERSON_NAMES_F,PER_ALL_PEOPLE_F, PER_PERSONS
WHERE PER_PERSON_NAMES_F.PERSON_ID=PER_ALL_PEOPLE_F.PERSON_ID
AND PER_PERSON_NAMES_F.PERSON_ID=PER_PERSONS.PERSON_ID
AND PER_ALL_PEOPLE_F.PERSON_ID=PER_PERSONS.PERSON_ID
AND name_type='GLOBAL'
ORDER BY PER_PERSON_NAMES_F.FULL_NAME.`

Upvotes: 1

Views: 233

Answers (5)

Jon Heller
Jon Heller

Reputation: 36912

Another alternative is to use UNION to force distinctness.

SELECT PER_PERSON_NAMES_F.TITLE,
               PER_PERSON_NAMES_F.FIRST_NAME,
               PER_PERSON_NAMES_F.LAST_NAME,
               PER_PERSON_NAMES_F.FULL_NAME,
               PER_PERSON_NAMES_F.DISPLAY_NAME,
FROM PER_PERSON_NAMES_F,PER_ALL_PEOPLE_F, PER_PERSONS
WHERE PER_PERSON_NAMES_F.PERSON_ID=PER_ALL_PEOPLE_F.PERSON_ID
AND PER_PERSON_NAMES_F.PERSON_ID=PER_PERSONS.PERSON_ID
AND PER_ALL_PEOPLE_F.PERSON_ID=PER_PERSONS.PERSON_ID
AND name_type='GLOBAL'
UNION
SELECT NULL,NULL,NULL,NULL,NULL FROM DUAL WHERE 1 = 0
ORDER BY PER_PERSON_NAMES_F.FULL_NAME

This solution is stupid, which is perhaps an advantage here. It tells future developers that you knowingly did something obviously stupid, and it will make them wonder why you did it. Because you can't add a comment to your code that says -- I was forced to do this because my organization is run by morons.

Upvotes: 0

1L0pth
1L0pth

Reputation: 58

The tables you mentioned are Oracle ERP tables, these tables (PER_PERSON_NAMES_F,PER_ALL_PEOPLE_F) are date based so you have to add a condition on the date effectivity

however if it is your own created table and just copied same table names, then using a group by as suggested by MTO or J.Heller would be the way to go

SELECT
ppnf.TITLE,
ppnf.FIRST_NAME,
ppnf.LAST_NAME,
ppnf.FULL_NAME,
ppnf.DISPLAY_NAME
FROM PER_PERSONS pp
INNER JOIN PER_PERSON_NAMES_F ppnf ON ppnf.PERSON_ID = pp.PERSON_ID
-- you are not using this table in your query so it can be excluded
--INNER JOIN PER_ALL_PEOPLE_F papf ON papf.PERSON_ID = ppnf.PERSON_ID
WHERE
ppnf.NAME_TYPE = 'GLOBAL'
TRUNC(SYSDATE) BETWEEN ppnf.EFFECTIVE_START_DATE AND ppnf.EFFECTIVE_END_DATE
-- if you need the per_all_people_f table 
-- AND TRUNC(SYSDATE) BETWEEN ppnf.EFFECTIVE_START_DATE AND ppnf.EFFECTIVE_END_DATE

Upvotes: 0

Arka_Deep
Arka_Deep

Reputation: 11

SELECT PER_PERSON_NAMES_F.TITLE,
   PER_PERSON_NAMES_F.FIRST_NAME,
   PER_PERSON_NAMES_F.LAST_NAME,
   PER_PERSON_NAMES_F.FULL_NAME,
   PER_PERSON_NAMES_F.DISPLAY_NAME
  FROM PER_PERSON_NAMES_F,PER_ALL_PEOPLE_F, PER_PERSONS
  WHERE PER_PERSON_NAMES_F.PERSON_ID=PER_ALL_PEOPLE_F.PERSON_ID
AND PER_PERSON_NAMES_F.PERSON_ID=PER_PERSONS.PERSON_ID
AND PER_ALL_PEOPLE_F.PERSON_ID=PER_PERSONS.PERSON_ID
AND name_type='GLOBAL'
GROUP BY PER_PERSON_NAMES_F.TITLE,
     PER_PERSON_NAMES_F.FIRST_NAME,
     PER_PERSON_NAMES_F.LAST_NAME,
     PER_PERSON_NAMES_F.FULL_NAME,
     PER_PERSON_NAMES_F.DISPLAY_NAME
ORDER BY PER_PERSON_NAMES_F.FULL_NAME;

Used Group by clause in the above query

Upvotes: -1

MT0
MT0

Reputation: 168530

Use GROUP BY:

SELECT n.TITLE,
       n.FIRST_NAME,
       n.LAST_NAME,
       n.FULL_NAME,
       n.DISPLAY_NAME
FROM   PER_PERSON_NAMES_F n
       INNER JOIN PER_ALL_PEOPLE_F ap
       ON n.PERSON_ID=ap.PERSON_ID
       INNER JOIN PER_PERSONS p
       ON  n.PERSON_ID=p.PERSON_ID
       AND ap.PERSON_ID=p.PERSON_ID
WHERE  name_type='GLOBAL'
GROUP BY
       n.TITLE,
       n.FIRST_NAME,
       n.LAST_NAME,
       n.FULL_NAME,
       n.DISPLAY_NAME
ORDER BY n.FULL_NAME

Or, since PERSON_ID appears to be the primary key for a person:

SELECT MAX(n.TITLE) as title,
       MAX(n.FIRST_NAME) as first_name,
       MAX(n.LAST_NAME) as last_name,
       MAX(n.FULL_NAME) as full_name,
       MAX(n.DISPLAY_NAME) as display_name
FROM   PER_PERSON_NAMES_F n
       INNER JOIN PER_ALL_PEOPLE_F ap
       ON n.PERSON_ID=ap.PERSON_ID
       INNER JOIN PER_PERSONS p
       ON  n.PERSON_ID=p.PERSON_ID
       AND ap.PERSON_ID=p.PERSON_ID
WHERE  name_type='GLOBAL'
GROUP BY
       n.PERSON_ID
ORDER BY n.FULL_NAME

Or use the ROW_NUMBER analytic function:

SELECT n.TITLE,
       n.FIRST_NAME,
       n.LAST_NAME,
       n.FULL_NAME,
       n.DISPLAY_NAME
FROM   (
  SELECT n.TITLE,
         n.FIRST_NAME,
         n.LAST_NAME,
         n.FULL_NAME,
         n.DISPLAY_NAME,
         ROW_NUMBER() OVER (PARTITION BY n.person_id) AS rn
  FROM   PER_PERSON_NAMES_F n
         INNER JOIN PER_ALL_PEOPLE_F ap
         ON n.PERSON_ID=ap.PERSON_ID
         INNER JOIN PER_PERSONS p
         ON  n.PERSON_ID=p.PERSON_ID
         AND ap.PERSON_ID=p.PERSON_ID
  WHERE  name_type='GLOBAL'
) n
WHERE    n.rn = 1
ORDER BY n.FULL_NAME

Alternatively, since the duplicates are probably coming from a one-to-many join then don't JOIN the tables use EXISTS instead:

SELECT n.TITLE,
       n.FIRST_NAME,
       n.LAST_NAME,
       n.FULL_NAME,
       n.DISPLAY_NAME
FROM   PER_PERSON_NAMES_F n
WHERE  EXISTS(
         SELECT 1
         FROM   PER_ALL_PEOPLE_F ap
                INNER JOIN PER_PERSONS p
                ON  ap.PERSON_ID=p.PERSON_ID
         WHERE  n.PERSON_ID=ap.PERSON_ID
         AND    p.name_type='GLOBAL'
       )
ORDER BY n.FULL_NAME

Upvotes: 3

Littlefoot
Littlefoot

Reputation: 143063

What does

due to Organisation restrictions

mean?

One option is to put all columns returned by select statement into group by clause:

  SELECT per_person_names_f.title,
         per_person_names_f.first_name,
         per_person_names_f.last_name,
         per_person_names_f.full_name,
         per_person_names_f.display_name
    FROM per_person_names_f, per_all_people_f, per_persons
   WHERE     per_person_names_f.person_id = per_all_people_f.person_id
         AND per_person_names_f.person_id = per_persons.person_id
         AND per_all_people_f.person_id = per_persons.person_id
         AND name_type = 'GLOBAL'
GROUP BY per_person_names_f.title,
         per_person_names_f.first_name,
         per_person_names_f.last_name,
         per_person_names_f.full_name,
         per_person_names_f.display_name
ORDER BY per_person_names_f.full_name

Upvotes: 0

Related Questions