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:[email protected]]
Sent: Tuesday, May 03, 2016 4:02 PM
To: [email protected]
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:[email protected]]
Sent: Tuesday, May 03, 2016 4:22 AM
To: [email protected]<mailto:[email protected]>
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
