Error while running hive join query

1
votes
SELECT A.* , B.* FROM
(SELECT ID,DATE FROM APPLE) A
INNER JOIN 
(SELECT ID,MAX(DATE) AS MAXDATE FROM APPLE GROUP BY ID) A1
ON A.ID = A.ID AND A.DATE = A1.MAXDATE
WHERE A.DATE > CURRENT_DATE
LEFT OUTER JOIN (
SELECT ID,NAME FROM BANANA) B
ON A.ID = B.ID 
WHERE B.NAME IN ('USA','GBR') LIMIT 10;

Error: Error while compiling statement: FAILED: ParseException line 22:0 missing EOF at 'LEFT' near 'CURRENT_DATE' (state=42000,code=40000)

2
joinhive
I am not sure whether this is the right syntax for Hive, but I am quite sure the SELECT A.*, B.* FROM ... must be with star and without just dot? - Andrej Zile
I added A.*,B.* only, for some reason it didn't display. Corrected it now. - Bichu
It's a guess, but I think it fails because you have where statement in the middle, which is meant to be only at the end. You are not using subquery to filter on this statement. - Andrej Zile
that where clause is needed for that inner join part, if i plug that where condition after the ON(left outer join), that will not solve my purpose. - Bichu

2 Answers

0
votes

Your problem is that you have a WHERE clause in the middle of your SQL statement. you can either move it into the nested query for A, or add it to the WHERE clause at the end. You also probably want to move the filtering on the B table inside the nested query, because you are essentially making the left join into an inner join by putting it in a WHERE clause at the end of the statement.

either

    SELECT A.* , B.* FROM
   (SELECT ID,DATE FROM APPLE WHERE DATE > CURRENT_DATE) A
   INNER JOIN 
   (SELECT ID,MAX(DATE) AS MAXDATE FROM APPLE GROUP BY ID) A1
   ON A.ID = A.ID AND A.DATE = A1.MAXDATE
   LEFT OUTER JOIN (
   SELECT ID,NAME FROM BANANA WHERE NAME IN ('USA','GBR') ) B
   ON A.ID = B.ID 
   LIMIT 10;

or

    SELECT A.* , B.* FROM
    (SELECT ID,DATE FROM APPLE) A
    INNER JOIN 
    (SELECT ID,MAX(DATE) AS MAXDATE FROM APPLE GROUP BY ID) A1
    ON A.ID = A.ID AND A.DATE = A1.MAXDATE
    LEFT OUTER JOIN (
    SELECT ID,NAME FROM BANANA WHERE NAME IN ('USA','GBR') ) B
    ON A.ID = B.ID 
    WHERE A.DATE > CURRENT_DATE
    LIMIT 10;
0
votes

The WHERE clause i.e. A.DATE > CURRENT_DATE should be inside the first select.Also note that you have a condition A.ID = A.ID and instead of A.ID = A1.ID

SELECT 
     A.* , B.* 
FROM 
    (SELECT ID,DATE FROM APPLE WHERE DATE > CURRENTDATE) A
INNER JOIN  
    (SELECT ID,MAX(DATE) AS MAXDATE FROM APPLE GROUP BY ID) A1
ON 
     A.ID = A1.ID AND A1.DATE = A1.MAXDATE 
LEFT OUTER JOIN 
     (SELECT ID,NAME FROM BANANA) B
ON 
     A.ID = B.ID 
WHERE B.NAME IN ('USA','GBR') LIMIT 10;