Hi, I have two tables in impala,in first table one of the column is mobile number and second table I have country_code and country_name. Table events Column1 Column2 .... CallingNumber CalledNumber....Columnn ----------------------------------------------------------------------------- Some Data ...... 917878999777 2347878999777 Table l_countrycode Country_Code Country_Name ------------------------------------------ 91 India 234 Nigeria 7 Kazakhastan ....More data So I have to join two tables on the basis of country code and country code can be of 1,2,3,and 4 digits.I have to extract country code from the mobile number write the following logic to join two tables select column1,colum2.... from events e,l_countrycode vcc2 where substring(e.callednumber,1,1)= vcc2.country_code in or substring(e.callednumber,1,2)=vcc2.country_code or substring(e.callednumber,1,3)=vcc2.country_code or substring(e.callednumber,1,4))=vcc2.country_code The preceding code does not run,so I try this select column1,colum2.... from events e,l_countrycode vcc2 where vcc2.country_code in(substring(e.callednumber,1,1),substring(e.callednumber,1,2),substring(e.callednumber,1,3), substring(e.callednumber,1,4)) Both query returns error NotImplementedException: Join between 'e' and 'vcc2' requires at least one conjunctive equality predicate between the two tables. The only option I left with is to use UNION but the table contains millions of data and it will hinder performance and affect memory. So please help me how can I achieve my target without UNION as the above query runs fine in MYSQL
... View more
