Toad World® Forums

PL/SQL question: Displaying two columns for duplicate records


#1

Hi everyone. Please bear with me as I’m just beginning to learn PL/SQL.

We have a forecasting data table with multiple columns. I’m trying to find duplicated combos of part number and site, after having grouped them. Technically, one part number should only be assigned to one plant, but things happen, I guess.

Here’s the initial code:
SELECT partnumber, count( * ) FROM
(
SELECT ft.partnumber, ft.plant
FROM forecasttable ft
GROUP BY ft.partnumber, ft.plant
ORDER BY ft.partnumber, ft.plant
)
GROUP BY part number
HAVING COUNT( * ) <> 1
ORDER BY 1

The “inside” select will group the actual forecast table into the part number and plant “combos”, since there can be multiple records there (for different months). The “outside” select will then show how many of those grouped combos actually have part numbers with multiple plants.

So this produces a list with part number and the count (all different than 1).
Like:
Part number - Count(*)
123456 - 2
234567 - 2
345678 - 2

Now, I want to also include the plant names involved in these duplicates, so the list becomes like this:
Part number - Plant - Count(*)
123456 - Plant 1 - 2
123456 - Plant 2 - 2
234567 - Plant 1 - 2
234567 - Plant 3 - 2
345678 - Plant 4 - 2
345678 - Plant 5 - 2

But when I try to add plant to the code, like this:
SELECT partnumber, plant, count( * ) FROM
(
SELECT ft.partnumber, ft.plant
FROM forecasttable ft
GROUP BY ft.partnumber, ft.plant
ORDER BY ft.partnumber, ft.plant
)
GROUP BY part number, plant
HAVING COUNT( * ) <> 1
ORDER BY 1

It rightly produces zero results, since grouping both part number and plant will not produce any duplicates, right? Only part number is duplicated.
However, if I remove plant from the second GROUP BY, I get a ORA-00979 (not a GROUP BY expression) error, since plant would need to be in the group statement.

Any suggestions on how to also include plant in this duplicated part number list?

Thank you,
José M.


#2

I worked up something similar using DBA_TABLES. Move your group query up into a “with” clause, and then you can join that back to your table.

with dups as 
  (select table_name, count(*)
   from dba_tables
   group by table_name
   having count(*) > 1)
select d.*, t.* 
from   dba_tables t, dups d
where  t.table_name = d.table_name

#3

Hi. Thank you so much for the prompt response.
Your answer was what I was looking for.
I did only have to group by and order the two fields after the “where” section.

Thank you!