SQL Server Query for BOM Quantity

Question

I think this is an issue of CAST correctly on q but not sure how. Quantity is defined as (numeric(24, 5), not null) Thank you for your help.

with all_item_counts as 
(
    select 
        ParentId, Id, Quantity as q, 0 as level 
    from 
        dbo.MBOM
    union all 
    select 
        all_item_counts. ParentId, dbo.MBOM.Id, Quantity * q, level + 1 
    from 
        all_item_counts
    join 
        dbo.MBOM on dbo.MBOM. ParentId = all_item_counts.Id
)
select 
    ParentId, Id, sum(q) 
from 
    all_item_counts
group by 
    ParentId, Id

I get this error:

Msg 240, Level 16, State 1, Line 1
Types don't match between the anchor and the recursive part in column "q" of recursive query "all_item_counts".

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Oct 24 '21 at 21:31

score 0 · Answer 1 · answered Oct 24 '21 at 22:26

I'm not too sure why you have the multiply in the first place. If you want the total for each root, try this:

with all_item_counts as 
(
    select 
        Id As RootId, ParentId, Id, Quantity as q, 0 as level 
    from 
        dbo.MBOM WHERE ParentId is NULL
    union all 
    select 
         all_item_counts.RootId,dbo.MBOM.ParentId,dbo.MBOM.Id, dbo.MBOM.Quantity, level + 1 
    from 
        all_item_counts
    join 
        dbo.MBOM on dbo.MBOM. ParentId = all_item_counts.Id
)
select 
    RootId, ParentId, Id, q
from 
    all_item_counts

That gives me a result like this:

RootId	ParentId	Id	q
1	NULL	1	1.00000
1	1	2	2.00000
1	2	3	4.00000
1	3	4	8.00000
1	4	5	16.00000
1	5	6	32.00000

Which you can easily group and sum by RootId. If you really need the product this post has a technique for doing that involving LOG and EXP functions

SQL Server Query for BOM Quantity

1 Answers1