Duplicates in a Query

Here is a way to find out why you have two rows in a select, when you only expect one.

In good database design you expect that a unique ID is giving you only one record. But many tables Oracle E-Business Suite has  composite primary keys – like:

– mtl_system_items – pk = iventory_item_id + organization_id

– per_all_people_f – pk = person_id + effective_start_date + effective_end_date

– FND_CURRENCIES_TL – pk = CURRENCY_CODE + LANGUAGE

So what to do when you have a query with duplicates. First try to reduce the query to show as few row as possible, the optimum is 2 rows. Then copy the sql into a new xls4OracleSQL sheet.

– Set the Layout = V for vertical.

– Replace the hole select part with select *

– Convert table to range, if data is in a table.

– add a column after the last column, formula “=A3=B3”. This will show TRUE if values are the same and FALSE when values are different, and we want to find those that are different.

– copy formula to all the other lines

– insert table or apply filter

– filter on false

You can now see the columns that differs, and this should enable you to add the needed where clauses to get rid off the duplicates.

This entry was posted in Applications development, SQL, xls4OracleSQL. Bookmark the permalink.