自我LV1

如下code:

WITH RecursiveCTE AS (
    -- 基本查詢，選擇起始點
    SELECT MD001, MD003, CAST(MC004 AS DECIMAL(18, 4)) AS USED
    FROM [TK].dbo.BOMMD
    INNER JOIN [TK].dbo.BOMMC ON MC001 = MD001
    WHERE MD001 = '401'
	
    UNION ALL

    -- 遞迴查詢，選擇下一級
    SELECT D.MD001, D.MD003, CAST(C.MC004 AS DECIMAL(18, 4))*R.USED
 AS USED
    FROM [TK].dbo.BOMMD D
    INNER JOIN [TK].dbo.BOMMC C ON C.MC001 = D.MD001
    INNER JOIN RecursiveCTE R ON R.MD003 = D.MD001
)
-- 最終查詢，選擇遞迴結果
SELECT *
FROM RecursiveCTE;

 

會出錯

「遞迴查詢 "RecursiveCTE" 的資料行 "USED" 當中，錨點與遞迴部分之間的類型不符」

 

主因是USED的型別不對

雖然在範例中已轉成DECIMAL(18, 4)

但是在遞迴計算USED

如果除不盡，用DECIMAL(18, 4)是不對的

應該要轉成 FLOAT

 

 

 

自我LV~