hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mahender bigdata <Mahender.BigD...@outlook.com>
Subject Re: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed
Date Tue, 10 May 2016 22:42:39 GMT
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 <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 Col2in 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(Col1int,Col3int)*;*
>
>     *create**table*Table2(Col1int,Col3int)*;*
>
>     *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*table1a
>
>     *left**join*  (*select*col1
>
>     *from*table2
>
>     *group**by*col1
>
>     *having*count(*) >1
>
>                             )
>
>     b2
>
>     *on*b2.col1=
>
>     a.col1
>
>     *left**join*table2b
>
>     *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 <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.BigData@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
>


Mime
View raw message