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.