BAQ Error -- The multi-part identifier "JobAsmbl.TLALaborCost" could not be bound

[Espa (Elise Spadavecchio)]

Running into this error when I add a subquery. I don’t know if it has something to do with my formulas or the joins with the subquery. With the formulas, I’m trying to avoid having multiple lines for the same jobs show up on the BAQ… Full disclosure – I’m a BAQ novice!

select  
	[OrderHed].[OrderNum] as [OrderHed_OrderNum], 
	[OrderHed].[SalesRepList] as [OrderHed_SalesRepList], 
	((case when OrderDtl.RMANum = 0 then 'Case Only' 

when OrderDtl.RMANum > 0 and ReWork.OrderHedReWork_OrderNum is null then 'Credit only' 
when OrderDtl.RMANum > 0 and ReWork.OrderHedReWork_OrderNum <> '' then 'Has RMA'

else 'Oops' end)) as [Calculated_HASRMA], 
	[HDCase].[HDCaseNum] as [HDCase_HDCaseNum], 
	[OrderDtl].[RMANum] as [OrderDtl_RMANum], 
	[OrderDtl].[RMALine] as [OrderDtl_RMALine], 
	[JobHead].[JobNum] as [JobHead_JobNum], 
	[OrderDtl].[OrderQty] as [OrderDtl_OrderQty], 
	[OrderDtl].[DocExtPriceDtl] as [OrderDtl_DocExtPriceDtl], 
	((case when CreditMemo.Calculated_ExtCredit is null then '0' else CreditMemo.Calculated_ExtCredit end)) as [Calculated_CreditBack], 
	((case when OrderDtl.DocExtPriceDtl + CreditBack = 0 then .001  else OrderDtl.DocExtPriceDtl + CreditBack end)) as [Calculated_NetExtendPrice], 
	((JobAsmbl.TLALaborCost + JobAsmbl.TLABurdenCost)) as [Calculated_TotalLabor], 
	(OrderDtl.DocExtPriceDtl - (JobAsmbl.TLAMaterialCost + TotalLabor) + CreditBack) as [Calculated_Margin], 
	((case when OrderDtl.DocExtPriceDtl = 0 then -100 else ((Margin / OrderDtl.DocExtPriceDtl)  * 100) END)) as [Calculated_MarginPCT2], 
	[ReWork].[OrderHedReWork_OrderNum] as [OrderHedReWork_OrderNum], 
	[ReWork].[OrderDtlRW_OrderLine] as [OrderDtlRW_OrderLine], 
	[ReWork].[OrderDtlRW_OrderQty] as [OrderDtlRW_OrderQty], 
	[ReWork].[JobHeadRW_JobNum] as [JobHeadRW_JobNum], 
	[ReWork].[JobHeadRW_JobComplete] as [JobHeadRW_JobComplete], 
	[ReWork].[OrderDtlRW_DocExtPriceDtl] as [OrderDtlRW_DocExtPriceDtl], 
	[ReWork].[Calculated_TotalLaborRW] as [Calculated_TotalLaborRW], 
	[ReWork].[JobAsmblRW_TLAMaterialCost] as [JobAsmblRW_TLAMaterialCost], 
	[ReWork].[Calculated_MarginRW] as [Calculated_MarginRW], 
	[ReWork].[Calculated_MarginRWPct] as [Calculated_MarginRWPct], 
	((OrderDtl.DocExtPriceDtl + ReWork.OrderDtlRW_DocExtPriceDtl) + CreditBack) as [Calculated_TTExtPrice], 
	(TotalLabor + ReWork.Calculated_TotalLaborRW + ReWork.JobAsmblRW_TLAMaterialCost + JobAsmbl.TLAMaterialCost) as [Calculated_TTCOST], 
	(Margin + ReWork.Calculated_MarginRW) as [Calculated_TTMargin], 
	(((TTExtPrice - TTCOST ) / TTExtPrice )*100) as [Calculated_TTMarginPct], 
	(OrderDtl.OrderQty + ReWork.OrderDtlRW_OrderQty) as [Calculated_TTQty], 
	((case when CreditBack < 0 then 'Yes'  else 'No' end)) as [Calculated_CreditMemo], 
	[HDCase].[Description] as [HDCase_Description], 
	[HDCase].[ResolutionText] as [HDCase_ResolutionText] 

from Erp.OrderHed as [OrderHed]
inner join Erp.OrderDtl as [OrderDtl] on 
	  OrderHed.Company = OrderDtl.Company
	and  OrderHed.OrderNum = OrderDtl.OrderNum
	and ( OrderDtl.OrderQty >= 1  )
inner join Erp.QuoteDtl as [QuoteDtl] on 
	  QuoteDtl.QuoteNum = OrderDtl.QuoteNum
	and  QuoteDtl.QuoteLine = OrderDtl.QuoteLine
