Referring to a column alias in two tables

Question

I have two hive SQL tables which consist of following columns.

table_1

|customer_id | ip_address|

region_table

|country_name | region_name|

I tried,

SELECT table_1.customer_id, table_1.ip_address, getCountry(ip_address) AS Country, region_table.region_name FROM table_1 JOIN region_table ON region_table.country_name = Country;

getCountry() is UDF which returns the country name when the IP address is passed into it. I want to use that country name to create another column with the corresponding region from the region_table. And i want to get the following table as my output.

customer_id | ip_address | Country | region_name

Any thoughts on what I'm missing in my query?

Answer 1

select      c.customer_id
           ,c.ip_address
           ,getCountry(c.ip_address) as Country
           ,r.region_name 

from                table_1         c

            join    region_table    r

            on      r.country_name = 
                    getCountry(c.ip_address)

Answer 2

In case of Oracle, you can not refer column alias defined in SELECT statement in WHERE clause of same query!! Because database engine first evaluates WHERE clause and identifies eligible rows and then proceeds to fetch columns as defined in SELECT part of query.

In your case, right query should be

select 
  table_1.customer_id, 
  table_1.ip_address, 
  getCountry(ip_address) AS Country, 
  region_table.region_name 
FROM table_1 
JOIN region_table ON region_table.country_name = getCountry(table_1 .ip_address);