RS_ST_GEO_CITY

(SQL View)

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 UNION 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 B.POSTAL = ' ' 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)

# 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

#	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