ParseException line 1:162 cannot recognize input near '' '' '' in statement

[#1]

I am getting error when running the below query ;

Error occurred executing hive query: Error while compiling statement: FAILED: ParseException line 1:162 cannot recognize input near '<EOF>' '<EOF>' '<EOF>' in statement

With Q as (select CLAIMNUMBER,EXP_ID,EXP_COVERAGE,VEHICLE_STYLE From orc_claiminfo Where VEHICLE_STYLE != "" AND EXP_COVERAGE ="Property Damage - Vehicle Damage");

select * from Q;

Select statement works all well, if I execute separate.

Anyone knows whats the issue??

[#2]

I have some finding here, if I run only the statement

With Q as (select CLAIMNUMBER,EXP_ID,EXP_COVERAGE,VEHICLE_STYLE From orc_claiminfo Where VEHICLE_STYLE != "" AND EXP_COVERAGE ="Property Damage - Vehicle Damage")

I get the error. But if I run with select * from Q, it works (below).

With Q as (select CLAIMNUMBER,EXP_ID,EXP_COVERAGE,VEHICLE_STYLE From orc_claiminfo Where VEHICLE_STYLE != "" AND EXP_COVERAGE ="Property Damage - Vehicle Damage")

select * from Q

But I don't want to have select immediately after getting the data set. I want to use it later. Any resolution??

[#3]

You're saying if you put SQL statements between "with Q..." and "select *..." then you get the error? If so can you share an example SQL statement that is between that causes the error?

[#4 · Accepted solution]

Perhaps what you want is a CTAS statement not a CTE.

create table q as select CLAIMNUMBER,EXP_ID,EXP_COVERAGE,VEHICLE_STYLE from orc_claiminfo where VEHICLE_STYLE != "" AND EXP_COVERAGE = "Property Damage - Vehicle Damage"

This way you can use table q later.

[#5]

Hi @colorsoflife 

Consider using CTE is possible, with the sequence as below for your references.

This is Hive script incorporate into Ozzie workflow.

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

with test_CTE as 
(select  *  from table1),

testone_CTE as
(select col1, col2, col3 from test_CTE)

insert into table mytablename partition(biz_dt)

select  col1 as name1, col2 as name2, col3 as name3 from testOne_CTE