"Union All" subquery skipping an entire joined table

Epicor ERP 10
, ,
1

Hello all, I did search first and I don’t see anything quite like this.

I am building a hierarchical lookup to use with dynamic query, based on a recursive CTE that works in SQL. The basic structure of the code page seems fine and matches what I have in SQL.

Except my Union All subquery seems to skip one table. The Phrase Builder shows this (“flatBOM” is my CTE):

image

But the code only shows the inner join on queries 2 & 4

This seems very strange. It’s also the only thing I can find that’s different between my SQL query and my BAQ.

Any ideas? Much appreciated!

2

OK, interesting - I found that in my table for my child revisionnum I had accepted the default join and although I had changed partnum=partnum to partnum=mtlpartnum I had left revisionnum=revisionnum, which of course doesnt’t work because the partmtl table doesn’t have a revisionnum for the material part.

Now the BAQ returns the same results as my SQL query. However the union all part of the code still doesn’t display what’s in the query builder. Maybe it’s redundant? I don’t know enough about proper SQL stuff.

1 Like