Hello,
I would like to have the Buyer name and ID on this BAQ I have. I did not make this BAQ. I can connect via Company and I do get the Buyer ID and Name in my results, but it is also giving me a “Severity: Error, Text: Bad SQL statement.” warning and I have to ‘x’ it otherwise it just hangs. Any advice? What is wrong with my connections?
Thank you!
Buyer is on the partclass. Are you linking from there?
EDIT: You can also get from the Part, so you may have to add it both places and then use a calculated field to sow the correct one.
Buyer is also on the PartPlant in case you don’t have buyers by PartClass.
You then add the PurAgent table from there to get the name.
your error is due to incomplete setup either on a join or selection criteria.
like @knash said, what condition is currently on your part table? Do you have any incomplete relations setup between tables?
I would remove the PerCon table - it’s not joined to anything an would be creating multiple rows.
Assuming you are single Site - Join the PartPlant to the Part and link the with an inner join PurAgent
Bring in a second purAgent table and inner join it to the PartClass
Hope this helps
Hello Everyone,
Thank you for all your suggestions. I was able to get rid of that SQL error and produce a Buyer ID and a Buyer Name on my report.
Now the issue is it’s reporting incorrect information. For example, it’s saying part ABC was purchased by Mr. Smith, but when you actually look at the part it has a different buyer.
Assuming you are getting PurAgent from PartClass and PartPlant you will need to probably use Coalesce something like below. That checks for null and uses the first non null result.
Coalesce(PurAgent.BuyerName,PurAgentPartClass.BuyerName)
In the Calculated fields, correct?
On mine it is PerAgent_BuyerID and PerAgent_Name
So I did this: Coalesce(PurAgent.BuyerID,PurAgent.BuyerID)
But it only created another column with the same inaccurate buyers. I figure I did something wrong here.
Do you have PurAgent outer joined twice like @LarsonSolutions showed it?
If so then one of them will have an alternate table name. They just add a 1, but you can also make it more descriptive like he did PurAgent_PartClass.
The one from PartPlant will be first then the one from PartClass.
I think it works! Thank you very much everyone.
@mlp - if you want to know who is buying the materials, you need to add in PO tables
This query below would show who the buyer is that is buying a part vs who “should be” based on the part class or Part Plant tables.
OK. I tried that but I am having difficulty weaving it in with the other tables I need.
What I have now is okay, it being pulled from the Part Maintenance to tell me who the buyer is. This is a BAQ made by someone else that I am trying to add the Buyer information.
But we noticed that I am still getting duplicates for other columns, this time when it concerns the Bin. Which messes up the calculation.
StockValTest2.baq (40.3 KB)
select
[PartBin].[WarehouseCode] as [PartBin_WarehouseCode],
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[PartBin].[BinNum] as [PartBin_BinNum],
[PartBin].[OnhandQty] as [PartBin_OnhandQty],
[PartCost].[StdBurdenCost] as [PartCost_StdBurdenCost],
[PartCost].[StdLaborCost] as [PartCost_StdLaborCost],
[PartCost].[StdMaterialCost] as [PartCost_StdMaterialCost],
[PartCost].[StdMtlBurCost] as [PartCost_StdMtlBurCost],
[PartCost].[StdSubContCost] as [PartCost_StdSubContCost],
(PartCost.StdBurdenCost + PartCost.StdLaborCost + PartCost.StdMaterialCost + PartCost.StdMtlBurCost + PartCost.StdSubContCost) as [Calculated_TotalCost],
(PartBin.OnhandQty * TotalCost) as [Calculated_Value],
[ProdGrup].[Description] as [ProdGrup_Description],
[PartClass].[Description] as [PartClass_Description],
[Part].[TypeCode] as [Part_TypeCode],
[ProdGrup].[ProdCode] as [ProdGrup_ProdCode],
[PartClass].[ClassID] as [PartClass_ClassID],
[Warehse].[Plant] as [Warehse_Plant],
[PartCost].[LastLaborCost] as [PartCost_LastLaborCost],
[PartCost].[LastBurdenCost] as [PartCost_LastBurdenCost],
[PartCost].[LastMaterialCost] as [PartCost_LastMaterialCost],
[PartCost].[LastMtlBurCost] as [PartCost_LastMtlBurCost],
[PartBin].[Company] as [PartBin_Company],
(PartBin.OnhandQty * PartCost.LastMaterialCost) as [Calculated_TotCost],
(Coalesce(PurAgent.BuyerID,PurAgent_PartClass.BuyerID)) as [Calculated_Coalesce],
[PurAgent].[Name] as [PurAgent_Name]
from Erp.PartBin as PartBin
inner join Erp.Part as Part on
PartBin.Company = Part.Company
and PartBin.PartNum = Part.PartNum
and ( Part.NonStock <> 1 )
left outer join Erp.ProdGrup as ProdGrup on
Part.Company = ProdGrup.Company
and Part.ProdCode = ProdGrup.ProdCode
left outer join Erp.PartClass as PartClass on
Part.Company = PartClass.Company
and Part.ClassID = PartClass.ClassID
left outer join Erp.PurAgent as PurAgent_PartClass on
PartClass.Company = PurAgent_PartClass.Company
and PartClass.BuyerID = PurAgent_PartClass.BuyerID
inner join Erp.PartPlant as PartPlant on
Part.Company = PartPlant.Company
and Part.PartNum = PartPlant.PartNum
inner join Erp.PurAgent as PurAgent on
PartPlant.Company = PurAgent.Company
and PartPlant.BuyerID = PurAgent.BuyerID
left outer join Erp.PartCost as PartCost on
PartBin.Company = PartCost.Company
and PartBin.PartNum = PartCost.PartNum
inner join Erp.Warehse as Warehse on
PartBin.Company = Warehse.Company
and PartBin.WarehouseCode = Warehse.WarehouseCode
order by Warehse.Plant, Warehse.WarehouseCode, Part.PartNum
If you have multiple bins then you will need to make the qty a subquery grouped by company and part or use PartQty instead.