inner join Erp.JobHead as [JobHead] on 
	  QuoteDtl.Company = JobHead.Company
	and  QuoteDtl.QuoteNum = JobHead.QuoteNum
	and  QuoteDtl.QuoteLine = JobHead.QuoteLine
left outer join  (select  
	[JobAsmbl].[TLAMaterialCost] as [JobAsmbl_TLAMaterialCost], 
	[JobAsmbl].[JobNum] as [JobAsmbl_JobNum], 
	[LaborDtlSub].[Calculated_Machines] as [Calculated_Machines], 
	[LaborDtlSub].[Calculated_Employees] as [Calculated_Employees], 
	[LaborDtlSub].[Calculated_Department] as [Calculated_Department] 

from Erp.JobAsmbl as [JobAsmbl]
left outer join  (select  
	[LaborDtl].[JobNum] as [LaborDtl_JobNum], 
	( STRING_AGG(LaborDtl.OpCode, ' | ') WITHIN GROUP (ORDER BY LaborDtl.JobNum)) as [Calculated_Machines], 
	(STRING_AGG(LaborDtl.EmployeeNum, ' | ') WITHIN GROUP (ORDER BY LaborDtl.JobNum)) as [Calculated_Employees], 
	( STRING_AGG(LaborDtl.JCDept, ' | ') WITHIN GROUP (ORDER BY LaborDtl.JobNum)) as [Calculated_Department] 

from Erp.LaborDtl as [LaborDtl]
group by 
	[LaborDtl].[JobNum])  as [LaborDtlSub] on 
	  JobAsmbl.JobNum = LaborDtlSub.LaborDtl_JobNum
where (JobAsmbl.AssemblySeq = 0)
group by 
	[JobAsmbl].[TLAMaterialCost], 
	[JobAsmbl].[JobNum], 
	[LaborDtlSub].[Calculated_Machines], 
	[LaborDtlSub].[Calculated_Employees], 
	[LaborDtlSub].[Calculated_Department])  as [JobAsmblSub] on 
	  JobHead.Company = JobAsmblSub.JobAsmbl_JobNum
	and  JobHead.JobNum = JobAsmblSub.JobAsmbl_JobNum
left outer join  (select  
	[QuoteDtlRW].[QuoteNum] as [QuoteDtlRW_QuoteNum], 
	[QuoteDtlRW].[REWORKRMA_c] as [QuoteDtlRW_REWORKRMA_c], 
	[QuoteDtlRW].[REWORKRMALINE_c] as [QuoteDtlRW_REWORKRMALINE_c], 
	[OrderHedReWork].[OrderNum] as [OrderHedReWork_OrderNum], 
	[OrderDtlRW].[OrderLine] as [OrderDtlRW_OrderLine], 
	[JobHeadRW].[JobComplete] as [JobHeadRW_JobComplete], 
	[JobHeadRW].[JobNum] as [JobHeadRW_JobNum], 
	((JobAsmblRW.TLALaborCost + JobAsmblRW.TLABurdenCost)) as [Calculated_TotalLaborRW], 
	[JobAsmblRW].[TLAMaterialCost] as [JobAsmblRW_TLAMaterialCost], 
	[OrderDtlRW].[OrderQty] as [OrderDtlRW_OrderQty], 
	[OrderDtlRW].[DocExtPriceDtl] as [OrderDtlRW_DocExtPriceDtl], 
	(OrderDtlRW.DocExtPriceDtl - (JobAsmblRW.TLAMaterialCost + TotalLaborRW)) as [Calculated_MarginRW], 
	(((MarginRW / OrderDtlRW.DocExtPriceDtl)*100)) as [Calculated_MarginRWPct] 

from Erp.OrderHed as [OrderHedReWork]
right outer join Erp.OrderDtl as [OrderDtlRW] on 
	  OrderHedReWork.Company = OrderDtlRW.Company
	and  OrderHedReWork.OrderNum = OrderDtlRW.OrderNum
right outer join Erp.QuoteDtl as [QuoteDtlRW] on 
	  QuoteDtlRW.QuoteNum = OrderDtlRW.QuoteNum
	and  QuoteDtlRW.QuoteLine = OrderDtlRW.QuoteLine
inner join Erp.JobHead as [JobHeadRW] on 
	  QuoteDtlRW.Company = JobHeadRW.Company
	and  QuoteDtlRW.QuoteNum = JobHeadRW.QuoteNum
	and  QuoteDtlRW.QuoteLine = JobHeadRW.QuoteLine
inner join Erp.JobAsmbl as [JobAsmblRW] on 
	  JobHeadRW.Company = JobAsmblRW.Company
	and  JobHeadRW.JobNum = JobAsmblRW.JobNum
	and ( JobAsmblRW.AssemblySeq = 0  )
where (OrderHedReWork.HASREWORK_c = True))  as [ReWork] on 
	  ReWork.QuoteDtlRW_REWORKRMA_c = OrderDtl.RMANum
	and  ReWork.QuoteDtlRW_REWORKRMALINE_c = OrderDtl.RMALine
