with as加insert into报错，解析器解析不到insert into后面的内容，目前有的是能执行成功的，到这一个卡住了，执行失败
-- truncate table dws.dws_wm_batch_stock_days_df;
with tmp1 as (select company_code,
company_name,
plant_code,
plant_name,
storage_location_code,
storage_location_name,
material_code,
material_name,
fi_year,
fi_period,
confirm_date,
case
when batch_created_date = '' or batch_created_date is null
then str_to_date('2020-01-01', '%y-%m-%d')
else batch_created_date end as batch_created_date,
batch_no,
stock_qty,
basic_unit_code,
material_type_code,
material_type_name,
material_group_code,
material_group_name,
price_control_code,
standard_price / coalesce(price_unit, 1) as standard_price,
move_avg_price / coalesce(price_unit, 1) as move_avg_price,
standard_stock_amt,
case
when substr(current_date(), 1, 7) = substr(confirm_date, 1, 7) then datediff(current_date(),
case
when batch_created_date = '' or batch_created_date is null
then str_to_date('2020-01-01', '%y-%m-%d')
else batch_created_date end) +
1
else datediff(date_add(confirm_date, interval 1 month), batch_created_date) end as day_num
from dws.dws_wm_batch_stock_amt_df),
tmp2 as (select t1.company_code,
t1.company_name,
t1.plant_code,
t1.plant_name,
t1.storage_location_code,
t1.storage_location_name,
t1.material_code,
t1.material_name,
t1.fi_year,
t1.fi_period,
t1.confirm_date,
t1.batch_created_date,
t1.batch_no,
t1.stock_qty,
t1.basic_unit_code,
t1.material_type_code,
t1.material_type_name,
t1.material_group_code,
t1.material_group_name,
t1.price_control_code,
t1.standard_price,
t1.move_avg_price,
t1.standard_stock_amt,
case when t1.day_num <= 30 then coalesce(t1.stock_qty, 0) else 0 end as stock_qty_30d,
case
when t1.day_num <= 60 and t1.day_num > 30 then coalesce(t1.stock_qty, 0)
else 0 end as stock_qty_31_60d,
case
when t1.day_num <= 90 and t1.day_num > 60 then coalesce(t1.stock_qty, 0)
else 0 end as stock_qty_61_90d,
case
when t1.day_num <= 120 and t1.day_num > 90 then coalesce(t1.stock_qty, 0)
else 0 end as stock_qty_91_120d,
case
when t1.day_num <= 180 and t1.day_num > 120 then coalesce(t1.stock_qty, 0)
else 0 end as stock_qty_121_180d,
case
when t1.day_num <= 365 and t1.day_num > 180 then coalesce(t1.stock_qty, 0)
else 0 end as stock_qty_181_365d,
case
when t1.day_num <= 730 and t1.day_num > 365 then coalesce(t1.stock_qty, 0)
else 0 end as stock_qty_366_730d,
case when t1.day_num > 730 then coalesce(t1.stock_qty, 0) else 0 end as stock_qty_excd_730d
from tmp1 t1)
insert into dws.dws_wm_batch_stock_days_df
select company_code,
plant_code,
storage_location_code,
material_code,
batch_no,
fi_year,
fi_period,
company_name,
plant_name,
material_name,
storage_location_name,
confirm_date as bnchmrk_date,
batch_created_date,
stock_qty,
basic_unit_code,
material_type_code,
material_type_name,
material_group_code,
material_group_name,
price_control_code,
standard_price,
move_avg_price,
standard_stock_amt,
stock_qty_30d,
stock_qty_31_60d,
stock_qty_61_90d,
stock_qty_91_120d,
stock_qty_121_180d,
stock_qty_181_365d,
stock_qty_366_730d,
stock_qty_excd_730d,
case
when price_control_code = 'V' then coalesce(stock_qty_30d, 0) * move_avg_price
when price_control_code = 'S' then coalesce(stock_qty_30d, 0) * standard_price
else 0 end as standard_stock_amt_30d,
case
when price_control_code = 'V' then coalesce(stock_qty_31_60d, 0) * move_avg_price
when price_control_code = 'S' then coalesce(stock_qty_31_60d, 0) * standard_price
else 0 end as standard_stock_amt_31_60d,
case
when price_control_code = 'V' then coalesce(stock_qty_61_90d, 0) * move_avg_price
when price_control_code = 'S' then coalesce(stock_qty_61_90d, 0) * standard_price
else 0 end as standard_stock_amt_61_90d,
case
when price_control_code = 'V' then coalesce(stock_qty_91_120d, 0) * move_avg_price
when price_control_code = 'S' then coalesce(stock_qty_91_120d, 0) * standard_price
else 0 end as standard_stock_amt_91_120d,
case
when price_control_code = 'V' then coalesce(stock_qty_121_180d, 0) * move_avg_price
when price_control_code = 'S' then coalesce(stock_qty_121_180d, 0) * standard_price
else 0 end as standard_stock_amt_121_180d,
case
when price_control_code = 'V' then coalesce(stock_qty_181_365d, 0) * move_avg_price
when price_control_code = 'S' then coalesce(stock_qty_181_365d, 0) * standard_price
else 0 end as standard_stock_amt_181_365d,
case
when price_control_code = 'V' then coalesce(stock_qty_366_730d, 0) * move_avg_price
when price_control_code = 'S' then coalesce(stock_qty_366_730d, 0) * standard_price
else 0 end as standard_stock_amt_366_730d,
case
when price_control_code = 'V' then coalesce(stock_qty_excd_730d, 0) * move_avg_price
when price_control_code = 'S' then coalesce(stock_qty_excd_730d, 0) * standard_price
else 0 end as standard_stock_amt_excd_730d,
case
when greatest(stock_qty_30d, stock_qty_31_60d, stock_qty_61_90d, stock_qty_91_120d,
stock_qty_121_180d,
stock_qty_181_365d, stock_qty_366_730d, stock_qty_excd_730d) > 0 then '1'
else '0' end as is_dormant,
if(greatest(stock_qty_91_120d, stock_qty_121_180d, stock_qty_181_365d, stock_qty_366_730d,
stock_qty_excd_730d) >
0, stock_qty_91_120d + stock_qty_121_180d + stock_qty_181_365d + stock_qty_366_730d +
stock_qty_excd_730d,
0) as dormant_stock_qty,
case
when price_control_code = 'V' then
if(greatest(stock_qty_91_120d, stock_qty_121_180d, stock_qty_181_365d, stock_qty_366_730d,
stock_qty_excd_730d) >
0, stock_qty_91_120d + stock_qty_121_180d + stock_qty_181_365d + stock_qty_366_730d +
stock_qty_excd_730d,
0) * coalesce(move_avg_price, 0)
when price_control_code = 'S' then
if(greatest(stock_qty_91_120d, stock_qty_121_180d, stock_qty_181_365d, stock_qty_366_730d,
stock_qty_excd_730d) >
0, stock_qty_91_120d + stock_qty_121_180d + stock_qty_181_365d + stock_qty_366_730d +
stock_qty_excd_730d,
0) * coalesce(standard_price, 0)
else 0 end as dormant_standard_stock_amt
from tmp2 b
;
