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.