VIEW: APPS.PAY_US_LOCALITIES_V

Source

View Text - Preformatted

SELECT CT.CITY_NAME LOCALITY_NAME , ST.STATE_NAME STATE_NAME , CN.COUNTY_NAME COUNTY_NAME , 'CITY' LOCALITY_TYPE , CT.STATE_CODE ||'-'|| CT.COUNTY_CODE || '-'|| CT.CITY_CODE LOCALITY_GEOCODE , CT.STATE_CODE STATE_CODE FROM PAY_US_STATES ST, PAY_US_COUNTIES CN, PAY_US_CITY_NAMES CT WHERE CT.STATE_CODE = ST.STATE_CODE AND CT.STATE_CODE = CN.STATE_CODE AND CT.COUNTY_CODE = CN.COUNTY_CODE AND CT.PRIMARY_FLAG = 'Y' UNION ALL SELECT CN.COUNTY_NAME, ST.STATE_NAME, ' ', 'COUNTY', CN.STATE_CODE ||'-'|| CN.COUNTY_CODE ||'-0000', CN.STATE_CODE FROM PAY_US_STATES ST, PAY_US_COUNTIES CN WHERE ST.STATE_CODE = CN.STATE_CODE

View Text - HTML Formatted

SELECT CT.CITY_NAME LOCALITY_NAME
, ST.STATE_NAME STATE_NAME
, CN.COUNTY_NAME COUNTY_NAME
, 'CITY' LOCALITY_TYPE
, CT.STATE_CODE ||'-'|| CT.COUNTY_CODE || '-'|| CT.CITY_CODE LOCALITY_GEOCODE
, CT.STATE_CODE STATE_CODE
FROM PAY_US_STATES ST
, PAY_US_COUNTIES CN
, PAY_US_CITY_NAMES CT
WHERE CT.STATE_CODE = ST.STATE_CODE
AND CT.STATE_CODE = CN.STATE_CODE
AND CT.COUNTY_CODE = CN.COUNTY_CODE
AND CT.PRIMARY_FLAG = 'Y' UNION ALL SELECT CN.COUNTY_NAME
, ST.STATE_NAME
, ' '
, 'COUNTY'
, CN.STATE_CODE ||'-'|| CN.COUNTY_CODE ||'-0000'
, CN.STATE_CODE
FROM PAY_US_STATES ST
, PAY_US_COUNTIES CN
WHERE ST.STATE_CODE = CN.STATE_CODE