BAQ syntax error help

Epicor ERP 10
1

So I’m trying to create a new calculated field that is uses the same functionality as another field already in the query.

datepart(year,Orderdtl.NeedByDate)

This is what is already in the query working fine. What I want to do is the same thing with the JobHead.ReqDueDate. Should be simple enough.

Whenever I add the new field, no matter what date I put in there I get this error

It doesn’t make sense, the exact same syntax is already running fine. If I delete the field, everything works fine. If I add it back in, error. If I just add the date with no datepart, then it runs (not what I need, but it runs)

Can anyone tell me why I am getting this error?

2

quick stab, change the name of your calc field?

3

Same error.

image.png517×567 15.5 KB

4

Try this.

YEAR(JobHead.ReqDueDate)

I have a feeling there is an error in another calculated field, the query runs fine before you try to add this new field?

5

I’d say share the entire query so the gurus can see how it’s configured

6

Yeah, it runs fine until I add this one.

Same error with that code.

image.png451×592 11.8 KB

7

Per your request, (I should add, for right now to get what I need, I used the original calculated field and changed with date I was chopping up to the job one, that works fine. I would still like to have both the job one, and the order one in the report though)

select 
	[JobProd].[JobNum] as [JobProd_JobNum],
	[JobHead].[ReqDueDate] as [JobHead_ReqDueDate],
	((case when datepart(month,JobHead.ReqDueDate)<10 then convert(varchar, '0') + convert(varchar, datepart(month,JobHead.ReqDueDate)) else convert(varchar, datepart(month,JobHead.ReqDueDate)) end)) as [Calculated_month],
	(datepart(year,JobHead.ReqDueDate)) as [Calculated_Year],
	[JobHead].[DueDate] as [JobHead_DueDate],
	[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
	[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
	[OrderDtl].[PartNum] as [OrderDtl_PartNum],
	[Part].[PartDescription] as [Part_PartDescription],
	[OrderDtl].[OrderQty] as [OrderDtl_OrderQty],
	[OrderDtl].[ProdCode] as [OrderDtl_ProdCode],
	[OrderHed].[SalesRepList] as [OrderHed_SalesRepList],
	[Customer].[Name] as [Customer_Name],
	[OrderHed].[NeedByDate] as [OrderHed_NeedByDate],
	[OrderHed].[RequestDate] as [OrderHed_RequestDate],
	[OrderDtl].[NeedByDate] as [OrderDtl_NeedByDate],
	[OrderDtl].[RequestDate] as [OrderDtl_RequestDate],
	(isnull(sum( LaborDtl.LaborHrs),0)) as [Calculated_laborhrs],
	[EngHours].[Calculated_EngHours] as [Calculated_EngHours],
	[ShopHours].[Calculated_ShopHrs] as [Calculated_ShopHrs],
	[OrderDtl].[ExtPriceDtl] as [OrderDtl_ExtPriceDtl],
	[OrderDtl].[OpenLine] as [OrderDtl_OpenLine],
	[Customer].[CreditHold] as [Customer_CreditHold],
	((case when OrderRel.DropShip = 'true' then 'x' else '' end)) as [Calculated_DrpShp],
	[OrderDtl].[Company] as [OrderDtl_Company],
	[ShopHours].[LaborDtl2_JobNum] as [LaborDtl2_JobNum],
	[EngHours].[LaborDtl1_JobNum] as [LaborDtl1_JobNum],
	[OrderHed].[OrderDate] as [OrderHed_OrderDate],
	(datepart(year,JobHead.ReqDueDate)) as [Calculated_JobYear]
from Erp.OrderDtl as OrderDtl
full outer join Erp.JobProd as JobProd on 
	OrderDtl.Company = JobProd.Company
And
	OrderDtl.OrderNum = JobProd.OrderNum
And
	OrderDtl.OrderLine = JobProd.OrderLine

left outer join Erp.Part as Part on 
	OrderDtl.Company = Part.Company
And
	OrderDtl.PartNum = Part.PartNum

inner join Erp.Customer as Customer on 
	OrderDtl.Company = Customer.Company
And
	OrderDtl.CustNum = Customer.CustNum

inner join Erp.OrderHed as OrderHed on 
	OrderDtl.Company = OrderHed.Company
And
	OrderDtl.OrderNum = OrderHed.OrderNum

left outer join Erp.LaborDtl as LaborDtl on 
	JobProd.JobNum = LaborDtl.JobNum

inner join Erp.OrderRel as OrderRel on 
	OrderDtl.Company = OrderRel.Company
And
	OrderDtl.OrderNum = OrderRel.OrderNum
And
	OrderDtl.OrderLine = OrderRel.OrderLine

left outer join  (select 
	[LaborDtl1].[JobNum] as [LaborDtl1_JobNum],
	(isnull(sum(LaborDtl1.LaborHrs),0)) as [Calculated_EngHours]
from Erp.LaborDtl as LaborDtl1
 where (LaborDtl1.OpCode like 'ENG')
group by [LaborDtl1].[JobNum])  as EngHours on 
	JobProd.JobNum = EngHours.LaborDtl1_JobNum

left outer join  (select 
	[LaborDtl2].[JobNum] as [LaborDtl2_JobNum],
	(isnull(sum( LaborDtl2.LaborHrs),0)) as [Calculated_ShopHrs]
from Erp.LaborDtl as LaborDtl2
 where (not LaborDtl2.OpCode like 'ENG')
group by [LaborDtl2].[JobNum])  as ShopHours on 
	JobProd.JobNum = ShopHours.LaborDtl2_JobNum

left outer join Erp.JobHead as JobHead on 
	JobProd.Company = JobHead.Company
And
	JobProd.JobNum = JobHead.JobNum

group by [JobProd].[JobNum],
	[JobHead].[ReqDueDate],
	((case when datepart(month,JobHead.ReqDueDate)<10 then convert(varchar, '0') + convert(varchar, datepart(month,JobHead.ReqDueDate)) else convert(varchar, datepart(month,JobHead.ReqDueDate)) end)) as [Calculated_month],
	(datepart(year,JobHead.ReqDueDate)) as [Calculated_Year],
	[JobHead].[DueDate],
	[OrderDtl].[OrderNum],
	[OrderDtl].[OrderLine],
	[OrderDtl].[PartNum],
	[Part].[PartDescription],
	[OrderDtl].[OrderQty],
	[OrderDtl].[ProdCode],
	[OrderHed].[SalesRepList],
	[Customer].[Name],
	[OrderHed].[NeedByDate],
	[OrderHed].[RequestDate],
	[OrderDtl].[NeedByDate],
	[OrderDtl].[RequestDate],
	[EngHours].[Calculated_EngHours],
	[ShopHours].[Calculated_ShopHrs],
	[OrderDtl].[ExtPriceDtl],
	[OrderDtl].[OpenLine],
	[Customer].[CreditHold],
	((case when OrderRel.DropShip = 'true' then 'x' else '' end)) as [Calculated_DrpShp],
	[OrderDtl].[Company],
	[ShopHours].[LaborDtl2_JobNum],
	[EngHours].[LaborDtl1_JobNum],
	[OrderHed].[OrderDate]
 order by  OrderDtl.ProdCode ,  OrderHed.NeedByDate
8

The data type should be int.

9

still doesn’t fix the error.

And it does this, which I don’t want

10

Start by just putting “test” with x(8) format and see if it still gives you the error. If so, then it sounds like a corrupted query. You could either try exporting-importing, or rebuilding it fresh.

I’ve seen this mostly in E9, but there are some bugs I’ve seen over the years in this interface.

11

I can add new calculated field with just characters in it. (that’s what you’re suggesting right?) it’s only when I try to add the date part that it throws the error.

This query happens to be a ones that was saved out and imported to start it (copied actually). I am adding in more date fields for a management level backlog view, but I copied it from the current one that runs the shop. So I basically did what you are suggesting. unless The process of exporting will do something different than the copy query function?

12

Brandon, if you’ve tried pasting in the exact same datepart formula and it fails where it is working in another calculated field. This suggests to me some sort of internal corruption in the BAQ. Try to re-create from a new BAQ and see if it still gives you the error. I have seen this before, and sometimes this is my only recourse.

13

Bummer. It’s kind of a beast of a query. I’ll give that a try when I get some time.

Thanks @josephmoeller

14

Try Cast on the second one. You could cast it to integer and just change the format to drop the thousands separator.

15

When you look at the error popup it is on the fields that are in left outer joins toward the bottom. You are right it is a beast of query. I did the calculated fields last night and could do as many dateparts as I wanted without issue.

If you want to export the baq and post it I will try to import and see if I can see anything.

Greg

16

Ok, so here is the query exported. This is a working one. On the top level query there are calculated fields that break apart the date to year and month in order to be able to use the group by functions of the grid. This is how they look at what orders they have due and we add up the prices to see how much revenue is scheduled for the month. I want to duplicate calculated fields that Year, and Month that are looking at the JobHead need by date and have the same things in the OrderDtl need by date. (that way we can look at overall business revenue one way, and production only revenue the other)

ManagerLevelBacklog.baq (101.1 KB)

17

Have you thought about creating 2 queries in the BAQ. 1 as a CTE and the second as a Union (or UnionAll)?

18

Can you explain why that would be a benefit? From what I understand, the benefit to a CTE is the fact that you can loop through hierarchical data (like a BOM or something). This query is not doing that. This is simply looking at order data from a variety of tables, as well as summing up labor hours and displaying them in one grid.

19

Give this a shot. brandon.baq (101.7 KB)

20

FML, I can’t import from a later version. We are on 10.0.700.4. That really sucks.

Did you do anything special? Or did it just work for you using normal methods?