Cte blocks sql insert

Question

我的代码不工作，如果我有插入。否则，如果我删除插入部分，它会正常工作(只是选择)此外，删除cte还有助于避免单独使用

插入。

WITH temp_cte as (select
item_id AS item_id,
item_name AS item_name,
item_price AS item_price,
date(created_dttm) AS valid_from_dt,
LEAD(date(created_dttm), 1, '9999-12-31') 
OVER (PARTITION BY item_id ORDER BY date(created_dttm)) as next_price
from item_prices)

insert into dict_item_prices (item_id, item_name, item_price, valid_from_dt, valid_to_dt)


SELECT item_id,
item_name,
item_price,
valid_from_dt,
(CASE  next_price
WHEN '9999-12-31' THEN '9999-12-31'
ELSE DATE_ADD(next_price,interval -1 day)
END) as valid_to_dt
from temp_cte
select * from dict_item_prices;

即使我不使用我的cte，它仍然失败

Answer 1

WITH子句是select的一部分，所以您的查询看起来像

insert into dict_item_prices (item_id, item_name, item_price, valid_from_dt, valid_to_dt)
WITH temp_cte as (select
item_id AS item_id,
item_name AS item_name,
item_price AS item_price,
date(created_dttm) AS valid_from_dt,
LEAD(date(created_dttm), 1, '9999-12-31') 
OVER (PARTITION BY item_id ORDER BY date(created_dttm)) as next_price
from item_prices)     

SELECT item_id,
item_name,
item_price,
valid_from_dt,
(CASE  next_price
WHEN '9999-12-31' THEN '9999-12-31'
ELSE DATE_ADD(next_price,interval -1 day)
END) as valid_to_dt
from temp_cte;
select * from dict_item_prices;