BAQ Subquery or Union for Current Partial and Next Op

Epicor ERP 10
1

I currently have a query that will show 1 row for any job and the First Op that is not compete.
When this job Op is partially complete I can still see the same Op as it isn’t complete.
But when the Op is not complete but a partial “Qty Completed” I want to show that Op, on 1 line and the next Op not completed on another line.

So Job 123 has a run qty of 4. and 3 Ops are not complete. Here is the data set:

Job | OpCode | QtyComplete | OpComplete
123. . .5… … … … … .4 . . . … … .TRUE
123. . .10… … … … …3 . . . … … .FALSE
123. . .15… … … … …0 . . . … … .FALSE
123. . .20… … … … …0 . . . … … .FALSE

I want to see
Job | OpCode | QtyComplete | OpComplete
123. . .10… … … … …3 . . . … … .FALSE
123. . .15… … … … …0 . . . … … .FALSE

If the Data was:
Job | OpCode | QtyComplete | OpComplete
123. . .5… … … … … .4 . . . … … .TRUE
123. . .10… … … … …4 . . . … … .TRUE
123. . .15… … … … …0 . . . … … .FALSE
123. . .20… … … … …0 . . . … … .FALSE

The results should be:
Job | OpCode | QtyComplete | OpComplete
123. . .15… … … … …0 . . . … … .FALSE

I can make a query see the lowest OpCode not complete.
I can make a query see the next OpCode. I just can’t make a query that shows both.
I was thinking of a UNION type query where I would just show the results of the 2 queries, but I can’t seem to get the UNION query to work right. I know they have to have same number and types of columns, but then it says there are no fields even though I set them to display.

How can I “filter” the data set to only show the partial Op and the next Op from the Partial?

2

Think your union should work.

What is your statement?

3

Here is the SQL for the Next Operation with Qty 0 completed.
select *
from (select
[JobOper_Next].[Company] as [JobOper_Next_Company],
[JobOper_Next].[JobNum] as [JobOper_Next_JobNum],
[JobOper_Next].[AssemblySeq] as [JobOper_Next_AssemblySeq],
(min(JobOper_Next.OprSeq)) as [Calculated_Next_Op]
from Erp.JobOper as JobOper_Next
where (JobOper_Next.JobNum = ‘000552-1-1’ and JobOper_Next.AssemblySeq = 0 and JobOper_Next.QtyCompleted = 0)
group by [JobOper_Next].[Company],
[JobOper_Next].[JobNum],
[JobOper_Next].[AssemblySeq]) as Next_Op

Here is the SQL for the Current Operation that is not complete.
select *
from (select
[JobOper].[Company] as [JobOper_Company],
[JobOper].[JobNum] as [JobOper_JobNum],
[JobOper].[AssemblySeq] as [JobOper_AssemblySeq],
(min(JobOper.OprSeq)) as [Calculated_First_OP_NotComplete]
from Erp.JobOper as JobOper
where (JobOper.JobNum = ‘000552-1-1’ and JobOper.AssemblySeq = 0 and JobOper.OpComplete <> TRUE)
group by [JobOper].[Company],
[JobOper].[JobNum],
[JobOper].[AssemblySeq]) as First_Op_NotComplete

Do I need to make each of these a UNION and then combine them at the TopLevel?
When I try this, then I get an error stating there are no fields.

4

Union are fun to do.

There is a example in EpicorICETools_UserGuide

Combine Results Sets Using Union.

5

I have the ICE 2.5 tools User Guide and searched for “union” and only came up with “European Union” talking about data set. Is there a specific page I should look for?

I actually found it in the ICE 3.1 Tools User Guide.