hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Markovitz, Dudu" <dmarkov...@paypal.com>
Subject RE: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed
Date Tue, 03 May 2016 05:52:55 GMT
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.BigData@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




Mime
View raw message