Can we create a permanent table using temporary tables-CodePudding

with temp1 as
(select cust_id,
case
    when cm_since_dt between '2017-01-01' and '2017-12-31' then '2017'
    when cm_since_dt between '2018-01-01' and '2018-12-31' then '2018'
    when cm_since_dt between '2019-01-01' and '2019-12-31' then '2019'
    when cm_since_dt between '2020-01-01' and '2020-12-31' then '2020'
    when cm_since_dt between '2021-01-01' and '2021-12-31' then '2021'
    when cm_since_dt between '2022-01-01' and '2022-12-31' then '2022'
    else 'old cm'
end as cm_since
from customer_demographics_table
),

temp2 as
(select cust_id, min(cm_since)
from temp1
group by cust_id)

create table cm_demographics as
select *
from temp2;

this gives a ParseException : cannot recognize input near 'create' 'table' in statement is there a workaround for it? I'm working on Hiveterminal.

CodePudding user response：

You just need to move your create statement on first line -

CREATE TABLE cm_demographics AS
WITH temp1 AS
(SELECT cust_id,
case
    when cm_since_dt between '2017-01-01' and '2017-12-31' then '2017'
    when cm_since_dt between '2018-01-01' and '2018-12-31' then '2018'
    when cm_since_dt between '2019-01-01' and '2019-12-31' then '2019'
    when cm_since_dt between '2020-01-01' and '2020-12-31' then '2020'
    when cm_since_dt between '2021-01-01' and '2021-12-31' then '2021'
    when cm_since_dt between '2022-01-01' and '2022-12-31' then '2022'
    else 'old cm'
end as cm_since
from customer_demographics_table
),
temp2 as
(select cust_id, min(cm_since)
   from temp1
  group by cust_id)
select *
from temp2;