BAQ Subquery to return one row (TOP 1)

Epicor ERP 10
1

I’m thinking I’m missing something pretty easy here. I’m trying to use the TOP function in a subquery, but it’s not returning the top one for each link back to the main query. To simplify my example, I’ve got a main query against Part where TypeCode = “P”, and I want to see the PartBin record with the HIGHEST OnHandQty. So, I’ve got a subquery on PartBin, sort by PartBin.OnHandQty (Descending), and on the Subquery Options I specify it as a TOP query with “1” in the Rows Number.

To link the query back to the main, I link on Part Num. When I run the query, I get ONE ROW period - just the part that happens to have the highest on-hand inventory. I want ALL parts with inventory, but to see which bin has the highest for each of them.

The SQL it’s showing looks like this:

select 
	[Part].[PartNum] as [Part_PartNum],
	[SubQuery2].[PartBin_WarehouseCode] as [PartBin_WarehouseCode],
	[SubQuery2].[PartBin_BinNum] as [PartBin_BinNum],
	[SubQuery2].[PartBin_OnhandQty] as [PartBin_OnhandQty]
from Erp.Part as Part
inner join  (select top (1)  
	[PartBin].[PartNum] as [PartBin_PartNum],
	[PartBin].[WarehouseCode] as [PartBin_WarehouseCode],
	[PartBin].[BinNum] as [PartBin_BinNum],
	[PartBin].[OnhandQty] as [PartBin_OnhandQty]
from Erp.PartBin as PartBin
order by PartBin.OnhandQty Desc)  as SubQuery2 on 
	Part.PartNum = SubQuery2.PartBin_PartNum
where (Part.TypeCode = 'P')
order by Part.PartNum

The problem is that the inner join is just returning one row. I really need a where clause on the inside of the inner join, so I get one row per part.

Am I missing something?

Thanks!

2

The subquery you have there returns just one row. You need a group by along with a max aggregate function there. See below and attached.

select 
	[Part].[Company] as [Part_Company],
	[Part].[PartNum] as [Part_PartNum],
	[sqOnhand].[PartBin_WarehouseCode] as [PartBin_WarehouseCode],
	[sqOnhand].[PartBin_BinNum] as [PartBin_BinNum],
	[sqOnhand].[Calculated_MaxOnHand] as [Calculated_MaxOnHand]
from Erp.Part as Part
inner join  (select 
	[PartBin].[Company] as [PartBin_Company],
	[PartBin].[PartNum] as [PartBin_PartNum],
	[PartBin].[WarehouseCode] as [PartBin_WarehouseCode],
	[PartBin].[BinNum] as [PartBin_BinNum],
	(max(PartBin.OnhandQty)) as [Calculated_MaxOnHand]
from Erp.PartBin as PartBin
group by [PartBin].[Company],
	[PartBin].[PartNum],
	[PartBin].[WarehouseCode],
	[PartBin].[BinNum])  as sqOnhand on 
	Part.Company = sqOnhand.PartBin_Company
	and Part.PartNum = sqOnhand.PartBin_PartNum
where (Part.TypeCode = 'P')
order by Part.PartNum

Test123.baq (25.6 KB)

3

This seems like a great time for you to get into windowed functions.

3 Likes
4

Thanks for the post. This led me to the answer.

What I needed was for the subquery to return all the data but with a rank by sort order within Part by quantity. That meant rank #1 for each part was the highest bin quantity for that part. The main query then just had to grab the subquery row with Rank #1.

Subquery calculated field RANK:

ROW_NUMBER() over (PARTITION BY PartBin.PartNum ORDER BY PartBin.OnhandQty DESC)

Then in the main query, just added a condition on the subquery where Calculated_Rank = 1