Insert into fairy tale from Select query that uses WITH clause in DB2
I am trying to insert into the results of a table a SELECT statement that uses the WITH clause, but I get syntax errors. Am I missing something simple, or are WITH clauses not allowed in Insert statements?
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 am getting an error indicating that a SELECT statement is expected instead of an INSERT. Can't seem to trick DB2 into accepting this statement. I tried to rewrite it where
INSERT INTO RETURNS (...) SELECT * FROM (WITH ...)
Still not working.
Any suggestion would be appreciated.
+3
source to share
1 answer
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;
The DB2 with
is part of select
.
+7
source to share