Hi Before dealing the issue itself, can you please fix the query? There are 3 aliased tables - Table1 (A), Table2 (B) & Table2 (mb) but you’re using additional 2 aliases – ma & adi1.
Thanks Dudu select A.Col1,A.Col2....B.Col3 From Table1 A LEFT OUTER JOIN Table2 B ON A.Col3= B.Col3 AND NOT EXISTS(SELECT 1 FROM Table2 mb WHERE ma.Col1= adi1.Col1 GROUP BY ma.Col1 HAVING COUNT(*)>1 ) AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL' ELSE 'NULL' END) = B.Col2) From: mahender bigdata [mailto:mahender.bigd...@outlook.com] Sent: Tuesday, May 03, 2016 4:22 AM To: user@hive.apache.org Subject: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed Hi, Is there a way to implement not exists in Hive. I'm using Hive 1.2. I'm getting below error "Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed" Query: select A.Col1,A.Col2....B.Col3 From Table1 A LEFT OUTER JOIN Table2 B ON A.Col3= B.Col3 AND NOT EXISTS(SELECT 1 FROM Table2 mb WHERE ma.Col1= adi1.Col1 GROUP BY ma.Col1 HAVING COUNT(*)>1 ) AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL' ELSE 'NULL' END) = B.Col2) I Would like to have OR Condition in LEFT Join hive statement. or alternative way by splitting. thanks