I’m using a BAQ to pull details from the JobOper table and need to be able to identify the previous oprseq before a particular opcode. Does anyone know a technique for this?
yes you can with a few limitations. Being that there may not be a previous op, you may return a null value. You have to use a inner sub query and that will limit you to returning only one column. If you need multiple columns with this method you may just have to make more than one query. This is the only method i know of at this time, I’m sure there may be other methods.
create a inner sub query
bring in the joboper table (you’ll need to assign an alias), create links under subquery criteria tab.
bring in oprseq column
sort desc
back in your top level query create a calculated field based on the datatype returned from your inner sub. Place the subquery in the expression. Should be good to go at that point…
9 Likes
That’s a nice way to only get the previous operation. I did some goofy pivot table to get next operation similar to this to work in a dashboard, but this would work much better.
I forgot, I wanted to ask why you didn’t just drag the subquery into the page like a table and display the value there? Why the calculated field?
i think if you do it the other way it will limit the entire results of the overall thing to 1. I could be wrong, its been a while but it was just a method when you wanted to get a single value back that may or may not be null and you didnt want to mess things up with potential one to many issues when bringing back an entire record set. Feel free to verify or update me on whether or not that is the case!
You’re right. I’m trying to set one up right now and that’s exactly what’s happening. If you do it the way you showed, it’s like it’s its own little subquery for each row. That’s awesome, I had no idea you could do this.
So after setting it up, your sub query can only return one column or it pukes, good to know, but it does a good job at the one piece of info.
1 Like
Yeha its an inner select subquery. Sort of like doing
SELECT OrderHed.OrderNum, (SELECT Customer.Name FROM Customer WHERE Customer.CustNum = OrderHed.CustNum)
FROM OrderHed
2 Likes
You crazy coders and your SQL…
3 Likes
lol meanwhile, the OP is nowhere to be seen or heard from again…you’ll find quite a few handy uses for it.
1 Like
I know this is an old post, but I have another question with this technique. A few time now I have run into where I wanted to use this to add something to an existing query that has aggregate functions in it. To get around it, I make the top level into a sub query and bring through all of the same fields, but since it’s been grouped and summed a level below, it works. The problem is, this query is already in place in a dashboard so doing this means I have to recreate everything. Is there a way to get this subquery calculated field to work with a top level that uses aggregate functions?
edit: never mind, I figured out I just had to make sure that the field that I was tying the mini sub to was in the list in the top level. I had the job number from the JobProd table in the field, but I was tying it to the job number from the JobHead table. I added the JobHead.JobNum field into the top level and it works fine. Now I don’t need to redo the whole dashboard!
1 Like
Sorry to bring up an old thread, but I’ve been working on something similar to pull the previous operation code but for some reason it keeps pulling a random operation instead of the next sequential operation. However when I try to pull the operation sequence, it pulls the next sequential operation number.
Has anyone had success with this?
It worked just fine for me. It sounds like maybe you are missing a join or an order by in your query somehow. It’s hard to tell without seeing what you have so far.
1 Like
So you have the op code is greater than, so you will need to order your subquery ascending and then limit to the first row. Did you do that?
1 Like
No sir, would I do that in a calculated field?
select
[JobHead].[ProjectID] as [JobHead_ProjectID],
[JobHead].[JobNum] as [JobHead_JobNum],
[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
[JobAsmbl].[Description] as [JobAsmbl_Description],
[JobAsmbl].[RequiredQty] as [JobAsmbl_RequiredQty],
[JobHead].[DueDate] as [JobHead_DueDate],
(((select distinct top (1)
[JobOper2].[OpCode] as [JobOper2_OpCode]
from Erp.JobOper as JobOper2
where (not JobOper2.OpCode = 'ENG' and not JobOper2.OpCode = 'PROG')
and JobOper2.Company = JobOper.Company and JobOper2.JobNum = JobOper.JobNum and JobOper2.AssemblySeq = JobOper.AssemblySeq and JobOper2.OprSeq < JobOper.OprSeq
order by JobOper2.OpCode))) as [Calculated_PreviousOp],
[JobOper].[OpCode] as [JobOper_OpCode],
[JobOper].[OprSeq] as [JobOper_OprSeq],
(((select distinct top (1)
[JobOper5].[OprSeq] as [JobOper5_OprSeq]
from Erp.JobOper as JobOper5
where JobOper5.Company = JobOper.Company and JobOper5.JobNum = JobOper.JobNum and JobOper5.AssemblySeq = JobOper.AssemblySeq and JobOper5.OprSeq > JobOper.OprSeq
order by JobOper5.OprSeq))) as [Calculated_NextOp],
(((select top (1)
[JobOper3].[OpCode] as [JobOper3_OpCode]
from Erp.JobOper as JobOper3
where (JobOper3.OpCode = 'BBLST' or JobOper3.OpCode = 'PLATE' or JobOper3.OpCode = 'HEAT')
and JobOper3.Company = JobOper.Company and JobOper3.JobNum = JobOper.JobNum and JobOper3.AssemblySeq = JobOper.AssemblySeq and JobOper3.OprSeq > JobOper.OprSeq
order by JobOper3.OpCode))) as [Calculated_Finish],
(((select top (1)
[JobOper4].[OpCode] as [JobOper4_OpCode]
from Erp.JobOper as JobOper4
where (JobOper4.OpCode = 'P2PRTMIL' or JobOper4.OpCode = 'SMAC' or JobOper4.OpCode = 'P2CNCMIL')
and JobOper4.Company = JobOper.Company and JobOper4.JobNum = JobOper.JobNum and JobOper4.AssemblySeq = JobOper.AssemblySeq and JobOper4.OprSeq > JobOper.OprSeq
order by JobOper4.OpCode))) as [Calculated_Machining],
[JobMtl].[PartNum] as [JobMtl_PartNum],
[JobMtl].[Description] as [JobMtl_Description],
[JobMtl].[QtyPer] as [JobMtl_QtyPer],
[JobMtl].[IUM] as [JobMtl_IUM],
[JobAsmbl].[AnalysisCode] as [JobAsmbl_AnalysisCode],
(((select distinct top (1)
[JobOper6].[OpCode] as [JobOper6_OpCode]
from Erp.JobOper as JobOper6
where JobOper6.Company = JobOper.Company and JobOper6.JobNum = JobOper.JobNum and JobOper6.AssemblySeq = JobOper.AssemblySeq and JobOper6.OpCode > JobOper.OpCode))) as [Calculated_Test]
from Erp.JobHead as JobHead
inner join Erp.JobOper as JobOper on
JobHead.Company = JobOper.Company
and JobHead.JobNum = JobOper.JobNum
and ( JobOper.OpCode = @Operation and JobOper.OpComplete = No and JobOper.ActProdHours = 0.00 )
inner join Erp.JobAsmbl as JobAsmbl on
JobOper.Company = JobAsmbl.Company
and JobOper.JobNum = JobAsmbl.JobNum
and JobOper.AssemblySeq = JobAsmbl.AssemblySeq
inner join Erp.JobMtl as JobMtl on
JobAsmbl.Company = JobMtl.Company
and JobAsmbl.JobNum = JobMtl.JobNum
and JobAsmbl.AssemblySeq = JobMtl.AssemblySeq
and ( JobMtl.MtlSeq = 10 )
where (JobHead.JobType = 'MFG' and JobHead.DueDate >= @Date and JobHead.JobComplete = NO and JobHead.ProjectID = @Project)
Nope, for this specific case you need to go the the display fields tab, then there is tab there for order (normally you can’t do this is a subquery, but since this is a single sub select you can). Select the opSeq to order by.
Also, I just noticed that you had the OpCode in your filter, you need the OpSeq for the order of the sequences. The OpCode is what the actual operation is.
Sorry about not having any screen shots, I don’t have Epicor anymore to reference, so I have to go off of memory.
1 Like
Sorry for being a bit unclear, but I would like it to also show the next OpCode. Returning the operation number is working perfectly! But the next actual operation code I can’t seem to get.
You can show the next op code, but you will have to get the operation sequence number, then rejoin to the JobOp table to get which specific code it is, since you can only bring one field with you for a single sub select.
This is the problem filter I am talking about. Having and opcode greater than another opcode doesn’t make sense. The OpCode, don’t inherently have an order (even though they might naturally have one in your business) so the filter for later opertations needs to be for greater than the opSeq. That’s why I think you are getting random operations back.
2 Likes
join to the JobOp table
Ahhh that specifically is what I am struggling with and need to learn how to do
Go to your top level, and bring in the JobOp table (again) and then do your joins so that it it being joined to the OpSeq that you brought in with the single sub select. You might have to make more levels to make this work out correctly.
3 Likes