How to implement recursion in SQL Developer?

Question

I have a table with the following columns:

ID, date, amount, amount2

Column amount2 has values only in the first row. I need to recursively calculate each row of column amount2 using values from previous row of this column and also column amount

second row for amount2 should be: second row amount +first row value of amount - first row value of amount2 third row for amount2 should be: third row amount +second row value of amount - second row value of amount2...

I tried to use following code, but it does not work:

SELECT   
    first_column,  
    CASE   
        WHEN second_column IS NULL THEN LAG(first_column, 1, first_column) OVER (ORDER BY row_id) - LAG(first_column, 1, first_column) OVER (ORDER BY row_id DESC)  
        ELSE second_column  
    END AS calculated_second_column
from table;

Reproducible example:

CREATE TABLE your_table_name (  
    id INT,  
    date DATE,  
    amount DECIMAL(10,2),  
    amount1 DECIMAL(10,2)  
); 
INSERT INTO your_table_name (id, date, amount, amount1)  
VALUES  
    (234, '2020-01-01', 4, 10),  
    (234, '2020-01-02', 7, NULL),  
    (234, '2020-01-03', 10, NULL),  
    (234, '2020-01-04', 15, NULL),  
    (234, '2020-01-05', 6, NULL);  

I expect to have in column amount1; 10, 7+4-10=1, 10+7-1=16, 15+10-16=9, 6+15-9=12

Answer 1

You can use:

SELECT id, dt, amount, amount1
FROM (
  SELECT t.*, ROW_NUMBER() OVER (PARTITION BY id ORDER BY dt) AS rn
  FROM   your_table_name t
)
MODEL
  PARTITION BY (id)
  DIMENSION BY (rn)
  MEASURES (dt, amount, amount1)
  RULES (
    amount1[rn>1] = amount[cv(rn)] + amount[cv(rn)-1] - amount1[cv(rn) - 1]
  );

or your logic simplifies to diff = amount - amount1 for the first row then every odd row is amount - diff and every even row is amount + diff:

SELECT id,
       dt,
       amount,
       CASE
       WHEN MOD(ROW_NUMBER() OVER (PARTITION BY id ORDER BY dt), 2) = 0
       THEN +1
       ELSE -1
       END
       * FIRST_VALUE(amount - amount1) OVER (PARTITION BY id ORDER BY dt)
       + amount AS amount1
FROM   your_table_name
ORDER BY id, dt;

Which, for the sample data:

CREATE TABLE your_table_name (
  id      INT,
  dt      DATE,
  amount  DECIMAL(10,2),
  amount1 DECIMAL(10,2)
);

INSERT INTO your_table_name (id, dt, amount, amount1)
VALUES
(234, DATE '2020-01-01',  4,   10),
(234, DATE '2020-01-02',  7, NULL),
(234, DATE '2020-01-03', 10, NULL),
(234, DATE '2020-01-04', 15, NULL),
(234, DATE '2020-01-05',  6, NULL);

Both output:

ID	DT	AMOUNT	AMOUNT1
234	2020-01-01 00:00:00	4	10
234	2020-01-02 00:00:00	7	1
234	2020-01-03 00:00:00	10	16
234	2020-01-04 00:00:00	15	9
234	2020-01-05 00:00:00	6	12

fiddle