Trying to convert this sql statement for the BAQ to like it.
Try making your data type “Decimal”.
you don’t need the AS unitcost
That is the name of the column. Which you have defined above.
What Ken says is correct, you don’t need the last part.
Thank you all for the insight. After doing all that I now get this…
What keyword set?
post what is in the query phrase please.
sum(PartCost.AvgLaborCost + PartCost.AvgBurdenCost + PartCost.AvgMaterialCost + PartCost.AvgSubContCost + PartCost.AvgMtlBurCost)
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!
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.
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)
try this. not sure you even need the group by and sum though…
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]
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”.
Lol! Derp. Thank you very much.
no problem.
experiment around with the BAQ designer, I have a sense that you are new to the toolset.
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.
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
Pardon the ignorance but how do you mark it as solved?