hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mahender bigdata <Mahender.BigD...@outlook.com>
Subject Re: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed
Date Tue, 03 May 2016 13:02:05 GMT
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.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