Thanks Dudu, I made modification as per our requirement. ur query helped me to modify as per our requirement.


On 5/4/2016 10:57 AM, Markovitz, Dudu wrote:

Hi

 

The syntax is not Hive specific but SQL ANSI/ISO.

In a series of “JOIN … ON …” any “ON” can (but not necessarily have to) refer any of its preceding tables, e.g. –

 

select … from t1 join t2 on … *1 … join t3 on … *2 … join t4 on … *3

*1      The 1st “ON” can refer tables t1 & t2

*2      The 2nd “ON” can refer tables t1, t2 & t3

*3      The 3rd “ON” can refer tables t1, t2, t3 & t4

 

In our query the “… group by> 1” combined with “b2.col1 is null” implements the functionality of the “not exists” from the original query.

The rest of the query stays quite the same.

 

Dudu

 

From: mahender bigdata [mailto:Mahender.BigData@outlook.com]
Sent: Wednesday, May 04, 2016 7:39 PM
To: user@hive.apache.org
Subject: Re: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

 

Thanks Dudu,

Can you help me in parsing below logic, I see First you are starting join of table1 with result set of Group by > 1 and perform left join with table2, how can we get reference a. alias of joined result or will hive pickup "a" column from table 1 and 3 column in table2.

 

thanks in advance

 

 

On 5/3/2016 11:24 AM, Markovitz, Dudu wrote:

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:dmarkovitz@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.BigData@outlook.com]
Sent: Tuesday, May 03, 2016 4:02 PM
To: 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.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