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:msrmaill...@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" <g...@cornell.edu<mailto:g...@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;
