RS_ED_WKR_LOC

(SQL View)
 Index Back

ETL Worker Geo Codes

Worker addresses are joined to Geocode tables as follows: 1) City, State, and Postal Code from PERSONAL_DATA matches exactly with RS_GEOCODE_CITY record. 2) City and State from PERSONAL_DATA matches with RS_GEOCODE_CITY where Postal Code does not match. This assumes that Postal Code is more likely than City to be incorrect in the PERSONAL_DATA record. 3) Postal Code and State from PERSONAL_DATA matches with RS_GEOCODE_CITY record where City does not match. This catches errors where the city is spelled differently or incorrectly in the two tables. For example: St. Louis vs Saint Louis.

SELECT A.EMPLID , A.CITY , A.STATE , A.COUNTRY , A.POSTAL , %NumToChar(B.LATITUDE) %Concat ' ' %Concat %NumToChar(B.LONGITUDE) FROM (%Sql(RS_ED_WKR_ADDR_SEL)) A , PS_GEOCODE_TBL B WHERE %Upper(A.CITY) = B.CITY AND A.STATE = B.STATE AND A.COUNTRY = B.COUNTRY AND A.POSTAL = B.POSTAL UNION SELECT A.EMPLID , A.CITY , A.STATE , A.COUNTRY , A.POSTAL %Concat ' -Error' , %NumToChar(B.LATITUDE) %Concat ' ' %Concat %NumToChar(B.LONGITUDE) FROM (%Sql(RS_ED_WKR_ADDR_SEL)) A , PS_GEOCODE_TBL B WHERE %Upper(A.CITY) = B.CITY AND A.STATE = B.STATE AND A.COUNTRY = B.COUNTRY AND B.LATITUDE = ( SELECT MIN(X.LATITUDE) FROM PS_GEOCODE_TBL X WHERE X.CITY = B.CITY AND X.STATE = B.STATE AND X.COUNTRY = B.COUNTRY) AND NOT EXISTS ( SELECT 'X' FROM PS_GEOCODE_TBL C WHERE C.CITY = B.CITY AND C.STATE = B.STATE AND C.COUNTRY = B.COUNTRY AND C.POSTAL = A.POSTAL) UNION SELECT A.EMPLID , A.CITY %Concat ' -Error' , A.STATE , A.COUNTRY , A.POSTAL , %NumToChar(B.LATITUDE) %Concat ' ' %Concat %NumToChar(B.LONGITUDE) FROM (%Sql(RS_ED_WKR_ADDR_SEL)) A , PS_GEOCODE_TBL B WHERE A.POSTAL = B.POSTAL AND A.STATE = B.STATE AND A.COUNTRY = B.COUNTRY AND %Upper(A.CITY) <> B.CITY AND NOT EXISTS ( SELECT 'X' FROM PS_GEOCODE_TBL C WHERE C.CITY = %Upper(A.CITY) AND C.STATE = A.STATE AND C.COUNTRY = A.COUNTRY) UNION SELECT A.EMPLID , A.CITY , A.STATE %Concat ' -Error' , A.COUNTRY , A.POSTAL , %NumToChar(B.LATITUDE) %Concat ' ' %Concat %NumToChar(B.LONGITUDE) FROM (%Sql(RS_ED_WKR_ADDR_SEL)) A , PS_GEOCODE_TBL B WHERE %Upper(A.CITY) = B.CITY AND A.STATE <> B.STATE AND A.COUNTRY = B.COUNTRY AND B.LATITUDE = ( SELECT MIN(X.LATITUDE) FROM PS_GEOCODE_TBL X WHERE X.CITY = B.CITY AND X.COUNTRY = B.COUNTRY) AND NOT EXISTS ( SELECT 'X' FROM PS_GEOCODE_TBL C WHERE C.CITY = B.CITY AND C.STATE = A.STATE AND C.COUNTRY = B.COUNTRY)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 CITY Character(30) VARCHAR2(30) NOT NULL City

Prompt Table: RS_GEOCODE_CITY

3 STATE Character(6) VARCHAR2(6) NOT NULL State
4 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
5 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
6 GEOCODE Character(30) VARCHAR2(30) NOT NULL The geocode of an address, created by concatenating the address latitude, blank space, longitude.