hive相关的子查询failed: semanticexception in encountered with 0 children

ca1c2owp 于 2021-04-09 发布在 Hive

关注(0)|答案(1)|浏览(3822)

我试图在hive中运行下面的查询，得到的错误是 "failed: semanticexception in encountered with 0 children”.同样的查询在mysql中正常运行。

`Select ord.orno,
inv.pono,
inv.srnb,
ord.cur,
inv.invd,
cureff.rate
FROM  order ord 
JOIN invoice inv
ON(inv.orno = ord.orno)
-- Currency data
LEFT JOIN currrates cureff
ON (
cureff.cur = 'USD'
AND  cureff.stdt in (Select MAX(stdt) from currrates where stdt <= inv.invd AND cur = ord.cur )
)`<br />

另外，如果我把子查询移到where子句，它给出的错误是 "行1:4052无效表别名或列引用'inv'"。

`Select ord.orno,
inv.pono,
inv.srnb,
ord.cur,
inv.invd,
cureff.rate
FROM  order ord 
JOIN invoice inv
ON(inv.orno = ord.orno)
-- Currency data
LEFT JOIN currrates cureff
ON (
cureff.cur = 'USD' 
)
WHERE cureff.stdt in (Select MAX(stdt) from currrates where stdt <= inv.invd AND cur = ord.cur )`

sql Hive

来源：https://stackoverflow.com/questions/62975676/hive-correlated-subquery

1条答案

按热度按时间

wfveoks01#

如果我的理解是正确的，你想从货币汇率表中得到最新的美元行。为此，你应该使用窗口函数。

select . . .
from order o join
     invoice i
     on i.orno = o.orno join
     (select cr.*,
             row_number() over (partition by cr.cur order by cr.stdt desc) as seqnum
      from currrates dr
      where cr.cur = 'USD'
    ) cr
    where cr.cur = o.cur and seqnum = 1;

赞(0）回复(0）举报 2021-04-10

我来回答

hive相关的子查询failed: semanticexception in encountered with 0 children

1条答案

相关问题

热门标签

最新问答