Error while converting Presto SQL to Hive SQL containing subquery

May 10, 2023

I am trying to convert the following Presto SQL to Hive SQL. The sub query in WHERE clause isn’t working in Hive and is throwing an error.

select distinct id 
from account
where load_date = (select max(load_date) from account)
and account_status='current'
and account_type='crnt'

Please advise how I can fix this and make it work in Hive.

>Solution :

Hive doesn’t support subqueries in the WHERE clause. Try this

SELECT DISTINCT a.id 
FROM account a
JOIN (
  SELECT max(load_date) AS max_load_date 
  FROM account
) b
ON a.load_date = b.max_load_date
WHERE a.account_status='current'
AND a.account_type='crnt';

or this

WITH max_load_date AS (
  SELECT max(load_date) AS max_date 
  FROM account
)

    SELECT DISTINCT a.id 
    FROM account a
    JOIN max_load_date m
    ON a.load_date = m.max_date
    WHERE a.account_status='current'
    AND a.account_type='crnt';