RE: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

[Markovitz, Dudu]

Forget about the BTW…
Apparently hive behaves like sqlite in that matter and not like other databases

hive> select 1 from table1 having 1=1;
FAILED: SemanticException HAVING specified without GROUP BY

From: Markovitz, Dudu [mailto:dmarkov...@paypal.com]
Sent: Tuesday, May 03, 2016 8:36 PM
To: user@hive.apache.org
Subject: RE: Unsupported SubQuery Expression '1': Only SubQuery expressions 
that are top level conjuncts are allowed

I left out the filter on column Col2 in order to simplify the test case.
The following query is logically equal to your original query.

BTW –
You don’t need the GROUP BY A.Col1 part in your original query

Dudu

create table Table1 (Col1 int,Col3 int);
create table Table2 (Col1 int,Col3 int);

insert into Table1 values (10,1),(20,2),(40,4),(60,7),(80,8);
insert into Table2 values (10,1),(30,2),(20,3),(50,4),(40,5),(40,6),(70,7);


select      *



from                    table1  a



            left join  (select      col1



                        from        table2



                        group by    col1



                        having      count(*) > 1

                        )

                        b2



          on            b2.col1  =

                        a.col1



            left join   table2  b



            on          a.col3  =

                        b.col3



                    and b2.col1 is null

;

10           1              NULL     10           1
20           2              NULL     30           2
40           4              40           NULL     NULL
60           7              NULL     70           7
80           8              NULL     NULL     NULL

From: mahender bigdata [mailto:mahender.bigd...@outlook.com]
Sent: Tuesday, May 03, 2016 4:02 PM
To: user@hive.apache.org<mailto:user@hive.apache.org>
Subject: Re: Unsupported SubQuery Expression '1': Only SubQuery expressions 
that are top level conjuncts are allowed


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<mailto: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