Insert into a tale from a Select query that use WITH Clause in DB2

Question

I am trying to insert into a table results of a SELECT statement that uses WITH clause but get syntax errors. Am I missing something simple or Insert statements are not allowed with WITH clauses?

with 
  tser_indx as 
    (SELECT ROW_NUMBER() OVER (ORDER BY BUSDATE ASC) AS ROWID, ITEMID, BUSDATE, PRICE)
     FROM prices_history where ITEMID =  12876),

  VALS as 
     (select P1.ITEMID, P1.BUSDATE , P1.PRICE, P1.PRICE / P2.PRICE) as ret 
    FROM  tser_indx p1, tser_indx p2 
    WHERE p1.rowid = p2.rowid + 1)

INSERT INTO RETURNS (ITEMID, TRADEDATE, PRICE, RETURN)
SELECT ITEMID, BUSDATE, PRICE, RET FROM VALS

I get an error stating that SELECT statement is expected instead of INSERT. Can't seem to fool the DB2 into taking this statement. I tried rewriting it where as

INSERT INTO RETURNS (...) SELECT * FROM (WITH ...)

Still didn't work.

Any suggestion will be appreciated.

Answer 1

I believe this syntax should work:

INSERT INTO RETURNS (ITEMID, TRADEDATE, PRICE, RETURN)
with 
  tser_indx as 
    (SELECT ROW_NUMBER() OVER (ORDER BY BUSDATE ASC) AS ROWID, ITEMID, BUSDATE, PRICE)
     FROM prices_history where ITEMID =  12876
    ),
  VALS as 
     (select P1.ITEMID, P2.BUSDATE , P1.PRICE, P1.PRICE / P2.PRICE) as ret 
      FROM  tser_indx p1, tser_indx p2 
      WHERE p1.rowid = p2.rowid + 1
     )
SELECT ITEMID, BUSDATE, PRICE, RET FROM VALS;

In DB2, the with is part of the select.