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

On 5/4/2016 10:57 AM=2C Markovitz=2C Dud= u wrote:

Hi

=C2=A0

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

In a series of =E2=80=9CJOIN =E2=80=A6 ON =E2=80=A6=E2=80=9D any = =E2=80=9CON=E2=80=9D can (but not necessarily have to) refer any of its preceding tables=2C e.g. =E2=80=93

=C2=A0

select =E2=80=A6 from t1 join t2 o= n =E2=80=A6 *1 =E2=80=A6 join t3 on =E2=80=A6 *2 =E2= =80=A6 join t4 on =E2=80=A6 *3 =E2=80=A6

*1=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 The 1st =E2=80=9CON=E2=80=9D can r= efer tables t1 &=3B t2

*2=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 The 2nd =E2=80=9CON=E2=80=9D can r= efer tables t1=2C t2 &=3B t3

*3=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 The 3rd =E2=80=9CON=E2=80=9D can r= efer tables t1=2C t2=2C t3 &=3B t4

=C2=A0

In our query the =E2=80=9C=E2=80=A6 group by =E2=80=A6 >=3B 1=E2=80=9D combined with =E2=80=9Cb2.col1 is null=E2=80=9D implements the functionality of the =E2=80=9Cnot exists=E2=80= =9D from the original query.

The rest of the query stays quite the same.

=C2=A0

Dudu

=C2=A0

From: mahender bigdata [mailto:Mahender.BigData@outlook.co= m]
Sent: Wednesday=2C May 04=2C 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

=C2=A0

Thanks Dudu=2C

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

=C2=A0

=C2=A0

=C2=A0

On 5/3/2016 11:24 AM=2C Markovitz=2C Dudu wrote:

Apparently hive behaves like sqlite in that matter and not like other databases

=C2=A0

hive>=3B select 1 from table1 having 1=3D1=3B

FAILED: SemanticException HAVING specified without GROUP BY

=C2=A0

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

=C2=A0

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.

=C2=A0=

BTW =E2=80=93

You don=E2=80=99t need the GROUP BY A.Col1 part in your original query

=C2=A0

Dudu

=C2=A0=

create table Table1 (Col1 int=2CCol3 int)=3B

create table Table2 (Col1 int=2CCol3 int)=3B

=C2=A0

insert into Table1 values (10=2C1)=2C(20=2C2)=2C(40=2C4)=2C(60=2C7)=2C(80=2C8)=3B

insert into Table2 values (10=2C1)=2C(30=2C2)=2C(20=2C3)=2C(50=2C4)=2C(40=2C5)=2C(40=2C6)=2C(70=2C7)=3B

=C2=A0

`select=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 *`
`=C2=A0`
```from=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0table1=C2=A0 a```
`=C2=A0`
```=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 l=
eft join=C2=A0 (select=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0 col1```
```=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ```
```=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0from=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0 table2=C2=A0 ```
```=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
```
```=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0group by=C2=A0=C2=A0=C2=A0 =
col1 ```
```=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
```
```=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0having=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0 count(*)=
>=3B 1=
```
```=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0)```
```=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 b2```
```=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ```
```=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0on=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0 b2.col1=C2=A0 =3D=
```
```=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 a.col1=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ```
```=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0```
```=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0left join=C2=A0=C2=A0 table2=C2=A0 =
b```
`=C2=A0`
```=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 o=
n=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0 a.col3=C2=A0 =3D ```
```=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
b.co=
l3```
```=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ```
```=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0and b2.col1 is null```
```=3B=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ```

10=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 1=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 10=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 1

20=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 2=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 30=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 2

40=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 4=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 40=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL

60=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 7=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 70=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 7

80=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 8=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2= =A0 NULL

=C2=A0

From: mahender bigdata [mailto:Mah= ender.BigData@outlook.com]
Sent: Tuesday=2C May 03=2C 2016 4:02 PM
To: user@hive.apache.o= rg
Subject: Re: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

=C2=A0

Updated..

select A<= /span>.Col1=2CA.Col2.= ...B.Col3<= o:p>

From = Table1 A

LEFT OUTER JOIN Table2 B
=C2=A0=C2=A0=C2=A0 ON=C2=A0 A.Col3=3D B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 B WHERE B.Col1= =3D A.C= ol1 GROUP BY A.Col1 HAVING COUNT(*)>=3B1 )
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0 AND (CASE WHEN ISNU= LL(A.Col2= =2C'\=3B')=C2=A0 =3D '\=3B' THEN 'NOT-NULL' ELSE 'NULL' END) =3D B.C= ol2)

On 5/2/2016 10:52 PM=2C Markovitz=2C Dud= u wrote:

Hi

=C2=A0

Before dealing the issue itself=2C can you please fix the query?

There are 3 aliased tables - Table1 (A)=2C Table2 (B) =C2=A0&= =3B Table2 (mb) but you=E2=80=99re using additional 2 aliases = =E2=80=93 ma &=3B adi1.

=C2=A0

Thanks

=C2=A0

Dudu

select = A.Col1=2CA.Col= 2....B.Col= 3

From Table1 A

LEFT OUTER JOIN Table2 B=
=C2=A0=C2=A0=C2=A0 ON=C2=A0 A.Col3=3D B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 B WHERE B.Col= 1=3D A.Col1 GROUP BY A.Col1 HAVING COUNT(*)>=3B1 )
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0 AND (CASE WHEN IS= NULL(A.Col= 2=2C'\=3B')=C2=A0 =3D '\=3B' THEN 'NOT-NULL' ELSE 'NULL' END) =3D B= .Col2)

=C2=A0

=C2=A0

=C2=A0

=C2=A0

From: mahender bigdata [mailto:M= ahender.BigData@outlook.com]
Sent: Tuesday=2C May 03=2C 2016 4:22 AM
To: user@hive.apache= .org
Subject: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

=C2=A0

Hi=2C

Is there a way to implement=C2=A0 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:

=C2=A0

select A.Col1=2CA.Col2....B.Col3

From Table1 A

LEFT OUTER JOIN Table2 B
=C2=A0=C2=A0=C2=A0 ON=C2=A0 A.Col3=3D B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 mb WHERE ma.Col1=3D adi1.Col1 GROUP BY ma.Col1 HAVING COUNT(*)>=3B1 )
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0 AND (CASE WHEN IS= NULL(A.Col2=2C'\=3B')=C2=A0 =3D '\=3B' THEN 'NOT-NULL' ELSE 'NULL' END) =3D B.Col2)

=C2=A0

I Would like to have OR Condition in LEFT Join hive statement. or alternative way by splitting.

=C2=A0

thanks

=C2=A0

=C2=A0

=C2=A0

=C2=A0

--------------71DED89D1BD60CC3E1CDC28C--