You’re welcome Dudu
From: mahender bigdata [mailto:mahender.bigd...@outlook.com] Sent: Wednesday, May 11, 2016 1:43 AM To: user@hive.apache.org Subject: Re: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed 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.bigd...@outlook.com] Sent: Wednesday, May 04, 2016 7:39 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 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:dmarkov...@paypal.com] Sent: Tuesday, May 03, 2016 8:36 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 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
