Recursive CTE for Leveled Job List

>>CTE
Not sure about the specifics of your query.
but... here are links to articles that helped me with CTEs.

http://sqlblog.com/blogs/john_paul_cook/archive/2009/10/03/displaying-hierarchical-data-indenting-the-output.aspx
http://sqlblog.com/blogs/john_paul_cook/archive/2009/11/06/bill-of-materials-the-basics.aspx
http://sqlblog.com/blogs/john_paul_cook/archive/2009/11/07/bill-of-materials-computing-component-totals.aspx

 

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!