hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Garry Chen <g...@cornell.edu>
Subject RE: left join error
Date Tue, 06 Oct 2015 14:24:45 GMT
He fully qualify query should be as
select master_request_tab.date_key, master_request_tab.ref, '' from master_request_tab
 left join dm_search_eng  on substr(master_request_tab.ref, 1, length(dm_search_eng.engine))
= dm_search_eng.engine where dm_search_eng.engine is null;

and still showing error.   The length(dm_search_eng.engine) just return a number as part of
the substr parameter why it throw out error?

Garry

From: Msr Msr [mailto:msrmaillist@gmail.com]
Sent: Tuesday, October 06, 2015 9:54 AM
To: user@hive.apache.org
Subject: Re: left join error


Try using alias qualified columns fir common columns in bith tables...something like this
one
select date_key,ref, '' from master_request_tab a left join dm_search_eng b on substr(ref,
1,length(a.engine)) = b.engine where a.engineis null;
On Oct 6, 2015 9:19 AM, "Garry Chen" <gc92@cornell.edu<mailto:gc92@cornell.edu>>
wrote:
Hi All,
                Do you guys see any error in the following left join sql statement?  However,
 the hive give me error as “Error: Error while compiling statement: FAILED: SemanticException
[Error 10017]: Line 1:75 Both left and right aliases encountered in JOIN 'engine' (state=42000,code=10017)”.
 Please let me kow where I am doing wrong.  Thank you.

select date_key,ref, '' from master_request_tab left join dm_search_eng on substr(ref, 1,
length(engine)) = engine where engine is null;



Mime
View raw message