sql - HIVE从子查询创建表

Question

这是我的代码:

create table sw_tmp6_gar_crm as
SELECT * FROM(
  select as_fid_x_gara.dat_fine_perio        as dat_fine_perio,
         as_fid_x_gara.cod_soc               as cod_soc, 
         as_fid_x_gara.cod_kto_gar           as cod_kto_gar, 
         as_fid_x_gara.cod_fido              as cod_fido,
         fid.dat_delib             as dat_delib, 
         fid.dat_scad              as dat_scad  

  from   it_soc_x_fv,
         as_fid_x_gara   ,
         rt_fidi         
  where  it_soc_x_fv.flg_tp_soc     in ('C','N')
    and  as_fid_x_gara.dat_fine_perio   = 2008-03-06
    and  as_fid_x_gara.cod_soc          = it_soc_x_fv.cod_soc
    and  rt_fidi.dat_fine_perio   = as_fid_x_gara.dat_fine_perio
    and  rt_fidi.cod_soc          = as_fid_x_gara.cod_soc       
    and  rt_fidi.cod_fido_tecnico = as_fid_x_gara.cod_fido 
     )         
;

我收到以下错误:

error while compiling statement: failed: parseexception line 10:9 cannot recognize input near 'it_soc_x_fv' ',' 'as_fid_x_gara' in from source

你能帮我吗？

Answer 1

您需要为子查询提供一个alias名称。下面的一个应该工作。

create table sw_tmp6_gar_crm as
SELECT * FROM(
  select as_fid_x_gara.dat_fine_perio        as dat_fine_perio,
         as_fid_x_gara.cod_soc               as cod_soc, 
         as_fid_x_gara.cod_kto_gar           as cod_kto_gar, 
         as_fid_x_gara.cod_fido              as cod_fido,
         fid.dat_delib             as dat_delib, 
         fid.dat_scad              as dat_scad  

  from   it_soc_x_fv,
         as_fid_x_gara   ,
         rt_fidi         
  where  it_soc_x_fv.flg_tp_soc     in ('C','N')
    and  as_fid_x_gara.dat_fine_perio   = 2008-03-06
    and  as_fid_x_gara.cod_soc          = it_soc_x_fv.cod_soc
    and  rt_fidi.dat_fine_perio   = as_fid_x_gara.dat_fine_perio
    and  rt_fidi.cod_soc          = as_fid_x_gara.cod_soc       
    and  rt_fidi.cod_fido_tecnico = as_fid_x_gara.cod_fido 
     ) tmp         
;

但是，正如评论中所提到的，您不需要子查询。