SQL - Recursive Calculation Solution Required

Question

Good day,

Could someone please assist with how to achieve the below. So I need to take the first "Addition" which is the "Total_COB_Growth" + "Sum_of_Previous_Interest" where the "Sum_of_Previous_Interest" is dependent on the LAG of "Additions" if that makes sense. I can't get it right in SQL to have the previous value drag down on each consecutive line. (LAG() OVER (PARTITION BY etc.) returns zeros once it hits the 2/3 line:

Table

My Table:

UniqueKey	Pol_No	New_Total_Rate	Total_COB_Growth	Duration
DE000724266001001016	724266	8	0	0
DE000724266001001017	724266	8	0	0
DE000724266001001018	724266	8	-9811.4	11
DE000724266001001019	724266	8	0	0
DE000724266001001020	724266	10	0	1
DE000724266001001021	724266	10	0	0

So the formulas in the green columns depend on each other and I am having a tough time figuring out how to code this in SQL.

First Formula (used in Excel):

Additions =H4 + ROUND(E4,0)

Additions

Second Formula (used in Excel):

Previous_Interest =ROUNDDOWN(IF(C4=C3,G3*(1+D4/100)^(F4/12),0),0)

Previous Interest

What I want (Result after 2 excel formulas):

UniqueKey	Pol_No	New_Total_Rate	Total_COB_Growth	Duration	Additions	Previous_Interest
DE000724266001001016	724266	8	0	0	0	0
DE000724266001001017	724266	8	0	0	0	0
DE000724266001001018	724266	8	-9811.4	11	-9811	0
DE000724266001001019	724266	8	0	0	-9811	-9811
DE000724266001001020	724266	10	0	1	-9889	-9889
DE000724266001001021	724266	10	0	0	-9889	-9889

My feeling is that this would be something achieved with CTE's but I have absolutely zero experience with those so really looking forward to solution for this.

Thanks in advance, this has been breaking me.

I've tried multiple subqueries to try and get the values copied over the partitioned lines but that does not help.

We use DBeaver on IBM i.

This is what I have tried without success:

WITH #cte AS (
SELECT 
    UNIQUEKEY, 
    POL_NO, 
    NEW_TOTAL_RATE, 
    TOTAL_COB_GROWTH, 
    DURATION,
    0 AS "Additions to date TF",
    0 AS "Sum of previous with interest",
    1 AS RowNum
FROM 
    QRYLIB.temp_Test
UNION ALL
SELECT 
    t.UNIQUEKEY, 
    t.POL_NO, 
    t.NEW_TOTAL_RATE, 
    t.TOTAL_COB_GROWTH, 
    t.DURATION,
    CASE 
        WHEN t.DURATION = 0 THEN 
            LAG(#cte."Sum of previous with interest", 1, 0) OVER (PARTITION BY t.POL_NO ORDER BY t.UNIQUEKEY) + ROUND(t.TOTAL_COB_GROWTH, 0)
        ELSE 
            LAG(#cte."Sum of previous with interest", 1, 0) OVER (PARTITION BY t.POL_NO ORDER BY t.UNIQUEKEY)
    END AS "Additions to date TF",
    CASE 
        WHEN t.POL_NO = LAG(t.POL_NO, 1, 0) OVER (ORDER BY t.UNIQUEKEY) THEN 
            ROUND(LAG(#cte."Sum of previous with interest", 1, 0) OVER (ORDER BY t.UNIQUEKEY) * POWER(1 + t.TOTAL_COB_GROWTH / 100, t.DURATION / 12), 0)
        ELSE 
            0
    END AS "Sum of previous with interest",
    #cte.RowNum + 1 AS RowNum
FROM 
    QRYLIB.temp_Test t
JOIN 
    #cte ON t.UNIQUEKEY = #cte.UNIQUEKEY + 1
)
SELECT 
    UNIQUEKEY, 
    POL_NO, 
    NEW_TOTAL_RATE, 
    TOTAL_COB_GROWTH, 
    DURATION,
    "Additions to date TF",
    "Sum of previous with interest"
FROM #cte
WHERE RowNum = 1
ORDER BY UNIQUEKEY;

Error Received:

SQL Error [42908]: [SQL0343] Column list not valid for table.

Answer 1

Did you look at the details of the error?

Causes:

• The column name list must be specified following the table name of the common table expression. &1 is the common table expression name.
• The sequence column name and the set cycle column name cannot be referenced in the column list of the recursive common table expression. &1 is the sequence column name or the set cycle column name. For a CREATE INDEX with a RENAME clause:
• The key column must be included in the rename column list.

Try naming the columns like so:

WITH #cte (uniquekey, pol_no, new_total_rate,total_cob_growth
           , duration, "Additions to date TF", "Sum of previous with interest"
           , rownum)
 AS (
SELECT 
    UNIQUEKEY, 
    POL_NO, 
    NEW_TOTAL_RATE, 
    TOTAL_COB_GROWTH, 
    DURATION,
    0 AS "Additions to date TF",
    0 AS "Sum of previous with interest",
    1 AS RowNum
FROM 
    QRYLIB.temp_Test
UNION ALL
<..snip...>