BAQ Linking Orders and Jobs

Epicor ERP 10
1

Happy holidays everyone!
I am working on a BAQ where I have two subqueries feeding into the main query. I have a subquery for Orders, and one for Jobs. They are setup like this:

Order1 Criteria for customer and open lines.
Order2
job1
job2

I want to link the two subqueries based on the Order number and the Job number. In our company the left 5 digits of JobNum are always the same as OrderNum. So I tried to link it that way.
linked

You can see that I tried to use the left function here on the JobNum field. I have also tried the substring function. Neither one of them worked, both had proper syntax, but failed to execute. I also tried adding a calculated field to the Jobs query that equals the functions I tried above. The calculated field also gave the same error.

Severity: Error, Table: , Field: , RowID: , Text: Bad SQL statement.
Review the server event logs for details.

I don’t have access to the server logs, but I assume that the problem is the link to a calculated field. I know I have linked on calculated fields before, so what is the problem here? If I replace the link with OrderHed.OrderNum = JobProd.OrderNum then the query runs without error.

Any ideas?
Thanks everyone!
Nate

2

OrderNum is an integer; JobNum is a string. It’s a type mismatch.

2 Likes
3

doh

4

I normally always include the link from OrderHed to JobProd to JobHead… if your sub-query only has job… then you could include Jobhead to JobProd, and display the order number from JobProd… then link the subquery using that Ordernumber field.

1 Like
5

I use order release table as well.