|
SELECT A.BUSINESS_UNIT , A.SO_ID , A.SO_LINE , A.CITY , A.STATE , A.COUNTRY , A.POSTAL , %NumToChar(B.LATITUDE) %Concat ' ' %Concat %NumToChar(B.LONGITUDE) FROM (%Sql(RS_ED_SO_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.BUSINESS_UNIT , A.SO_ID , A.SO_LINE , A.CITY , A.STATE , A.COUNTRY , A.POSTAL %Concat ' -Error' , %NumToChar(B.LATITUDE) %Concat ' ' %Concat %NumToChar(B.LONGITUDE) FROM (%Sql(RS_ED_SO_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.BUSINESS_UNIT , A.SO_ID , A.SO_LINE , A.CITY %Concat ' -Error' , A.STATE , A.COUNTRY , A.POSTAL , %NumToChar(B.LATITUDE) %Concat ' ' %Concat %NumToChar(B.LONGITUDE) FROM (%Sql(RS_ED_SO_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.BUSINESS_UNIT , A.SO_ID , A.SO_LINE , A.CITY , A.STATE %Concat ' -Error' , A.COUNTRY , A.POSTAL , %NumToChar(B.LATITUDE) %Concat ' ' %Concat %NumToChar(B.LONGITUDE) FROM (%Sql(RS_ED_SO_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)
|