La ruche SQL: à la Fois à gauche et à droite alias rencontrées dans la JOINTURE

Je valide cette requête T-SQL:

select t1.*
    ,case when s1.period is not null then 'Y' else 'N' end as flag_cur
    ,case when s2.period is not null then 'Y' else 'N' end as flag_prev
    ,s1.cutoff_date as cutoff_date_cur ,s1.cutoff_dtkey as cutoff_dtkey_cur 
    ,s2.cutoff_date as cutoff_date_prev ,s2.cutoff_dtkey as cutoff_dtkey_prev 
into #tmp_leads2
from #tmp_leads t1
left join #param s1 on s1.period = '(a) Current' and s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date 
left join #param s2 on s2.period = '(b) Previous' and s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date

J'ai essayé de le ré-écrire pour la Ruche (v0.13) comme:

create table tmp_leads2 as  
select t1.*
    ,case when s1.period is not null then 'Y' else 'N' end as flag_cur
    ,case when s2.period is not null then 'Y' else 'N' end as flag_prev
    ,s1.cutoff_date as cutoff_date_cur ,s1.cutoff_dtkey as cutoff_dtkey_cur 
    ,s2.cutoff_date as cutoff_date_prev ,s2.cutoff_dtkey as cutoff_dtkey_prev 
from tmp_leads t1
left join param s1 on s1.period = '(a) Current'  and s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date 
left join param s2 on s2.period = '(b) Previous' and s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date ;

Mais j'obtiens l'erreur:

Error occurred executing hive query: OK FAILED: SemanticException [Error 10017]: Line 8:53 Both left and right aliases encountered in JOIN 'CreatedDate'

Je vois les champs, il est en train de parler, mais je ne suis pas sûr de la façon de ré-écrire ce tout en gardant les résultats de la requête identique.

InformationsquelleAutor Hack-R | 2016-03-15
  1. 3

    Le problème vient de l'inégalité des conditions dans le joins. Cela pose un problème. Ce qui suit est probablement suffisante pour vos besoins:

    create table tmp_leads2 as  
    select t1.*,
           (case when s1.period is not null then 'Y' else 'N' end) as flag_cur,
           (case when s2.period is not null then 'Y' else 'N' end) as flag_prev,
           s1.cutoff_date as cutoff_date_cur, s1.cutoff_dtkey as cutoff_dtkey_cur ,
           s2.cutoff_date as cutoff_date_prev, s2.cutoff_dtkey as cutoff_dtkey_prev 
    from tmp_leads t1 left join
         param s1
         on s1.period = '(a) Current' left join  
         param s2
         on s2.period = '(b) Previous'
    where (s1.begin_date is null or s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date) or
          (s2.begin_date is null or s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date);

    Ce n'est pas exactement équivalent. Il fait l'hypothèse que si un paramètre est dans la table, alors il est dans la table pour toutes les dates. Ce qui pourrait être une hypothèse raisonnable. Si non, alors une requête plus complexe serait nécessaire.

    InformationsquelleAutor Gordon Linoff
  2. 1

    Ici est quelque chose qui ne serait pas une cause de jointure interne ou alias question et de vous donner les résultats escomptés dans la Ruche

       create table tmp_leads2 as  
select final.*
,case when s1period is not null then 'Y' else 'N' end as flag_cur
,case when s2period is not null then 'Y' else 'N' end as flag_prev
from
(select t1.*,
max(case when  s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date then s1.peroid else null end) as s1period,
max(case when  s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date then s1.cutoff_date else null end) as cutoff_date_cur,
max(case when  s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date then s1.cutoff_dtkey else null end) as cutoff_dtkey_cur,
max(case when  s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date then s2.peroid else null end) as s2period,
max(case when  s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date then s2.cutoff_date else null end) as cutoff_date_prev,
max(case when  s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date then s2.cutoff_dtkey else null end) as cutoff_dtkey_prev,
from tmp_leads t1
left join param s1 on s1.period = '(a) Current'  
left join param s2 on s2.period = '(b) Previous' 
group by t1.* /* type all column names required from t1*/
) final ;
    InformationsquelleAutor Sukhpreet