Hi

I have a query to calculate product BOM list based on data retieved from ERP-system:

--USE [Site_MyDB]
-- I removed non-essential part of procedure here
DECLARE
@prod_id int

SELECT @prod_id =MIN(prod_id)FROM dbo.products WHERE art_artnr IN('011000') -- for testing only 1 product is processed

WHILE @prod_id IsNotNull

BEGIN
;
WITH struktur_cte AS
(
SELECT
@prod_id AS prod_id,
UPPER(CAST(Right('00'+ utilities.dbo.udf_NumToNBase32(st_rad,2,2),2) AS varchar(52))) AS prod_pos_nr,
@prod_id AS prod_art_id,
1 AS st_level,
st_id,
st_hart_id,
st_rad,
st_art_id,
st_pos_nr,
st_top,
st_kvant,
st_kvant_extra,
st_kvant_inst,
st_typ
FROM


ERPMirror.dbo.struktur
WHERE
st_hart_id = @prod_id
UNION ALL
SELECT
@prod_id AS prod_id,
UPPER(CAST(ecte.prod_pos_nr +Right('00'+ utilities.dbo.udf_NumToNBase32(e.st_rad,2,2),2) AS varchar(52))) AS prod_pos_nr,
@prod_id AS prod_art_id,
ecte.st_level+1 AS st_level,
e.st_id,
e.st_hart_id,
e.st_rad,
e.st_art_id,
e.st_pos_nr,
e.st_top,
e.st_kvant,
ecte.st_kvant AS st_kvant_ecte,
e.st_kvant_extra,
e.st_kvant_inst,
e.st_typ
FROM
ERPMirror.dbo.struktur e INNER JOIN struktur_cte ecte ON ecte.st_art_id = e.st_hart_id
)
SELECT * FROM struktur_cte ORDER BY prod_pos_nr option(maxrecursion 10000)

SELECT
@prod_id =MIN(prod_id) FROM dbo.products WHERE prod_id > @prod_id AND art_artnr IN('011000')
END
--

An example what I get now, and what I need:
P.e. for prod_pos_nr = '010J10' art_artnr = '180640' and st_kvant = 2. This means, that for product '011000' we must have 2 parts '180640' on this BOM row. These parts are manufactured, so we must have some parts for this. These parts are on positions '010J1001' - '010J1005', and st_kvant for them is currently 1 or 2 - which is a quantity needed to manufacture 1 part '180640'. I need those quantities multiplied with 2 instead.
Now on position '010J1002' is a part '180631' with st_kvant = 1 (which must be multiplied by 2), and which is also manufactured. To it belong 2 next BOM positions on next BOM level - '101J100201' and '101J100201'.
On position '101J100201' is a part 'MK13/' with st_kvant = 0.0483, which must be multiplied by (2*1).
On position '101J100202' is a pert 'SCR BRASS' (brass scrap) with st-kvant = -0.036, which also must be multiplied by (2*1).

I tried to add a column into both parts of recursive query, where I read the quantity from previous level ("st_kvant AS stkvant_ecte" and "ecte.st_kvant AS dt_kvant_ecte"). It worked. But when I tried to multiply quantities for current and previous level in second query in UNION instead ("e.st_kvant*ecte.st_kvant AS st_kvant_ecte"), I got an error "Types don't match between the anchor and recursive part...".

I have used recursive queries a couple of times until now, but it feels the water is too deep for me now