Let’s break this down into smaller chunks first. And verify that is makes sense what you are trying to do.
First, I think that your hierarchical part of the query can’t have the operations in it. Why? Because each assembly sequence can have more than one operation. A hierarchical query will add more records per row. So if you start adding duplicate rows, you hierarchy is going to be multiplied. So what you probably need to do, is make a query that just has the assembly sequences and part numbers for the assemblies in it. Then once you have that list (which will be your union all query) you can encapsulate that in parenthesis, and join that to the job operation table and find all of the operations based on the job number and asm seq fields.
So your union all is going to look like this (with other fields added as needed, like job number, and parent, part number for info, etc)
| jobNum | asm | level | indent | parent |
|---|---|---|---|---|
| xxx-x-x | 0 | 1 | 0 | 0 (note, this is 0 because you can’t have a null int, but is really nothing) |
| xxx-x-x | 10 | 2 | 0…10 | 0 |
| xxx-x-x | 20 | 2 | 0…20 | 0 |
| xxx-x-x | 30 | 2 | 0…30 | 0 |
| xxx-x-x | 40 | 3 | 0…10…40 | 10 |
| xxx-x-x | 50 | 3 | 0…10…50 | 10 |
| xxx-x-x | 60 | 3 | 0…10…60 | 10 |
| xxx-x-x | 70 | 3 | 0…20…70 | 20 |
| xxx-x-x | 80 | 3 | 0…20…80 | 20 |
| xxx-x-x | 90 | 3 | 0…20…90 | 20 |
| xxx-x-x | 100 | 3 | 0…30…100 | 30 |
Then once you have that, you simply join to the job operation table with JobNum and Asm Seq.
So just start with the CTE as a top level and you should get this (and only this)
| jobNum | asm | level | indent | parent |
|---|---|---|---|---|
| xxx-x-x | 0 | 1 | 0 | 0 |
Then when you add the heirarchy, you should get the rest of the rows for the job. like shown above.
Once you have that working correctly, THEN add the operation information at the top level.