Updated..
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 B WHERE B.Col1= A.Col1 GROUP BY
A.Col1 HAVING COUNT(*)>1 )
AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL'
ELSE 'NULL' END) = B.Col2)
On 5/2/2016 10:52 PM, Markovitz, Dudu wrote:
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 B WHERE B.Col1= A.Col1 GROUP BY
A.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
