Error when sum of labor details

Epicor ERP 10
1

image
image.png921Ă—597 30.2 KB

Trying to convert this sql statement for the BAQ to like it.

2

Try making your data type “Decimal”.

1 Like
3

you don’t need the AS unitcost

That is the name of the column. Which you have defined above.

2 Likes
4

What Ken says is correct, you don’t need the last part.

1 Like
5

Thank you all for the insight. After doing all that I now get this…
image
What keyword set?

6

post what is in the query phrase please.

7

sum(PartCost.AvgLaborCost + PartCost.AvgBurdenCost + PartCost.AvgMaterialCost + PartCost.AvgSubContCost + PartCost.AvgMtlBurCost)

8

image
image.png920Ă—603 24.7 KB

9

I mean the query phrase on the general tab of the BAQ.

copy and paste, not a screenshot please.

Capture
Capture.JPG911Ă—690 43.9 KB

10

select
[PartWhse].[PartNum] as [PartWhse_PartNum],
[PartWhse].[ManualABC] as [PartWhse_ManualABC],
[PartWhse].[WarehouseCode] as [PartWhse_WarehouseCode],
(sum(PartCost.AvgLaborCost + PartCost.AvgBurdenCost + PartCost.AvgMaterialCost + PartCost.AvgSubContCost + PartCost.AvgMtlBurCost)) as [Calculated_PartCosts]
from Erp.PartWhse as PartWhse
cross join Erp.PartCost as PartCost

My bad. Thanks!

11

Looks like your table joins are not set correctly.

plus your using an aggregation (sum) you need to select group by on the partnum,manualabc, and warehousecode.

12

You, sir are correct. Now I have to figure out the group by function since I cannot type it in the field (That would be a billion times easier)

13

try this. not sure you even need the group by and sum though…

Capture
Capture.JPG1478Ă—920 138 KB
Capture2
Capture2.JPG1862Ă—429 67.6 KB

query phrase will look like:

select 
	[PartWhse].[PartNum] as [PartWhse_PartNum],
	[PartWhse].[WarehouseCode] as [PartWhse_WarehouseCode],
	[PartWhse].[ManualABC] as [PartWhse_ManualABC],
	(SUM(PartCost.AvgLaborCost + PartCost.AvgBurdenCost + PartCost.AvgMaterialCost + PartCost.AvgSubContCost + PartCost.AvgMtlBurCost)) as [Calculated_PartCosts]
from Erp.PartCost as PartCost
inner join Erp.PartWhse as PartWhse on 
	PartWhse.PartNum = PartCost.PartNum
And
	PartWhse.Company = PartCost.Company

group by [PartWhse].[PartNum],
	[PartWhse].[WarehouseCode],
	[PartWhse].[ManualABC]
14

That’s exactly what my SQL look like in my Operations Studio but trying to get epicor to do so is difficult. I have the joins, now the group by. The only group by options it gives me is “Rollups”, “Cube” and “grouping sets”.

15

look at this screen again. The group by is in the display column area.

Capture
Capture.JPG1862Ă—429 70.8 KB

1 Like
16

Lol! Derp. Thank you very much.

17

no problem.

experiment around with the BAQ designer, I have a sense that you are new to the toolset.

18

Very much so. I usually pull data out through sql and get that code down but since I can’t simple copy and paste what I have, it is a learning curve.

19

all good. make sure you make the topic solved so folks don’t respond with possible solutions.

I still think the group by is not needed as the part cost table only has one part in there.(it should) per company

1 Like
20

Pardon the ignorance but how do you mark it as solved?