Saving or Running BAQ makes Epicor crash

Epicor ERP 10
, ,
1

I have a BAQ that I am attempting to use to pull attachments links for material picked to jobs.

The working part of the query goes through the part tran table filtered to -MTL transactions and recursively goes through JobNumber = Lot Number, for the sub level parts and displays the results showing the hierarchy and grabs all the lower level lot numbers.

I now want to do a join to the results of that subquery to grab attachments from a bunch of different tables and union those to my original results so I’m left with all the material that has been picked for top to lower level along with any attachments linked to the lot.

As soon as I add an additional union to my baq, if I save or run the query, it crashes epicor.

This works:
image

This crashes epicor
image

Crash error:
image

Which after looking online appears it could have something to do with an infinite recursion loop. Which does not make sense since the recursion portion works without the union.

image
image785Ă—268 8.02 KB

I will also add I’ve done unions to results of the recursive subquery before with no issue.

Other query that works:
image

Any suggestions would be greatly appreciated!!

1 Like
2

Upload your BAQ it is really hard to troubleshoot with screenshots alone

2 Likes
3

DocRetrieve_10-23-24.baq (74.6 KB)

Sure, this is the working portion that is saveable. Thank you!

4

ok so I got the query loaded I don’t fully understand what you are doing but that’s fine. YOu want to add another Union to this? A union to which table?

5

So it looks like you are looking at all
PartTran Records related to a Specific JobNumber (which you call LotNum)

And any JobHeads related to the Same

And Unioning Both of those fair enough now what?

6

I want to take the results of subquery “FullBOM” and join it to the attachment table to find lots that have attachments linked to them and union those results back to the “FullBOM” subquery. Results should look like this

image

7

I’m not sure why you need a union again? You already have this
image

So just add a join to the PartLotAttach (XFileAttacH) and get it that way? I’m missing something I think.

8

Yep I should’ve specified there’s going to be 5 different areas that documents related to that lot can be attached. RcvHead, RcvDtl, PartLot, JobHead, JobOper . I want to do a union to avoid joining to the attachment rows, which would result in a lot of duplicates

9

Regarding the crash part - Just glancing at the export in a text editor, @LotNum gets cast a couple of different ways in a union’d query. Looks like it’s probably valid SQL, but casts plus unions can confuse BAQ. Sometimes if you typecast one thing in a union, BAQ will complain until the entire expression of every instance of that field in the stack is cast identically the same way. Like, the entire expression:

-- BAQ will sometimes be unhappy with this kind of pattern
select cast('a' as nvarchar(1))
union all
select case where 1=1 then cast('b' as nvarchar(1)) else cast('c' as nvarchar(1)) end
-- while loving this
select cast('a' as nvarchar(1))
union all
select cast(case where 1=1 then 'b' else 'c' end as nvarchar(1))
2 Likes