BAQ Invalid Select

Epicor ERP 10
,
1

Can someone help with this BAQ im trying to do an inner subquery to put in the top level query with a column of free stock in two warehouses but im getting the group by error this is subquery 3

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */select 
	[Part].[RefCategory] as [Part_RefCategory],
	[Part].[family_c] as [Part_family_c],
	(0) as [Calculated_KWE],
	[SubQuery2].[PartWhse1_OnHandQty] as [PartWhse1_OnHandQty],
	[Part].[PartNum] as [Part_PartNum],
	(sum( PartWhse.OnHandQty - PartWhse.DemandQty )) as [Calculated_freestock],
	[SubQuery3].[Calculated_Untested] as [Calculated_Untested]
from Erp.Part as Part
inner join Erp.PartWhse as PartWhse on 
	Part.Company = PartWhse.Company
	and Part.PartNum = PartWhse.PartNum
inner join  (select 
	[PartWhse1].[PartNum] as [PartWhse1_PartNum],
	[PartWhse1].[OnHandQty] as [PartWhse1_OnHandQty]
from Erp.PartWhse as PartWhse1
where (PartWhse1.WarehouseCode = 'POLAND'))  as SubQuery2 on 
	Part.PartNum = SubQuery2.PartWhse1_PartNum
inner join  (select 
	[PartWhse2].[PartNum] as [PartWhse2_PartNum],
	(sum(PartWhse2.OnHandQty)) as [Calculated_Untested]
from Erp.PartWhse as PartWhse2
where (PartWhse2.WarehouseCode = 'INSP'  or PartWhse2.WarehouseCode = 'RCV')
group by [PartWhse2].[PartNum])  as SubQuery3 on 
	Part.PartNum = SubQuery3.PartWhse2_PartNum
where (Part.Company = @CurrentCompany)
 and PartWhse.WarehouseCode <> 'Poland'  and PartWhse.WarehouseCode <> 'QCDMR'  and Part.Shown_c = TRUE  and PartWhse.WarehouseCode <> 'SHIP'  and PartWhse.WarehouseCode <> 'INSP'  and PartWhse.WarehouseCode <> 'INSPU5'

group by [Part].[RefCategory],
	[Part].[family_c],
	[SubQuery2].[PartWhse1_OnHandQty],
	[Part].[PartNum]
order by Part.PartNum
2

image
image1567×780 40.7 KB

3

It is contained in the aggregate function (sum), so you need to put it in a group by. In your image, the row in the Display Column(s) area has a cell for Group By. Check that box for Calculated_Untested and it should work.

4

@Banderson did you fix my baq ?

WebsiteStock.baq (43.2 KB)

5

I couldn’t import the baq cause is on a latter version but @GThom is right.

image

Thanks,

6

no. I added ``` to the beginning and end of your code block so people could read it easier.