CTE recursive specification

Question

This is my first question, so forgive me if I'm not clear enough.

I have been instructed to obtain the total cost of all components for the Assembly (specification). Basically, I want to query a table that contains the distribution of purchase orders for components, so I can get the value that is currently associated with an unpublished assembly.

This is difficult because the assembly component may be the assembly itself, in which case I need to query another table that contains information about whether other assemblies are associated with this main one. (I have checks to make sure that the redistribution does not occur, but it is possible that nothing was allocated and that OK). In this case, query the distribution table of the purchase orders for the components of the assembly number THAT and add these costs to total for the parent assembly.

I am using CTE for the first time and do not have much luck. Can someone help determine what I'm doing wrong here?

An anchor draws all components except subcomplexes, and their costs per unit and quantity from PO for the main assembly. I am trying to determine the value for.

The recursive part must draw components, cost, and quantity for assemblies that were “connected” by existing ones in table BM10200_AssemblyQtyDetail. If the parent assembly is in the TRANNUM column, then the TRX_ID of this row is a related assembly, which is a component of the assembly in the main assembly.

USE HT
GO
WITH BOMCost (Assembly, Component, PriceFromPO, Qty, BOMLevel)
AS
(
-- Anchor member definition
SELECT asl.TRX_ID, asl.ITEMNMBR, asl.UNITCOST, asl.SERLTQTY,
0 AS BOMLevel
FROM HT.DBO.BM10400 AS asl
WHERE asl.TRX_ID = 'ASM0002909'
UNION ALL
-- Recursive member definition
SELECT asl.TRX_ID, asl.ITEMNMBR, asl.UNITCOST, asl.SERLTQTY,
    BOMLevel + 1
FROM HT.DBO.BM10400 AS asl
INNER JOIN HT.DBO.BM10200_AssemblyQtyDetail AS bqd 
    ON asl.TRX_ID = bqd.TRANNUM
INNER JOIN BOMCost AS bc
    ON bqd.TRX_ID = bc.Assembly
)


-- Statement that executes the CTE
SELECT Assembly, Component, PriceFromPO, Qty, BOMLevel
FROM BOMCost

To repeat, the problem here is that it ONLY returns the components of the main assembly without any assembly costs. These are simply top-level components. I have an entry in the link table linking the assembly for the assembly in this BM to the main assembly, but it does not pull the components for this assembly number. I think this may have something to do with recursive sections. Any help is appreciated!

. ASM0002909 - , . ASM0002914 ASM0002909 . , , , , . (SERLTQTY * UNITCOST) .

, . ASM0002914.

sqlfiddle , SQL-, , , . http://sqlfiddle.com/#!3/bd1b98/6

Answer 1

, - , , , JOIN :

INNER JOIN BOMCost AS bc ON bqd.TRX_ID = bc.Assembly

ASM0002909 ASM0002914, .

Update:

WITH BOMCost (Assembly, Component, PriceFromPO, Qty, BOMLevel)
AS
(
-- Anchor member definition
    SELECT asl.TRX_ID, asl.ITEMNMBR, asl.UNITCOST, asl.SERLTQTY,
    0 AS BOMLevel
    FROM DBO.BM10400 AS asl
    WHERE asl.TRX_ID = 'ASM0002909'
    UNION  ALL
-- Recursive member definition
    SELECT asl.TRX_ID, asl.ITEMNMBR, asl.UNITCOST, asl.SERLTQTY,
        BOMLevel + 1
    FROM DBO.BM10400 AS asl
    INNER JOIN DBO.BM10200_AssemblyQtyDetail AS bqd 
        ON asl.TRX_ID = bqd.TRX_ID
    INNER JOIN BOMCost AS bc
        ON bqd.TRANNUM = bc.Assembly

)


-- Statement that executes the CTE
SELECT DISTINCT Assembly, Component, PriceFromPO, Qty, BOMLevel
FROM BOMCost

: SQL Fiddle