Re: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

[mahender bigdata]

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
*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 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.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