• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:June 12, 2020
  • Reading time:5 mins read
You are currently viewing SQL Query to get Geography Information
SQL Query to get Geography Information

In this article we will look into getting the Country, State, County, City and Zipcode information from backend tables. These queries will be handy when there is a requirement to get the Country Name, State Names etc apart from the Country Code and State Code which are present in most of the address tables.

Countries:
SELECT TERRITORY_CODE
,TERRITORY_SHORT_NAME
,CURRENCY_CODE
,DESCRIPTION
FROM FND_TERRITORIES_VL
WHERE OBSOLETE_FLAG = 'N'
ORDER BY TERRITORY_SHORT_NAME
,TERRITORY_CODE
States:
SELECT hzgiv.identifier_value
FROM hz_geographies hzg
,hz_geography_identifiers hzgi
,hz_geography_identifiers_vl hzgiv
WHERE hzg.country_code = 'US'
AND hzg.geography_use = 'MASTER_REF'
AND hzg.geography_type = 'STATE'
AND hzg.geography_element1 = 'United States'
AND hzg.geography_id = hzgi.geography_id
AND hzgi.identifier_subtype = 'GEO_CODE'
AND hzgi.identifier_type = 'CODE'
AND hzgi.primary_flag = 'N'
AND hzgi.geography_use = 'MASTER_REF'
AND hzgi.language_code = 'US'
AND hzgi.geography_id = hzgiv.geography_id
AND hzgiv.geography_use = 'MASTER_REF'
AND hzgiv.identifier_type = 'NAME'
AND hzgiv.identifier_subtype = 'STANDARD_NAME'
AND hzgiv.language_code = 'US'
AND hzgiv.primary_flag = 'Y'
Counties:
SELECT hzgiv.identifier_value
FROM hz_geographies hzg
	,hz_geography_identifiers hzgi
	,hz_geography_identifiers_vl hzgiv
WHERE hzg.country_code = 'US'
	AND hzg.geography_use = 'MASTER_REF'
	AND hzg.geography_type = 'COUNTY'
	AND hzg.geography_element1 = 'United States'
	AND hzg.geography_id = hzgi.geography_id
	AND hzgi.identifier_subtype = 'GEO_CODE'
	AND hzgi.identifier_type = 'CODE'
	AND hzgi.primary_flag = 'Y'
	AND hzgi.geography_use = 'MASTER_REF'
	AND hzgi.language_code = 'US'
	AND hzgi.geography_id = hzgiv.geography_id
	AND hzgiv.geography_use = 'MASTER_REF'
	AND hzgiv.identifier_type = 'NAME'
	AND hzgiv.identifier_subtype = 'STANDARD_NAME'
	AND hzgiv.language_code = 'US'
	AND hzgiv.primary_flag = 'Y'
	AND substr(hzgi.identifier_value, 1, 2) = 'STATE_VALUE'
Cities:
SELECT initcap(hzgiv.identifier_value)
FROM hz_geographies hzg
	,hz_geography_identifiers hzgi
	,hz_geography_identifiers_vl hzgiv
WHERE hzg.country_code = 'US'
	AND hzg.geography_use = 'MASTER_REF'
	AND hzg.geography_type = 'CITY'
	AND hzg.geography_element1 = 'United States'
	AND hzg.geography_id = hzgi.geography_id
	AND hzgi.identifier_subtype = 'GEO_CODE'
	AND hzgi.identifier_type = 'CODE'
	AND hzgi.primary_flag = 'Y'
	AND hzgi.geography_use = 'MASTER_REF'
	AND hzgi.language_code = 'US'
	AND hzgi.geography_id = hzgiv.geography_id
	AND hzgiv.identifier_type = 'NAME'
	AND hzgiv.identifier_subtype = 'STANDARD_NAME'
	AND hzgiv.language_code = 'US'
	AND hzgiv.primary_flag = 'Y'
	AND hzgiv.geography_use = 'MASTER_REF'
	AND substr(hzgi.identifier_value, 1, 2) = 'STATE_VALUE'
	AND substr(hzgi.identifier_value, 4, 3) = 'COUNTY_VALUE'

Zipcodes:
SELECT hzgpc.geography_name
FROM hz_geographies hzg
	,hz_geography_identifiers hzgi
	,hz_geography_identifiers_vl hzgiv
	,hz_geographies hzgpc
WHERE hzg.country_code = 'US'
	AND hzg.geography_use = 'MASTER_REF'
	AND hzg.geography_type = 'CITY'
	AND hzg.geography_element1 = 'United States'
	AND hzg.geography_id = hzgi.geography_id
	AND hzgi.identifier_subtype = 'GEO_CODE'
	AND hzgi.identifier_type = 'CODE'
	AND hzgi.primary_flag = 'Y'
	AND hzgi.geography_use = 'MASTER_REF'
	AND hzgi.language_code = 'US'
	AND hzgi.geography_id = hzgiv.geography_id
	AND hzgiv.identifier_type = 'NAME'
	AND hzgiv.identifier_subtype = 'STANDARD_NAME'
	AND hzgiv.language_code = 'US'
	AND hzgiv.primary_flag = 'Y'
	AND hzgiv.geography_use = 'MASTER_REF'
	AND hzgpc.country_code = 'US'
	AND hzgpc.geography_use = 'MASTER_REF'
	AND hzgpc.geography_type = 'POSTAL_CODE'
	AND hzgpc.geography_element1 = hzg.geography_element1
	AND hzgpc.geography_element2 = hzg.geography_element2
	AND hzgpc.geography_element3 = hzg.geography_element3
	AND hzgpc.geography_element4 = hzg.geography_element4
	AND hzgpc.geography_name IS NOT NULL
	AND substr(hzgi.identifier_value, 1, 2) = 'STATE_VALUE'
	AND substr(hzgi.identifier_value, 4, 3) = 'COUNTY_VALUE'
	AND substr(hzgi.identifier_value, 8, 4) = 'CITY_VALUE'

If you have any questions, please feel free to reach out to me by posting in comments section.

If you are interested in learning Fusion Technical tools go through this post

If you liked the article, please share it with your friends/ colleagues/ teammates or anyone who might also benefit from it.