Selecting first record name from duplicate values-CodePudding

I'm writing a query where I want to select the PartNo, Description, Model, and AvaQty from a view.

But in our system, there are slightly different Descriptions or Models for the same Part Number.

As an example, Part A has description like This is Part A and also there is another record for description like This is Part Aa

In my query, I want to remove duplicates and Sum the Ava Qty and show. But because the descriptions and model are different for the same part numbers I'm getting more duplicate values in the final report.

This is my current code.

SELECT DISTINCT PART_NO as PartNo, 
ad.INVENTORY_PART_API.Get_Description(contract,part_no) as PartDescription,
ad.Inventory_Product_Family_API.Get_Description(ad.Inventory_Part_API.Get_Part_Product_Family(CONTRACT, PART_NO)) as PartModel,
SUM( QTY_ONHAND - QTY_RESERVED) as AvaQty 
FROM ad.INVENTORY_PART_IN_STOCK_UIV 
WHERE CONTRACT is not null and 
upper(ad.Sales_Part_API.Get_Catalog_Group(CONTRACT, PART_NO)) = upper('SPAM')OR 
upper(ad.Sales_Part_API.Get_Catalog_Group(CONTRACT, PART_NO)) = upper('OTOA') 
GROUP BY PART_NO,
ad.INVENTORY_PART_API.Get_Description(contract,part_no),
ad.Inventory_Product_Family_API.Get_Description(ad.Inventory_Part_API.Get_Part_Product_Family(CONTRACT, PART_NO))

So I get 14623 counts of records, 46 records are duplicated because the description or model was different from each other. So is there any way to get this without duplicating it?

I tried without selecting Description and Model. Selected Only PartNo and Qty. Then records come without duplicate records. Need to know is there any way to select PartNo and then assign description and model from the duplicate values first record or something and sum of qty. Thanks

CodePudding user response：

You want one result row per part number, so group by part number. There can be different descriptions per part number, so decide which to show. Below, I am showing the first in alphabet (MIN). You can also use MAX to show the latest or LISTAGG to show them all.

SELECT
  part_no AS partno, 
  MIN(ad.inventory_part_api.get_description(contract,part_no)) AS partdescription,
  MIN(ad.inventory_product_family_api.get_description(ad.inventory_part_api.get_part_product_family(contract, part_no))) AS partmodel,
  SUM(qty_onhand - qty_reserved) AS avaqty 
FROM ad.inventory_part_in_stock_uiv 
WHERE contract IS NOT NULL 
AND UPPER(ad.sales_part_api.get_catalog_group(contract, part_no)) IN ('SPAM', 'OTOA')
GROUP BY part_no
ORDER BY part_no;

As to your WHERE clause: You had WHERE (contract IS NOT NULL AND catgrp = 'SPAM') OR (catgrp = 'OTOA'), because AND has precedence over OR. In my query it is WHERE (contract IS NOT NULL) AND (catgrp = 'SPAM' OR catgrp = 'OTOA'). I suppose this is what you really want. Otherwise change it back.

CodePudding user response：

I would solve this task by using analytic functions. For example, SUM(qty_onhand - qty_reserved) OVER (PARTITION BY PART_NO). In this case you don't have to use GROUP BY.