left outer join  (select  
	[InvcDtl].[OrderNum] as [InvcDtl_OrderNum], 
	[InvcDtl].[OrderLine] as [InvcDtl_OrderLine], 
	[InvcDtl].[DocExtPrice] as [InvcDtl_DocExtPrice], 
	(SUM(InvcDtl.DocExtPrice)) as [Calculated_ExtCredit] 

from Erp.InvcHead as [InvcHead]
left outer join Erp.InvcDtl as [InvcDtl] on 
	  InvcHead.Company = InvcDtl.Company
	and  InvcHead.InvoiceNum = InvcDtl.InvoiceNum
where (InvcHead.CreditMemo = True  
and not InvcHead.InvoiceNum IN (187953)  
and InvcHead.RMANum > 0  
and not InvcHead.InvoiceAmt = 0)
group by 
	[InvcDtl].[OrderNum], 
	[InvcDtl].[OrderLine], 
	[InvcDtl].[DocExtPrice])  as [CreditMemo] on 
	  CreditMemo.InvcDtl_OrderNum = OrderDtl.OrderNum
	and  CreditMemo.InvcDtl_OrderLine = OrderDtl.OrderLine
inner join Erp.HDCase as [HDCase] on 
	  OrderDtl.OrderNum = HDCase.OrderNum
	and  OrderDtl.OrderLine = HDCase.OrderLine
where (OrderHed.VoidOrder = False)
order by OrderDtl.OrderNum, OrderDtl.OrderLine

[gpayne (Greg Payne)]

I formatted the code with grave accents for readability. The only thing I see is one join I think is wrong of Company to JobNum. Do you have access to the server logs? You could look at the query Epicor generated.

[Espa (Elise Spadavecchio)]

I don’t have access to the logs -we are on public cloud.

Do you know what formula I would aggregate multiple resources or multiple employees so lines are getting duplicated? Do you have any suggestions there? And would this need to be on a subquery?

[gpayne (Greg Payne)]

Did it get better with the join fixed? I would assume that the string_agg would be in a subquery.

[Banderson (Brandon Anderson)]

You should be able to group all of the fields and use that field for the rows you need combined. You can’t group in a calculated field like that, so remove that part of the calculation.

A subquery would work too though.

[Espa (Elise Spadavecchio)]

I tried this route - Added the LaborDtl table as a subquery. Grouped by all fields. Removed all Calculated fields.

I don’t get an error which is great, however, I’m getting duplicate lines if more than one employee worked on a job or if the job was manufactured on two machines. Any ideas?

[Banderson (Brandon Anderson)]

well, it can only group rows if the fields are the same. If you have more than one employee number, you’ll get a row for that, since that row is no longer the same. So you’ll have to replace the field that you want to combine with a calculated field, like the string agg formula you have above. Then remove the actual field.

This would be the calculated field.

STRING_AGG(LaborDtl.EmployeeNum, ' | ')

[Banderson (Brandon Anderson)]

Keep in mind, if you do this all on one level, string_agg() won’t remove duplicates. So if you have the same employee number clocking into the same op twice, their number is going to show up twice in the calculated field. To remove duplicate values and set the order of things if they aren’t ordered correctly, you’ll need to do a subquery to remove actual duplicates, then on the next level up use these calculated fields to combine the non-duplicate fields that you want to combine.

[Espa (Elise Spadavecchio)]

Ok, got it. This is where I’m going wrong. I need a subquery on the subquery. But I have to ask, what formula do I use to remove the duplicates? I’m a little lost.

[Banderson (Brandon Anderson)]

If you group all of the rows, then they will condense down to unique rows. Or you can cheat and use the “Distinct” setting on the subqeury options. Then use you aggregate functions (like Sum() Count(), string_agg() etc.) to do thing with the fields that aren’t duplicated that you to combine into those rows.

[Espa (Elise Spadavecchio)]

Thanks so much. I’m still getting stuck, but I think it may be best to revisit next week. Have a nice weekend!

[Banderson (Brandon Anderson)]

Yeah. Looks like you are trying to do a lot at once. Start with a simpler example where you can see all the data.

This video has a lot of good stuff in it that might help you.

BAQ Calculated fields insights presentation

Hey Everyone, This is a recording done after the fact of my insights presentation due to the technical difficulties I had during the presentation. Now I was able to record all of the live things that I wanted to show. I will apologize for my fat head in the way for some of it, but I thought it would be better if you could see me talking instead of just a disembodied voice. And I didn’t feel like taking the time to figure out how to use active presenter well enough to do some editing, so you ge…

[klincecum (Kevin Lincecum)]

@Espa

I don’t have access to the logs -we are on public cloud.

You do have access to the logs. User Server file download.

However that probably won’t help, as you do not have access to add the proper trace flags to the web.config to log that query. I don’t think the proper flags are in there by default.