Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Plain View
Hive >> mail # user >> HIVE NOT EXISTS

Mohit Anchlia 2012-10-01, 20:42
Copy link to this message
Hi Mohit,

Hive doesn’t support correlated subqueries.  In this instance, you can do a
left outer join to find values that are not in a table.









FROM page_temp_ext a

    LEFT OUTER JOIN page_temp b ON

        a.pagename = b.pagename AND

        a.pagetitle = b.pagetitle AND

        a.page_id = b.pageid AND

        a.pagetype = b.pagetype


    b.pagename IS NULL
Hope that helps,

       On Mon, Oct 1, 2012 at 4:42 PM, Mohit Anchlia <[EMAIL PROTECTED]
> wrote:

> Could someone help me understand what alternative do I have for this
> query? I am trying to check if a given row exists in the table.
> select "",a.pagename,a.pagedetail,"",a.pagetitle,a.page_id,a.pagetype
>   from page_temp_ext a
> where 0 = (select count(*) from page_temp b where a.pagename = b.pagename
> and a.pagetitle = b.pagetitle and a.page_id = b.pageid and a.pagetype > b.pagetype);
Mohit Anchlia 2012-10-01, 22:59