SELECT UPPER(i.db_schema) ||'_'|| a.address_id ID, UPPER(i.db_schema) county, a.address_id AS address_id, (SELECT e.description FROM ejs_codes e WHERE UPPER (e.code_type) = UPPER (a.addr_sc_address_subtype_c_type) AND UPPER (e.code) = UPPER (a.addr_sc_address_subtype_code)) AS SUBTYPE, a.addr_sc_address_subtype_code AS subtypecode, (SELECT e.description FROM ejs_codes e WHERE UPPER (e.code_type) = UPPER (a.state_cd_state_code_type) AND UPPER (e.code) = UPPER (a.state_cd_state_code)) AS state, a.state_cd_state_code AS statecode, (SELECT e.description FROM ejs_codes e WHERE UPPER (e.code_type) = UPPER (a.dirct_cd_direction_code_type) AND UPPER (e.code) = UPPER (a.dirct_cd_direction_code)) AS direction_code, a.dirct_cd_direction_code AS direction_type_code, a.street_number AS street_number, a.street_name AS street_name, (SELECT e.description FROM ejs_codes e WHERE UPPER (e.code_type) = UPPER (a.street_cd_street_type_cod_type) AND UPPER (e.code) = UPPER (a.street_cd_street_type_code)) AS street_type_desc, a.street_cd_street_type_code AS street_type_code, a.zip4 AS zip4, a.zip5 AS zip5, CASE WHEN A.BEAT IS NOT NULL AND A.SUB_BEAT IS NOT NULL THEN (a.beat || '-' || a.sub_beat) ELSE '' END AS reporting_area, a.sub_number AS subnumber, (SELECT e.description FROM ejs_codes e WHERE UPPER (e.code_type) = UPPER (a.direct_suffix_type) AND UPPER (e.code) = UPPER (a.direct_suffix)) AS durect_suffix, a.direct_suffix AS direct_suffix_code, a.city AS city, a.indx_sec_level_id AS asecurity, a.indx_sec_level_code AS aseccode, a.indx_type_code AS aindxtype, a.intrsect1_dir AS intersection_direction, a.intrsect1_street_number AS intersection_streetnumber, a.intrsect1_name AS intersection_street_name, a.intrsect1_street_type_code AS interscetion_streettype, a.intrsect1_dir_suffix AS intesection_dir_suffix, a.distance1 AS distance, a.units1 AS units, TO_CHAR (a.updator_date, 'mm/dd/yyyy') AS date_of_info, e.ejs_comment AS ejscomment, a.creator_id AS createdby, a.creator_date AS creation_date, a.updator_id AS modifiedby, a.updator_date AS modification_date, CASE WHEN EJS_IS_NUMERIC(A.LONGITUDE) != 0 AND A.LONGITUDE BETWEEN -180 AND 180 THEN A.LONGITUDE ELSE '' END AS longitude, CASE WHEN EJS_IS_NUMERIC(A.LATITUDE) != 0 AND A.LATITUDE BETWEEN -90 AND 90 THEN A.LATITUDE ELSE '' END AS latitude, CASE WHEN EJS_IS_NUMERIC(A.LATITUDE) != 0 AND EJS_IS_NUMERIC(A.LONGITUDE) != 0 AND A.LATITUDE BETWEEN -90 AND 90 AND A.LONGITUDE BETWEEN -180 AND 180 THEN a.latitude || ',' || a.longitude ELSE '' END AS lat_long, ejs_common_names_pk.GET_COMMON_NAMES(a.address_id) AS common_place_name, TRIM (regexp_replace(a.STREET_NUMBER || ' ' || DC.DESCRIPTION || ' ' || a.STREET_NAME || ' ' || SC.DESCRIPTION || CASE WHEN a.INTRSECT1_NAME IS NOT NULL THEN ', ' || a.INTRSECT1_STREET_NUMBER || ' ' || DC2.DESCRIPTION || ' ' || a.INTRSECT1_NAME || ' ' || SC2.DESCRIPTION END || ' ' || DSC.DESCRIPTION || ' ' || STC.DESCRIPTION || CASE WHEN a.SUB_NUMBER IS NOT NULL THEN ' ' || a.SUB_NUMBER ELSE '' END || ' ' || a.city || ' ' || a.state_cd_state_code || ' ' || a.zip5 || CASE WHEN a.zip4 IS NOT NULL THEN '-' || a.zip4 ELSE '' END ,'[[:space:]]+', chr(32)) ) AS formatted, a.geocoded_flag AS geocoded_flag, CONV_SOLR_CREATOR_DATE(a.creator_date, (SELECT ec.code FROM ejs_codes ec WHERE ec.code_type = 'DATABASE_TIMEZONE' AND ROWNUM = 1)) AS SOLR_CREATION_DATE FROM install i, addresses a LEFT OUTER JOIN ejs_comments e ON a.rec_id = e.rec_id LEFT OUTER JOIN EJS_CODES DC ON a.DIRCT_CD_DIRECTION_CODE = DC.CODE AND a.DIRCT_CD_DIRECTION_CODE_TYPE = DC.CODE_TYPE LEFT OUTER JOIN EJS_CODES DSC ON a.DIRECT_SUFFIX = DSC.CODE AND a.DIRECT_SUFFIX_TYPE = DSC.CODE_TYPE LEFT OUTER JOIN EJS_CODES SC ON a.STREET_CD_STREET_TYPE_CODE = SC.CODE AND a.STREET_CD_STREET_TYPE_COD_TYPE = SC.CODE_TYPE LEFT OUTER JOIN EJS_CODES SC2 ON a.INTRSECT1_STREET_TYPE_CODE = SC2.CODE AND a.INTRSECT1_STREET_TYPE_COD_TYPE = SC2.CODE_TYPE LEFT OUTER JOIN EJS_CODES STC ON a.ADDR_SC_ADDRESS_SUBTYPE_CODE = STC.CODE AND a.ADDR_SC_ADDRESS_SUBTYPE_C_TYPE = STC.CODE_TYPE LEFT OUTER JOIN EJS_CODES DC2 ON a.INTRSECT1_DIR = DC2.CODE AND a.INTRSECT1_DIR_TYPE = DC2.CODE_TYPE