解决：Error while compiling statement: FAILED: ParseException line 11:5 missing KW_END at ‘substr‘ near

1. hive 执行sql，报错：

Error: Error while compiling statement: FAILED: ParseException line 11:5 missing KW_END at 'substr' near 'substr'
line 11:11 missing EOF at '(' near 'substr' (state=42000,code=40000)

2. sql如下：

create table szdsjsxm_sj.xxx_10000453_jieguo
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE
TBLPROPERTIES ('serialization.null.format' ='','serialization.encoding' ='GBK')
as
select distinct
a.xuhao  
,a.is_sz 
,case when a.id= c.id_nbr then '是'  
      when substr(a.id,1,12) = substr(c.id_nbr,1,12) then '是' 
      when substr(a.id,1,length(a.id)-6) = substr(c.id_nbr,1,length(c.id_nbr)-6) then '是'        
      substr(a.id,1,length(a.id)-8) = substr(c.id_nbr,1,length(c.id_nbr)-8) and substr(a.id,-4) = substr(c.id_nbr,-4) then '是'    
      else '否' end
from szdsjsxm_sj.xxx_10000453_01          a
left join szdsjsxm_sj.TW_PERS_LST_CUST_SZ c on c.month=202007 and c.day=20200712 and a.cust_id=c.cust_id
;

3. 解决：根据报错，定位到第11行第五列，少一个when语句：
   
   代码如下：

create table szdsjsxm_sj.xxx_10000453_jieguo
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE
TBLPROPERTIES ('serialization.null.format' ='','serialization.encoding' ='GBK')
as
select distinct
a.xuhao  
,a.is_sz 
,case when a.id= c.id_nbr then '是'  
      when substr(a.id,1,12) = substr(c.id_nbr,1,12) then '是' 
      when substr(a.id,1,length(a.id)-6) = substr(c.id_nbr,1,length(c.id_nbr)-6) then '是'        
      when substr(a.id,1,length(a.id)-8) = substr(c.id_nbr,1,length(c.id_nbr)-8) and substr(a.id,-4) = substr(c.id_nbr,-4) then '是'    
      else '否' end
from szdsjsxm_sj.xxx_10000453_01          a
left join szdsjsxm_sj.TW_PERS_LST_CUST_SZ c on c.month=202007 and c.day=20200712 and a.cust_id=c.cust_id
;

5. 反思总结：
   在Hive上编写SQL语句的时候，最好现在本地先预编写好内容，然后格式化一下，这样方便检查和在报错时的查错，还有就是要养成良好的编程习惯，诸如( ) ; . 之类的遗漏，是很不应该的低级错误。