Hello,
We're on E9 so I can't use the E10 Subqueries, but I'm trying to write one in SQL as a view so we can get a leveled look at jobs, however I keep getting my statement terminated due to going over max recursion, so I think my recursive query is incorrect because our jobs only go about 7 levels deep.
My Anchor seems fine, and is:
select J.jobnum, j.partnum, mtl.partnum, pt.lotnum as ChildJob, 0 as level
from jobhead j
join jobmtl mtl on mtl.Company=j.Company and mtl.JobNum=j.JobNum
join PartTran pt on pt.Company=mtl.Company and pt.JobNum=mtl.JobNum and pt.JobSeq=mtl.MtlSeq
join part p on p.Company=mtl.Company and p.PartNum=mtl.PartNum
where pt.TranType='STK-MTL' and p.TypeCode='M' and j.Company ='VT'
My pesky CTE though, I'm not sure where I'm going wrong:
union all
select j.JobNum, j.partnum, mtl.partnum, pt.LotNum as Source, Level+1
from jobhead j
inner join ctejob cte on j.JobNum=cte.Source
join jobmtl mtl on mtl.Company=j.Company and mtl.JobNum=j.JobNum
join PartTran pt on pt.Company=mtl.Company and pt.JobNum=mtl.JobNum and pt.JobSeq=mtl.MtlSeq
join part p on p.Company=mtl.Company and p.PartNum=mtl.PartNum
where pt.TranType='STK-MTL' and p.TypeCode='M' and j.Company ='VT'
As a general explanation, for each job, I'm looking for the lot number of the manufactured parts on the job, as those lot numbers are the Job #s for the lower leveled part.
Not sure if anyone has any ideas, they'd be greatly appreciated!