question Re: ParseException line 1:162 cannot recognize input near '' '' '' in statement in Archives of Support Questions (Read Only)

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

colorsoflife — Sat, 19 Dec 2015 02:30:02 GMT

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??

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

colorsoflife — Sat, 19 Dec 2015 03:00:49 GMT

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??

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

gates — Sat, 19 Dec 2015 04:49:47 GMT

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?

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

deepesh1 — Sat, 19 Dec 2015 08:44:59 GMT

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.

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

newbieone — Sun, 17 Jul 2022 12:34:28 GMT

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