What's new

HIVE Correlated subquery

Fever

Active member
Staff member
I am trying to run below query in HIVE and getting error 'FAILED: SemanticException IN encountered with 0 children. Same query is running in 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 />


Also, if I move the subquery to where clause it gives the error "Line 1:4052 Invalid table alias or column reference '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 )`

Continue reading...
 
Top