英文:
Hive Error: both left and right aliases encountered in join'late_fee_charged'
问题
这是我的查询，我需要根据两个条件连接两个表，但是我遇到了这个错误-Hive错误：在连接中遇到了左右别名'late_fee_charged'。此外，Late_fee_charged只存在于一个表中。
select z.*
from (
select a.*,b.*,
row_number() over (partition by a.account_number order by a.load_date) as rn
from base a
left join master b
on a.account_number=b.acct_number
and b.load_date>a.late_fee_charged) z
where z.rn=1
英文:
This is my query, I need to join two tables based on two conditions
however I am getting this error-Hive Error: both left and right aliases encountered in join'late_fee_charged'
Also, Late_fee_charged is present only in one table
select z.*
from (
select a.*,b.*,
row_number() over (partition by a.account_number order by a.load_date) as rn
from base a
left join master b
on a.account_number=b.acct_number
and b.load_date>a.late_fee_charged) z
where z.rn=1
答案1
得分: 0
Hive不允许非等值连接（b.load_date>a.late_fee_charged）与外连接结合使用。
在您的情况下，我建议尝试类似以下的查询：
select z.*
from (
select a.*,b.*,
case when b.load_date>a.late_fee_charged then 1 else 0 end as tst_col,
row_number() over (partition by a.account_number order by a.load_date) as rn
from base a
left join master b
on a.account_number=b.acct_number ) z
where z.rn=1
and tst_col = 1
这可能会影响您的行号，您需要使用您的数据进行测试。
英文:
Hive doesn't allow non-equi joins ( b.load_date>a.late_fee_charged) combined with outer joins.
In your case, I would try something like this:
select z.*
from (
select a.*,b.*,
case when b.load_date>a.late_fee_charged then 1 else 0 end as tst_col,
row_number() over (partition by a.account_number order by a.load_date) as rn
from base a
left join master b
on a.account_number=b.acct_number ) z
where z.rn=1
and tst_col = 1
This may mess up your row_number though, you'll have to test it with your data.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论