I’m not sure Yash. the query could be working as planned because those joins are fine. Here is a query I wrote and it returns rows where the material part num in the second level is 03-118.
SELECT *
FROM erp.OrderDtl od
join erp.PartMtl pm on od.Company = pm.Company and od.PartNum = pm.[PartNum]
join erp.PartMtl pm2 on pm.Company = pm2.Company and pm.MtlPartNum = pm2.PartNum
where od.OpenLine = 1 and pm2.MtlPartNum = ‘03-118’
In other words, maybe you don’t have any order details that have the SR part number in the second level of the part. Why don’t you look in the first level of the BOM.
So on your first part material table in the query make that Begins with criteria statement instead of doing it on the second one.
Ahhhh it was the first level not the second! Good catch
Nice
The other thing you need to be aware of is that the PartMtl table stores materials for each revision of a part. So if your organization maintains multiple revisions for a part you need to address what specific revision you are trying to find the SR part number in when querying the partmtl table. We would always use the most current revision, but this might not make sense for your organization.
It should be only looking for the latest revision. Should this approach be doing that already?
Nah it’s not doing that already. You would have to join the part revision table onto the part table and each subsequent part mtl table to get the current rev.
We would always unapprove every rev except the current rev so when I joined the rev table I would put a criteria on there that said approved = 1 so that I would only bring the approved/current revision.
This is how it looks at the moment. I am putting a filter in the first partmtl to look at mtlpartnum that begin with PW or SR, then the second partmtl is looking at mtlpartnum that begin with SR. The SR values are either in the first level or second level depending on if the ordered part has a PW number or not. I am able to get both the PW values and the SR that correspond to it, but the SR values in the first level disappear
If I do not tell the 2nd partmtl to look for SR values the first partmtl finds all PW and SR values as it should
Yash, based on the way you have this query structured here is what it would return:
All order detail lines that have a part material in their BOM that starts with PW or SR AND ALSO have a part material inside of those PW or SR parts that starts with SR.
That is exactly what I need it to do. Expect, when the SR value shows up in the part material from the order detail, it should stop and not look for anything else. It seems as if it only finds the SR parts for the PW parts but doesn’t keep the first level SR parts
Yeah because the first level SR parts don’t have a material inside them that starts with SR, right? BUT the first level PW parts DO have a part inside their method that starts with SR, correct?
Correct
Yash, make the second two tables underneath the first part mtl table LEFT OUTER JOINS. This is the equivalent of saying, these tables/conditions don’t need to exist for my query. In other words, I don’t care if the PW or SR parts have an SR part inside of them, but if they do, show it.
Still not getting all the values from the first level
I did right outer join and it worked!
Hi Everyone!
I’ve had good look with the Indented BOM BAQ outlined in the CTE Case Study. However, I’d like to do something that I assume is relatively simple add a field / column for “Master Part Number” to show the upper level part number that was entered.
Can anyone help?
Thanks Very Much!
A.
Make a calculated field that shows the parameter.
@yourParameter
One way would be to include the field in your BAQ. There are many threads that will have this code. Here is one of them.