RS_ST_WRKR_GEO

(SQL View)
 Index Back

SELECT A.CANDIDATE_ID , A.CITY , A.STATE , A.POSTAL , B.LATITUDE + 360 , B.LONGITUDE + 360 FROM PS_FO_CANDIDATE_W A , PS_RS_GEOCODE_CITY B WHERE %Upper(A.CITY) = B.CITY AND A.STATE = B.STATE AND A.POSTAL = B.POSTAL UNION SELECT A.CANDIDATE_ID , A.CITY , A.STATE , A.POSTAL %Concat '-Error' , B.LATITUDE + 360 , B.LONGITUDE + 360 FROM PS_FO_CANDIDATE_W A , PS_RS_GEOCODE_CITY B WHERE %Upper(A.CITY) = B.CITY AND A.STATE = B.STATE AND B.LATITUDE = ( SELECT MIN(X.LATITUDE) FROM PS_RS_GEOCODE_CITY X WHERE X.CITY = %Upper(A.CITY) AND X.STATE = A.STATE) AND NOT EXISTS ( SELECT 'X' FROM PS_RS_GEOCODE_CITY C WHERE C.CITY = B.CITY AND C.STATE = B.STATE AND C.POSTAL = A.POSTAL) UNION SELECT A.CANDIDATE_ID , A.CITY %Concat '-Error' , A.STATE , A.POSTAL , B.LATITUDE + 360 , B.LONGITUDE + 360 FROM PS_FO_CANDIDATE_W A , PS_RS_GEOCODE_CITY B WHERE A.POSTAL = B.POSTAL AND A.STATE = B.STATE AND %Upper(A.CITY) <> B.CITY AND NOT EXISTS ( SELECT 'X' FROM PS_RS_GEOCODE_CITY C WHERE C.CITY = %Upper(A.CITY) AND C.STATE = A.STATE)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 CANDIDATE_ID Character(11) VARCHAR2(11) NOT NULL Used as a general ID for anything from Employee id to other types of ID like asset and material.
2 CITY Character(30) VARCHAR2(30) NOT NULL City

Prompt Table: RS_GEOCODE_CITY

3 STATE Character(6) VARCHAR2(6) NOT NULL State
4 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
5 LATITUDE Signed Number(12,7) DECIMAL(10,7) NOT NULL Latitude
6 LONGITUDE Signed Number(12,7) DECIMAL(10,7) NOT NULL Longitude