my add and adhd mind is spinning. think you have everything you need to bring the parent qty along.
Somehow I have to multiply the Qty per and the Parent quantity. The way I think it should happen is above, but it errors out. I’m wondering if I just have to do things in more granular step. I’ll probable make a new query just to get BOM quantities to see how I can multiply that.
I was thinking of something like this.
for your CTE.
with [SubQuery1] as
(select
(PartMtl.PartNum) as [Calculated_IntialPart],
(Part1.PartDescription) as [Calculated_InitialPartDesc],
[PartMtl].[Company] as [PartMtl_Company],
[PartMtl].[PartNum] as [PartMtl_PartNum],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
[PartMtl].[QtyPer] as [PartMtl_QtyPer],
1 as [Calculated_PartMtl_TotalQtyPer]
from Erp.PartMtl as PartMtl
inner join Erp.Part as Part1 on
PartMtl.Company = Part1.Company
and PartMtl.PartNum = Part1.PartNum
union all
select
(SubQuery1.Calculated_IntialPart) as [Calculated_intialpart2],
(SubQuery1.Calculated_InitialPartDesc) as [Calculated_InitialPartDesc2],
[PartMtl1].[Company] as [PartMtl1_Company],
[PartMtl1].[PartNum] as [PartMtl1_PartNum],
[PartMtl1].[MtlPartNum] as [PartMtl1_MtlPartNum],
[PartMtl1].[QtyPer] as [PartMtl1_QtyPer],
cast([PartMtl1].[QtyPer] as int) * SubQuery1.[Calculated_PartMtl_TotalQtyPer] as [Calculated_PartMtl_TotalQtyPerChild]
from Erp.PartMtl as PartMtl1
inner join SubQuery1 as SubQuery1 on
PartMtl1.Company = SubQuery1.PartMtl_Company
and PartMtl1.PartNum = SubQuery1.PartMtl_MtlPartNum)
select
*
from SubQuery1 as SubQuery12If you go down the path of recreating. Let me know, there are few things you may want to consider with a BOM query.
The main one being that your anchor query could be narrowed down to only top level parts.
The only reason I don’t set the parameter for the anchor is because of the crappy implementation of parameters in a dashboard. If someone wants to make changes to the filters, they have to hit refresh, and every time you do that, you have to type in the top level part number again. It’s a huge pain. So, since the filters in the trackers basically act as a parameters (as explained by Jose), I just have the filter in the tracker and warn the user to not run it wide open. Not the best solution, but it works.
Is that what you are talking about? Or are you thinking of something else?
Something else.
Look at the anchor for this query notice that it is only using Top level parts ( we have them as class id)
This makes your CTE view more compact. I don’t know your table size, but it could make your query run quicker.
There is also a line in there to only show approved revs.
WITH PartsExplosion (PartNum, MtlPartNum, lv, QtyPer, TotalQtyPer, rowid, Name, SORT)
AS (
-- Anchor
SELECT Erp.Part.PartNum,
Erp.Part.PartNum,
0,
cast(Erp.Part.PurchasingFactor as int) as QtyPer,
cast(Erp.Part.PurchasingFactor as int) as TotalQtyPer,
Erp.Part.SysRowID,
CAST(Erp.Part.PartDescription as nvarchar(100)) as Name,
CAST('\' + Erp.Part.PartDescription as nvarchar(254)) as Sort
FROM Erp.Part
WHere ClassID in ('FGD', 'FGA')
-- Recursive Call
UNION ALL
SELECT BOM.PartNum, BOM.MtlPartNum, lv + 1, cast(BOM.QtyPer as int) as QtyPer, cast(BOM.QtyPer as int) * CTE.QtyPer as TotalQTYPart, BOM.SysRowID,
CAST(REPLICATE ('| ' , lv + 1 ) + BOM.PartDescription as nvarchar(100)),
CAST(cte.Sort + '\' + BOM.PartDescription as nvarchar(254))
FROM PartsExplosion CTE
JOIN (SELECT Erp.PartMtl.PartNum , Erp.PartMtl.MtlPartNum, cast(Erp.PartMtl.QtyPer as int) as QtyPer, Erp.PartMtl.SysRowID, b.PartDescription
FROM Erp.PartMtl Join Erp.Part AS b on Erp.PartMtl.MtlPartNum = b.PartNum
inner join dbo.PartRev on Erp.PartMtl.RevisionNum = dbo.PartRev.RevisionNum and Erp.PartMtl.PartNum = dbo.PartRev.PartNum and Erp.PartMtl.Company = dbo.PartRev.Company
WHERE dbo.PartRev.Approved = 1
) AS BOM
ON CTE.MtlPartNum = BOM.PartNum
)
select Name,pe.QtyPer, PE.TotalQtyPer, pe.PartNum,PE.MtlPartNum, lv,sort
FROM PartsExplosion AS PE
ORDER BY sortI would still like to grab a sub assembly and go down from there, so that won’t work for us.
Pretty much all of our rev’s are approved revs so that wouldn’t save us much.
Yes it does the Recursive Call goes down to nth level.
not if I want to start at the mid level and go down from there.
Well if you want your sub assembly to be the top level, then what you have is the way to go.
hmmm, so that means I just have to figure out how to multiply the qty per times the parent quantity as I go up the BOM. I know that I should be able to do this since you can add from the previous level to show what level the BOM is on (from the example in the users guide). I just have to figure out how to multiply instead.
I think small pieces is going to be my key. I’ll post what I figure out.
How do you deal with different revs?
How do you deal with when the part is on multiple boms?
This one didn’t work?
with [SubQuery1] as
(select
(PartMtl.PartNum) as [Calculated_IntialPart],
(Part1.PartDescription) as [Calculated_InitialPartDesc],
[PartMtl].[Company] as [PartMtl_Company],
[PartMtl].[PartNum] as [PartMtl_PartNum],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
[PartMtl].[QtyPer] as [PartMtl_QtyPer],
1 as [Calculated_PartMtl_TotalQtyPer]
from Erp.PartMtl as PartMtl
inner join Erp.Part as Part1 on
PartMtl.Company = Part1.Company
and PartMtl.PartNum = Part1.PartNum
union all
select
(SubQuery1.Calculated_IntialPart) as [Calculated_intialpart2],
(SubQuery1.Calculated_InitialPartDesc) as [Calculated_InitialPartDesc2],
[PartMtl1].[Company] as [PartMtl1_Company],
[PartMtl1].[PartNum] as [PartMtl1_PartNum],
[PartMtl1].[MtlPartNum] as [PartMtl1_MtlPartNum],
[PartMtl1].[QtyPer] as [PartMtl1_QtyPer],
cast([PartMtl1].[QtyPer] as int) * SubQuery1.[Calculated_PartMtl_TotalQtyPer] as [Calculated_PartMtl_TotalQtyPerChild]
from Erp.PartMtl as PartMtl1
inner join SubQuery1 as SubQuery1 on
PartMtl1.Company = SubQuery1.PartMtl_Company
and PartMtl1.PartNum = SubQuery1.PartMtl_MtlPartNum)
select
*
from SubQuery1 as SubQuery12
We don’t, everything is rev A…
If it’s on multiple BOM’s it will show up multiple times so the qty per adds up. It’s when the parent quantity isn’t one where this problem is showing up. I didn’t realize I had a problem until today.
I haven’t taken the time to parse out the code into a BPM. I don’t know how to run the SQL in the backend so I can’t test it just by copy paste. At first glance I think that the section below isn’t going to work because of the integer and the decimal mixing, but I’ll have to try it out on Monday.
cast([PartMtl1].[QtyPer] as int) * SubQuery1.[Calculated_PartMtl_TotalQtyPer] as
So I am back to trying to get this to work. Still having trouble. I made the simplest possible CTE to try and get this to work.
These are the levels I have.
I’m trying to get this to work, where the previous qty per will be multiplied by the current qty per, giving me a required qty.
If I just do this, it doesn’t error out, but it only shows the qty per. (not multiplied)
I’ve tried messing with the cast like Ken suggested, but I can’t get it to not give me an error, I’ve tried convert(decimal,…) that gives me the same thing. I can use either the CTE qty per, of the current quantity per on their own and it works fine, and shows the respective qty per, but I can’t get it to do the multiplication. What am I missing?
I got it figured out (finally)
I needed a cast as decimal(10,20) to keep the lengths the same. so on the CTE level I needed a calculated field
cast(PartMtl.QtyPer as decimal(20,10))
and on the Union all level, I needed
cast((CTE.Calculated_StartQty * PartMtl1.QtyPer)as decimal(20,10))
now it works.
Lots of google searching. Seems I’m not the only one that’s had this problem.
3 Likes
Hi Knash,
Your code works great, but I have a dobut, in this part of the code:
" Where dbo.PartRev.Approved = 1 " How can I do to get me the JOIN for the parts from the last revision whether or not it is approved?
Regards.
Welcome Jona,
This code has been updated over the years. check out this thread.
if you get stuck start a new thread and tag me. Just that way your issue won’t get lost in an old thread.
OK Let me check, Thanks for the reply.
Regards.
Thanks Knash, I resolved my problem in base with your example code.
Regards.