#include <StringConstants.au3> ; to declare the Constants of StringRegExp
#include <Array.au3> ; UDF needed for _ArrayDisplay and _ArrayConcatenate
Local $sRegex = "(?m)(LEFT|RIGHT|CROSS)\s{0,}(INNER|OUTER){0,}\s{0,}JOIN.{0,}$|FROM.{0,}$"
Local $sString = "--------------------------------------------------------" & @CRLF & _
"-- INSERT PLACEHOLDER RECORDS (fill in query later)" & @CRLF & _
"--------------------------------------------------------" & @CRLF & _
"" & @CRLF & _
"INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES ('OPERATING STATION', 'ITGIS', 'COMP_OP_STN', 'OBJECTID', 'SHAPE', 'POINT', 'OK ', 'Y');" & @CRLF & _
"INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES ('CROSSING', 'ENGXING', 'CI_LOCATIONANDCLASSIFICATION', 'ASSET_PK', 'SHAPE', 'POINT', 'OK ', 'Y');" & @CRLF & _
"INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES ('MILEPOST', 'EGIS', 'ENG_MPMARK', 'OBJECTID', 'SHAPE', 'POINT', 'OK ', 'Y');" & @CRLF & _
"INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES ('SWITCH', 'EGIS', 'ENG_SWITCH', 'OBJECTID', 'SHAPE', 'POINT', 'OK ', 'Y');" & @CRLF & _
"INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES ('TOWER', 'EGIS', 'ENG_TOWER', 'OBJECTID', 'SHAPE', 'POINT', 'OK ', 'Y');" & @CRLF & _
"INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES ('AEI SCANNER', 'ITGIS', 'COMP_AEI_SCNR', 'OBJECTID', 'SHAPE', 'POINT', 'OK ', 'Y');" & @CRLF & _
"INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES ('NSPD LOCATION', 'ITGIS', 'NSPD_LOCATIONS', 'OBJECTID', 'SHAPE', 'POINT', 'OK ', 'Y');" & @CRLF & _
"INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES ('NSPD POI', 'NSPD', 'NSPD_POI', 'ID', 'SHAPE', 'POINT', 'OK ', 'Y');" & @CRLF & _
"INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES ('MILEPOST 100S', 'ITGIS', 'ITGIS_MP100S', 'OBJECTID', 'SHAPE', 'POINT', 'OK ', 'Y');" & @CRLF & _
"" & @CRLF & _
"--------------------------------------------------------" & @CRLF & _
"-- UPDATE ACTUAL QUERIES" & @CRLF & _
"--------------------------------------------------------" & @CRLF & _
"" & @CRLF & _
"/* ITGIS.COMP_OP_STN */" & @CRLF & _
"UPDATE ITGIS.GLOBALSEARCHMASTER" & @CRLF & _
"SET QUERYTEXT =" & @CRLF & _
" q'[" & @CRLF & _
"WITH nsdMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT cos.OBJECTID," & @CRLF & _
"nsd.DIVCODE," & @CRLF & _
"nsd.DIVDESCR," & @CRLF & _
"nsd.DIVABBR," & @CRLF & _
"nsd.DIVNAME" & @CRLF & _
"FROM ITGIS.COMP_OP_STN cos" & @CRLF & _
"CROSS JOIN ITGIS.NS_DIVISIONS nsd" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( cos.SHAPE, nsd.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"nrzMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT cos.OBJECTID," & @CRLF & _
"nrz.BEAT_ID," & @CRLF & _
"nrz.AREA_ID," & @CRLF & _
"nrz.FIELD_OFF," & @CRLF & _
"nrz.COMMON_NAME NSPD_RESP_ZONE" & @CRLF & _
"FROM ITGIS.COMP_OP_STN cos" & @CRLF & _
"CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( cos.SHAPE, nrz.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"countyMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT cos.OBJECTID," & @CRLF & _
"usc.STATE," & @CRLF & _
"usc.COUNTY" & @CRLF & _
"FROM ITGIS.COMP_OP_STN cos" & @CRLF & _
"CROSS JOIN PUBDATA.US_COUNTIES usc" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( usc.SHAPE, cos.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"cityBoundaryMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT cos.OBJECTID," & @CRLF & _
"uscb.NAME CITY" & @CRLF & _
"FROM ITGIS.COMP_OP_STN cos" & @CRLF & _
"CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( uscb.SHAPE, cos.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")" & @CRLF & _
"SELECT" & @CRLF & _
"***MASTERID***, --MASTERID" & @CRLF & _
"cos.OBJECTID, --XUID" & @CRLF & _
"UPPER(cos.TCNTRL_STN_NAME), --SEARCHTEXT" & @CRLF & _
"UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(cos.PREFIX), TRIM(cos.SUFFIX), cos.MP, nfsac.DISTRICT, nfsac.BRANCH)), --DISPLAYTEXT" & @CRLF & _
"SDO_CS.MAKE_2D ( cos.SHAPE, 4326 ), --POINT/LINE/POLYGON" & @CRLF & _
"ROUND ( cos.SHAPE.SDO_POINT.y, 6 ), --LATITUDE" & @CRLF & _
"ROUND ( cos.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE" & @CRLF & _
"nrz.BEAT_ID, --BEAT" & @CRLF & _
"UPPER(cos.POSTAL_CD), --ZIPCODE" & @CRLF & _
"UPPER(COALESCE(cityBoundaryMatches.CITY,c.NAME)), --CITY" & @CRLF & _
"UPPER(countyMatches.COUNTY), --COUNTY" & @CRLF & _
"UPPER(COALESCE(CAST(cos.ST_ABBR AS NVARCHAR2(2)),CAST(countyMatches.STATE AS NVARCHAR2(2)))), --STATE" & @CRLF & _
"UPPER(COALESCE(nd.DIVNAME,nsd.DIVNAME)), --DIVISION" & @CRLF & _
"UPPER(nrz.FIELD_OFF), --FIELD_OFFICE" & @CRLF & _
"nrz.AREA_ID, --AREA" & @CRLF & _
"NULL, --JURISDICTION" & @CRLF & _
"NULL, --MUNICIPALITY" & @CRLF & _
"NULL, --LOCATION_ID" & @CRLF & _
"UPPER(ITGIS.MAKE_NSPD_MILEPOST_SHORT(TRIM(cos.PREFIX),TRIM(cos.SUFFIX),cos.MP)), --MILEPOST" & @CRLF & _
"UPPER(TRIM(cos.PREFIX)), --MP_PREFIX" & @CRLF & _
"cos.MP, --MP_NUMBER" & @CRLF & _
"UPPER(TRIM(cos.SUFFIX)), --MP_SUFFIX" & @CRLF & _
"UPPER(cos.TCNTRL_STN_NAME), --NAME" & @CRLF & _
"NULL, --CROSSING_DOT_NUM" & @CRLF & _
"NULL, --CROSSING_FULL_NAME" & @CRLF & _
"NULL --TRACK_SEGMENT_RANGE" & @CRLF & _
"FROM ITGIS.COMP_OP_STN cos" & @CRLF & _
"LEFT JOIN nsdMatches nsd" & @CRLF & _
"ON cos.OBJECTID = nsd.OBJECTID" & @CRLF & _
"LEFT JOIN nrzMatches nrz" & @CRLF & _
"ON cos.OBJECTID = nrz.OBJECTID" & @CRLF & _
"LEFT JOIN cityBoundaryMatches" & @CRLF & _
"ON cos.OBJECTID = cityBoundaryMatches.OBJECTID" & @CRLF & _
"LEFT JOIN countyMatches" & @CRLF & _
"ON cos.OBJECTID = countyMatches.OBJECTID" & @CRLF & _
"LEFT JOIN ITGIS.NSPD_LOCATIONS nl" & @CRLF & _
"ON nrz.BEAT_ID = nl.BEAT" & @CRLF & _
"LEFT JOIN ITGIS.NS_DIVISIONS nd" & @CRLF & _
"ON cos.DIVCODE = nd.DIVCODE" & @CRLF & _
"LEFT JOIN NFSAPP.CONTROL nfsac" & @CRLF & _
"ON(ITGIS.MAKE_LINEID(cos.DIVCODE, cos.PREFIX, cos.SUFFIX) = nfsac.LINE_ID AND cos.MP BETWEEN nfsac.LMP AND nfsac.HMP)" & @CRLF & _
"CROSS JOIN PUBDATA.US_CITIES c" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_NN ( c.SHAPE, cos.SHAPE, 'sdo_num_res = 1', 1 ) = 'TRUE'" & @CRLF & _
"--AND ROWNUM = 1" & @CRLF & _
"AND 1 = 1" & @CRLF & _
"]'" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
" AND XSCHEMA = 'ITGIS'" & @CRLF & _
" AND XTABLE = 'COMP_OP_STN'" & @CRLF & _
" AND 1 = 1;" & @CRLF & _
"" & @CRLF & _
"/* ENGXING.DOTLIST */" & @CRLF & _
"UPDATE ITGIS.GLOBALSEARCHMASTER" & @CRLF & _
"SET QUERYTEXT = TO_CLOB(q'[" & @CRLF & _
"WITH nsdMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT cils.CROSSINGID," & @CRLF & _
"nsd.DIVCODE," & @CRLF & _
"nsd.DIVDESCR," & @CRLF & _
"nsd.DIVABBR," & @CRLF & _
"nsd.DIVNAME" & @CRLF & _
"FROM ENGXING.CI_LOCATIONANDCLASSIFICATION cils" & @CRLF & _
"CROSS JOIN ITGIS.NS_DIVISIONS nsd" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE(cils.SHAPE, nsd.SHAPE, 'mask = anyinteract') = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"nrzMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT cils.CROSSINGID," & @CRLF & _
"nrz.BEAT_ID," & @CRLF & _
"nrz.AREA_ID," & @CRLF & _
"nrz.FIELD_OFF," & @CRLF & _
"nrz.COMMON_NAME NSPD_RESP_ZONE" & @CRLF & _
"FROM ENGXING.CI_LOCATIONANDCLASSIFICATION cils" & @CRLF & _
"CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE(cils.SHAPE, nrz.SHAPE, 'mask = anyinteract') = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"countyMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT cils.CROSSINGID," & @CRLF & _
"usc.STATE," & @CRLF & _
"usc.COUNTY" & @CRLF & _
"FROM ENGXING.CI_LOCATIONANDCLASSIFICATION cils" & @CRLF & _
"CROSS JOIN PUBDATA.US_COUNTIES usc" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE(usc.SHAPE, cils.SHAPE, 'mask = anyinteract') = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"cityBoundaryMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT cils.CROSSINGID," & @CRLF & _
"uscb.NAME CITY" & @CRLF & _
"FROM ENGXING.CI_LOCATIONANDCLASSIFICATION cils" & @CRLF & _
"CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE(uscb.SHAPE, cils.SHAPE, 'mask = anyinteract') = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")" & @CRLF & _
"SELECT" & @CRLF & _
"***MASTERID***, --MASTERID" & @CRLF & _
"cils.ASSET_PK," & @CRLF & _
"CASE WHEN q.INVDATE IS NOT NULL THEN UPPER(ITGIS.MAKE_NSPD_MILEPOST_SHORT(cior.PRFXMILEPOST,TRIM(cior.SFXMILEPOST),TRIM(cior.MILEPOST)) || ' ' || cior.RRSUBDIV || ' ' || cils.STREET || ' (' || cils.HIGHWAY || ')') ELSE UPPER( ITGIS.MAKE_NSPD_MILEPOST_SHORT(cior.PRFXMILEPOST,TRIM(cior.SFXMILEPOST),TRIM(cior.MILEPOST)) || ' ' || ec.STREETNAME) END, --SEARCHTEXT" & @CRLF & _
"CASE WHEN q.INVDATE IS NOT NULL THEN UPPER(ITGIS.MAKE_NSPD_MILEPOST_SHORT(cior.PRFXMILEPOST,TRIM(cior.SFXMILEPOST),TRIM(cior.MILEPOST)) || ' ' || cior.RRSUBDIV) ELSE UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(ec.PREFIX), TRIM(ec.SUFFIX), ec.MP, nfsac.DISTRICT, nfsac.BRANCH)) END, --DISPLAYTEXT" & @CRLF & _
"CASE WHEN q.INVDATE IS NOT NULL THEN SDO_CS.MAKE_2D ( cils.SHAPE, 4326) ELSE SDO_CS.MAKE_2D ( ec.SHAPE, 4326) END, --POINT/LINE/POLYGON" & @CRLF & _
"CASE WHEN q.INVDATE IS NOT NULL THEN ROUND(cils.SHAPE.SDO_POINT.y, 6) ELSE ROUND(ec.SHAPE.SDO_POINT.y, 6) END, --LATITUDE" & @CRLF & _
"CASE WHEN q.INVDATE IS NOT NULL THEN ROUND(cils.SHAPE.SDO_POINT.x, 6) ELSE ROUND(ec.SHAPE.SDO_POINT.x, 6) END, --LONGITUDE" & @CRLF & _
"nrz.BEAT_ID, --BEAT" & @CRLF & _
"NULL, --ZIPCODE" & @CRLF & _
"CASE WHEN q.INVDATE IS NOT NULL THEN UPPER(CAST(cils.CITYNAME AS NVARCHAR2(100))) ELSE UPPER(COALESCE(cityBoundaryMatches.CITY, c.NAME)) END, --CITY" & @CRLF & _
"CASE WHEN q.INVDATE IS NOT NULL THEN UPPER(CAST(cils.COUNTYNAME AS NVARCHAR2(50))) ELSE UPPER(countyMatches.COUNTY) END, --COUNTY" & @CRLF & _
"CASE WHEN q.INVDATE IS NOT NULL THEN UPPER(CAST(cils.STATENAME AS NVARCHAR2(2))) ELSE UPPER(CAST(countyMatches.STATE AS NVARCHAR2(2))) END, --STATE" & @CRLF & _
"CASE WHEN q.INVDATE IS NOT NULL THEN UPPER(CAST(cior.RRDIV AS NVARCHAR2(50))) ELSE UPPER(COALESCE(nd.DIVNAME, nsd.DIVNAME)) END, --DIVISION" & @CRLF & _
"UPPER(nrz.FIELD_OFF), --FIELD_OFFICE" & @CRLF & _
"nrz.AREA_ID, --AREA]') || TO_CLOB(q'[" & @CRLF & _
"NULL, --JURISDICTION" & @CRLF & _
"NULL, --MUNICIPALITY" & @CRLF & _
"CASE WHEN q.INVDATE IS NOT NULL THEN CASE cils.POSXING WHEN '1' THEN 'AG' WHEN '2' THEN 'UG' WHEN '3' THEN 'OG' ELSE '' END ELSE CASE CAST(ec.GRADETYPE AS VARCHAR2 ( 50)) WHEN 'ATGRADE' THEN 'AG' WHEN 'RRUNDER' THEN 'UG' WHEN 'RROVER' THEN 'OG' ELSE '' END END, --LOCATION_ID" & @CRLF & _
"CASE WHEN q.INVDATE IS NOT NULL THEN ITGIS.MAKE_NSPD_MILEPOST_SHORT ( TRIM(cior.PRFXMILEPOST), TRIM(cior.SFXMILEPOST), cior.MILEPOST) ELSE ITGIS.MAKE_NSPD_MILEPOST_SHORT ( TRIM(ec.PREFIX), TRIM(ec.SUFFIX), CAST ( CASE WHEN REGEXP_LIKE(TRIM(ec.MPLOCATION),'^\d+(\.\d+)?$','') THEN ec.MPLOCATION ELSE NULL END AS NUMBER )) END, --MILEPOST" & @CRLF & _
"CASE WHEN q.INVDATE IS NOT NULL THEN TRIM(cior.PRFXMILEPOST) ELSE TRIM(ec.PREFIX) END, --MP_PREFIX" & @CRLF & _
"CASE WHEN q.INVDATE IS NOT NULL THEN cior.MILEPOST ELSE CAST ( CASE WHEN REGEXP_LIKE(TRIM(ec.MPLOCATION),'^\d+(\.\d+)?$','') THEN ec.MPLOCATION ELSE NULL END AS NUMBER ) END, --MP_NUMBER" & @CRLF & _
"CASE WHEN q.INVDATE IS NOT NULL THEN TRIM(cior.SFXMILEPOST) ELSE TRIM(ec.SUFFIX) END, --MP_SUFFIX" & @CRLF & _
"CASE WHEN q.INVDATE IS NOT NULL THEN UPPER(cils.STREET || ' (' || cils.HIGHWAY || ')') ELSE UPPER(ec.STREETNAME) END, --NAME" & @CRLF & _
"UPPER(dl.DOTNUM), --CROSSING_DOT_NUM" & @CRLF & _
"NULL, --CROSSING_FULL_NAME" & @CRLF & _
"NULL --TRACK_SEGMENT_RANGE" & @CRLF & _
"FROM ENGXING.DOTLIST dl" & @CRLF & _
"INNER JOIN ENGXING.CI_LOCATIONANDCLASSIFICATION cils" & @CRLF & _
"ON dl.DOTNUM = cils.CROSSINGID" & @CRLF & _
"LEFT JOIN ENGXING.INVENTORY_QUE q" & @CRLF & _
"ON dl.DOTNUM = q.DOTNUM" & @CRLF & _
"LEFT JOIN nsdMatches nsd" & @CRLF & _
"ON dl.DOTNUM = nsd.CROSSINGID" & @CRLF & _
"LEFT JOIN ENGXING.CI_OPERATINGRAILROAD cior" & @CRLF & _
"ON dl.DOTNUM = cior.CROSSINGID" & @CRLF & _
"LEFT JOIN nrzMatches nrz" & @CRLF & _
"ON cils.CROSSINGID = nrz.CROSSINGID" & @CRLF & _
"LEFT JOIN cityBoundaryMatches" & @CRLF & _
"ON cils.CROSSINGID = cityBoundaryMatches.CROSSINGID" & @CRLF & _
"LEFT JOIN countyMatches" & @CRLF & _
"ON cils.CROSSINGID = countyMatches.CROSSINGID" & @CRLF & _
"LEFT JOIN ITGIS.NSPD_LOCATIONS nl" & @CRLF & _
"ON nrz.BEAT_ID = nl.BEAT" & @CRLF & _
"LEFT JOIN ITGIS.NS_DIVISIONS nd" & @CRLF & _
"ON cior.DIVCODE = nd.DIVCODE" & @CRLF & _
"LEFT JOIN ENG.ENG_CROSSING ec" & @CRLF & _
"ON(dl.DOTNUM = ec.DOTNUM AND ec.SUBLOC = 'C')" & @CRLF & _
"LEFT JOIN NFSAPP.CONTROL nfsac" & @CRLF & _
"ON(ITGIS.MAKE_LINEID(ec.DIVCODE, ec.PREFIX, ec.SUFFIX) = nfsac.LINE_ID AND ec.MP BETWEEN nfsac.LMP AND nfsac.HMP)" & @CRLF & _
"CROSS JOIN PUBDATA.US_CITIES c" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_NN(c.SHAPE, cils.SHAPE, 'sdo_num_res = 1', 1) = 'TRUE'" & @CRLF & _
"--AND ROWNUM = 1" & @CRLF & _
"AND 1 = 1" & @CRLF & _
"]')" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
" AND XSCHEMA = 'ENGXING'" & @CRLF & _
" AND XTABLE = 'CI_LOCATIONANDCLASSIFICATION'" & @CRLF & _
" AND 1 = 1;" & @CRLF & _
"" & @CRLF & _
"/* EGIS.ENG_MPMARK */" & @CRLF & _
"UPDATE ITGIS.GLOBALSEARCHMASTER" & @CRLF & _
"SET QUERYTEXT =" & @CRLF & _
" q'[" & @CRLF & _
"WITH nsdMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT mp.OBJECTID," & @CRLF & _
"nsd.DIVCODE," & @CRLF & _
"nsd.DIVDESCR," & @CRLF & _
"nsd.DIVABBR," & @CRLF & _
"nsd.DIVNAME" & @CRLF & _
"FROM EGIS.ENG_MPMARK mp" & @CRLF & _
"CROSS JOIN ITGIS.NS_DIVISIONS nsd" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( mp.SHAPE, nsd.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"nrzMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT mp.OBJECTID," & @CRLF & _
"nrz.BEAT_ID," & @CRLF & _
"nrz.AREA_ID," & @CRLF & _
"nrz.FIELD_OFF," & @CRLF & _
"nrz.COMMON_NAME NSPD_RESP_ZONE" & @CRLF & _
"FROM EGIS.ENG_MPMARK mp" & @CRLF & _
"CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( mp.SHAPE, nrz.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"countyMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT mp.OBJECTID," & @CRLF & _
"usc.STATE," & @CRLF & _
"usc.COUNTY" & @CRLF & _
"FROM EGIS.ENG_MPMARK mp" & @CRLF & _
"CROSS JOIN PUBDATA.US_COUNTIES usc" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( usc.SHAPE, mp.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"cityBoundaryMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT mp.OBJECTID," & @CRLF & _
"uscb.NAME CITY" & @CRLF & _
"FROM EGIS.ENG_MPMARK mp" & @CRLF & _
"CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( uscb.SHAPE, mp.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")" & @CRLF & _
"SELECT" & @CRLF & _
"***MASTERID***, --MASTERID" & @CRLF & _
"mp.OBJECTID, --XUID" & @CRLF & _
"UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(mp.PREFIX), TRIM(mp.SUFFIX), mp.MPMARKER, nfsac.DISTRICT, nfsac.BRANCH)), --SEARCHTEXT" & @CRLF & _
"UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(mp.PREFIX), TRIM(mp.SUFFIX), mp.MPMARKER, nfsac.DISTRICT, nfsac.BRANCH)), --DISPLAYTEXT" & @CRLF & _
"SDO_CS.MAKE_2D ( mp.SHAPE, 4326 ), --POINT/LINE/POLYGON" & @CRLF & _
"ROUND ( mp.SHAPE.SDO_POINT.y, 6 ), --LATITUDE" & @CRLF & _
"ROUND ( mp.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE" & @CRLF & _
"nrz.BEAT_ID, --BEAT" & @CRLF & _
"NULL, --ZIPCODE" & @CRLF & _
"UPPER(COALESCE(cityBoundaryMatches.CITY,c.NAME)), --CITY" & @CRLF & _
"UPPER(countyMatches.COUNTY), --COUNTY" & @CRLF & _
"UPPER(countyMatches.STATE), --STATE" & @CRLF & _
"UPPER(COALESCE(nd.DIVNAME,nsd.DIVNAME)), --DIVISION" & @CRLF & _
"UPPER(nrz.FIELD_OFF), --FIELD_OFFICE" & @CRLF & _
"nrz.AREA_ID, --AREA" & @CRLF & _
"UPPER(nl.OFFICEDESCRIPTION), --JURISDICTION" & @CRLF & _
"UPPER(COALESCE(cityBoundaryMatches.CITY,c.NAME)), --MUNICIPALITY" & @CRLF & _
"NULL, --LOCATION_ID" & @CRLF & _
"UPPER(ITGIS.MAKE_NSPD_MILEPOST_SHORT ( TRIM(mp.PREFIX), TRIM(mp.SUFFIX), mp.MPMARKER )), --MILEPOST" & @CRLF & _
"UPPER(TRIM(mp.PREFIX)), --MP_PREFIX" & @CRLF & _
"mp.MPMARKER, --MP_NUMBER" & @CRLF & _
"UPPER(TRIM(mp.SUFFIX)), --MP_SUFFIX" & @CRLF & _
"UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(mp.PREFIX), TRIM(mp.SUFFIX), mp.MPMARKER, nfsac.DISTRICT, nfsac.BRANCH)), --NAME" & @CRLF & _
"NULL, --CROSSING_DOT_NUM" & @CRLF & _
"NULL, --CROSSING_FULL_NAME" & @CRLF & _
"NULL --TRACK_SEGMENT_RANGE" & @CRLF & _
"FROM EGIS.ENG_MPMARK mp" & @CRLF & _
"LEFT JOIN nrzMatches nrz" & @CRLF & _
"ON mp.OBJECTID = nrz.OBJECTID" & @CRLF & _
"LEFT JOIN nsdMatches nsd" & @CRLF & _
"ON mp.OBJECTID = nsd.OBJECTID" & @CRLF & _
"LEFT JOIN cityBoundaryMatches" & @CRLF & _
"ON mp.OBJECTID = cityBoundaryMatches.OBJECTID" & @CRLF & _
"LEFT JOIN countyMatches" & @CRLF & _
"ON mp.OBJECTID = countyMatches.OBJECTID" & @CRLF & _
"LEFT JOIN ITGIS.NSPD_LOCATIONS nl" & @CRLF & _
"ON nrz.BEAT_ID = nl.BEAT" & @CRLF & _
"LEFT JOIN ITGIS.NS_DIVISIONS nd" & @CRLF & _
"ON mp.DIVCODE = nd.DIVCODE" & @CRLF & _
"LEFT JOIN NFSAPP.CONTROL nfsac" & @CRLF & _
"ON(ITGIS.MAKE_LINEID(mp.DIVCODE, mp.PREFIX, mp.SUFFIX) = nfsac.LINE_ID AND mp.MPMARKER BETWEEN nfsac.LMP AND nfsac.HMP)" & @CRLF & _
"CROSS JOIN PUBDATA.US_CITIES c" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_NN ( c.SHAPE, mp.SHAPE, 'sdo_num_res = 1', 1 ) = 'TRUE'" & @CRLF & _
"--AND ROWNUM = 1" & @CRLF & _
"AND 1 = 1" & @CRLF & _
"]'" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
" AND XSCHEMA = 'EGIS'" & @CRLF & _
" AND XTABLE = 'ENG_MPMARK'" & @CRLF & _
" AND 1 = 1;" & @CRLF & _
"" & @CRLF & _
"/* EGIS.ENG_SWITCH */" & @CRLF & _
"UPDATE ITGIS.GLOBALSEARCHMASTER" & @CRLF & _
"SET QUERYTEXT =" & @CRLF & _
" q'[" & @CRLF & _
"WITH nsdMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT s.OBJECTID," & @CRLF & _
"nsd.DIVCODE," & @CRLF & _
"nsd.DIVDESCR," & @CRLF & _
"nsd.DIVABBR," & @CRLF & _
"nsd.DIVNAME" & @CRLF & _
"FROM EGIS.ENG_SWITCH s" & @CRLF & _
"CROSS JOIN ITGIS.NS_DIVISIONS nsd" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( s.SHAPE, nsd.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"nrzMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT s.OBJECTID," & @CRLF & _
"nrz.BEAT_ID," & @CRLF & _
"nrz.AREA_ID," & @CRLF & _
"nrz.FIELD_OFF," & @CRLF & _
"nrz.COMMON_NAME NSPD_RESP_ZONE" & @CRLF & _
"FROM EGIS.ENG_SWITCH s" & @CRLF & _
"CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( s.SHAPE, nrz.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"countyMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT s.OBJECTID," & @CRLF & _
"usc.STATE," & @CRLF & _
"usc.COUNTY" & @CRLF & _
"FROM EGIS.ENG_SWITCH s" & @CRLF & _
"CROSS JOIN PUBDATA.US_COUNTIES usc" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( usc.SHAPE, s.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"cityBoundaryMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT s.OBJECTID," & @CRLF & _
"uscb.NAME CITY" & @CRLF & _
"FROM EGIS.ENG_SWITCH s" & @CRLF & _
"CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( uscb.SHAPE, s.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")" & @CRLF & _
"SELECT" & @CRLF & _
"***MASTERID***, --MASTERID" & @CRLF & _
"s.OBJECTID, --XUID" & @CRLF & _
"UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(s.PREFIX), TRIM(s.SUFFIX), s.MP, nfsac.DISTRICT, nfsac.BRANCH)), --SEARCHTEXT" & @CRLF & _
"UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(s.PREFIX), TRIM(s.SUFFIX), s.MP, nfsac.DISTRICT, nfsac.BRANCH)), --DISPLAYTEXT" & @CRLF & _
"SDO_CS.MAKE_2D ( s.SHAPE, 4326 ), --POINT/LINE/POLYGON" & @CRLF & _
"ROUND ( s.SHAPE.SDO_POINT.y, 6 ), --LATITUDE" & @CRLF & _
"ROUND ( s.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE" & @CRLF & _
"nrz.BEAT_ID, --BEAT" & @CRLF & _
"NULL, --ZIPCODE" & @CRLF & _
"UPPER(COALESCE(cityBoundaryMatches.CITY,c.NAME)), --CITY" & @CRLF & _
"UPPER(countyMatches.COUNTY), --COUNTY" & @CRLF & _
"UPPER(countyMatches.STATE), --STATE" & @CRLF & _
"UPPER(COALESCE(nd.DIVNAME,nsd.DIVNAME)), --DIVISION" & @CRLF & _
"UPPER(nrz.FIELD_OFF), --FIELD_OFFICE" & @CRLF & _
"nrz.AREA_ID, --AREA" & @CRLF & _
"NULL, --JURISDICTION" & @CRLF & _
"NULL, --MUNICIPALITY" & @CRLF & _
"NULL, --LOCATION_ID" & @CRLF & _
"UPPER(ITGIS.MAKE_NSPD_MILEPOST_SHORT(TRIM(s.PREFIX), TRIM(s.SUFFIX), s.MP)), --MILEPOST" & @CRLF & _
"UPPER(TRIM(s.PREFIX)), --MP_PREFIX" & @CRLF & _
"s.MP, --MP_NUMBER" & @CRLF & _
"UPPER(TRIM(s.SUFFIX)), --MP_SUFFIX" & @CRLF & _
"UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(s.PREFIX), TRIM(s.SUFFIX), s.MP, nfsac.DISTRICT, nfsac.BRANCH)), --NAME" & @CRLF & _
"NULL, --CROSSING_DOT_NUM" & @CRLF & _
"NULL, --CROSSING_FULL_NAME" & @CRLF & _
"NULL --TRACK_SEGMENT_RANGE" & @CRLF & _
"FROM EGIS.ENG_SWITCH s" & @CRLF & _
"LEFT JOIN nsdMatches nsd" & @CRLF & _
"ON s.OBJECTID = nsd.OBJECTID" & @CRLF & _
"LEFT JOIN nrzMatches nrz" & @CRLF & _
"ON s.OBJECTID = nrz.OBJECTID" & @CRLF & _
"LEFT JOIN cityBoundaryMatches" & @CRLF & _
"ON s.OBJECTID = cityBoundaryMatches.OBJECTID" & @CRLF & _
"LEFT JOIN countyMatches" & @CRLF & _
"ON s.OBJECTID = countyMatches.OBJECTID" & @CRLF & _
"LEFT JOIN ITGIS.NSPD_LOCATIONS nl" & @CRLF & _
"ON nrz.BEAT_ID = nl.BEAT" & @CRLF & _
"LEFT JOIN ITGIS.NS_DIVISIONS nd" & @CRLF & _
"ON s.DIVCODE = nd.DIVCODE" & @CRLF & _
"LEFT JOIN NFSAPP.CONTROL nfsac" & @CRLF & _
"ON(ITGIS.MAKE_LINEID(s.DIVCODE, s.PREFIX, s.SUFFIX) = nfsac.LINE_ID AND s.MP BETWEEN nfsac.LMP AND nfsac.HMP)" & @CRLF & _
"CROSS JOIN PUBDATA.US_CITIES c" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_NN ( c.SHAPE, s.SHAPE, 'sdo_num_res = 1', 1 ) = 'TRUE'" & @CRLF & _
"--AND ROWNUM = 1" & @CRLF & _
"AND 1 = 1" & @CRLF & _
"]'" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
" AND XSCHEMA = 'EGIS'" & @CRLF & _
" AND XTABLE = 'ENG_SWITCH'" & @CRLF & _
" AND 1 = 1;" & @CRLF & _
"" & @CRLF & _
"-- /* EGIS.ENG_TOWER */" & @CRLF & _
"UPDATE ITGIS.GLOBALSEARCHMASTER" & @CRLF & _
"SET QUERYTEXT =" & @CRLF & _
" q'[" & @CRLF & _
"WITH nsdMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT t.OBJECTID," & @CRLF & _
"nsd.DIVCODE," & @CRLF & _
"nsd.DIVDESCR," & @CRLF & _
"nsd.DIVABBR," & @CRLF & _
"nsd.DIVNAME" & @CRLF & _
"FROM EGIS.ENG_TOWER t" & @CRLF & _
"CROSS JOIN ITGIS.NS_DIVISIONS nsd" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( t.SHAPE, nsd.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"nrzMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT t.OBJECTID," & @CRLF & _
"nrz.BEAT_ID," & @CRLF & _
"nrz.AREA_ID," & @CRLF & _
"nrz.FIELD_OFF," & @CRLF & _
"nrz.COMMON_NAME NSPD_RESP_ZONE" & @CRLF & _
"FROM EGIS.ENG_TOWER t" & @CRLF & _
"CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( t.SHAPE, nrz.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"countyMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT t.OBJECTID," & @CRLF & _
"usc.STATE," & @CRLF & _
"usc.COUNTY" & @CRLF & _
"FROM EGIS.ENG_TOWER t" & @CRLF & _
"CROSS JOIN PUBDATA.US_COUNTIES usc" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( usc.SHAPE, t.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"cityBoundaryMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT t.OBJECTID," & @CRLF & _
"uscb.NAME CITY" & @CRLF & _
"FROM EGIS.ENG_TOWER t" & @CRLF & _
"CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( uscb.SHAPE, t.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")" & @CRLF & _
"SELECT" & @CRLF & _
"***MASTERID***, --MASTERID" & @CRLF & _
"t.OBJECTID, --XUID" & @CRLF & _
"UPPER(t.SITENAME || ' ' || ITGIS.MAKE_NSPD_MP_DB_LINEID(t.LINEID, REGEXP_SUBSTR(t.MILEPOSTCONCAT, '\d{1,}\.{0,}\d{0,}'))), --SEARCHTEXT" & @CRLF & _
"UPPER(ITGIS.MAKE_NSPD_MP_DB_LINEID(t.LINEID, REGEXP_SUBSTR(t.MILEPOSTCONCAT, '\d{1,}\.{0,}\d{0,}'))), --DISPLAYTEXT" & @CRLF & _
"SDO_CS.MAKE_2D ( t.SHAPE, 4326 ), --POINT/LINE/POLYGON" & @CRLF & _
"ROUND ( t.SHAPE.SDO_POINT.y, 6 ), --LATITUDE" & @CRLF & _
"ROUND ( t.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE" & @CRLF & _
"nrz.BEAT_ID, --BEAT" & @CRLF & _
"NULL, --ZIPCODE" & @CRLF & _
"UPPER(COALESCE(cityBoundaryMatches.CITY,c.NAME)), --CITY" & @CRLF & _
"UPPER(countyMatches.COUNTY), --COUNTY" & @CRLF & _
"UPPER(countyMatches.STATE), --STATE" & @CRLF & _
"UPPER(COALESCE(nd.DIVNAME,nsd.DIVNAME)), --DIVISION" & @CRLF & _
"UPPER(nrz.FIELD_OFF), --FIELD_OFFICE" & @CRLF & _
"nrz.AREA_ID, --AREA" & @CRLF & _
"NULL, --JURISDICTION" & @CRLF & _
"NULL, --MUNICIPALITY" & @CRLF & _
"NULL, --LOCATION_ID" & @CRLF & _
"UPPER(ITGIS.MAKE_NSPD_MILEPOST_SHORT(ITGIS.MAKE_PREFIX_LINEID(t.LINEID),ITGIS.MAKE_SUFFIX_LINEID(t.LINEID),REGEXP_SUBSTR(t.MILEPOSTCONCAT, '\d{1,}\.{0,}\d{0,}'))), --MILEPOST" & @CRLF & _
"UPPER(ITGIS.MAKE_PREFIX_LINEID(t.LINEID)), --MP_PREFIX" & @CRLF & _
"REGEXP_SUBSTR(t.MILEPOSTCONCAT, '\d{1,}\.{0,}\d{0,}'), --MP_NUMBER" & @CRLF & _
"UPPER(ITGIS.MAKE_SUFFIX_LINEID(t.LINEID)), --MP_SUFFIX" & @CRLF & _
"UPPER(t.SITENAME), --NAME" & @CRLF & _
"NULL, --CROSSING_DOT_NUM" & @CRLF & _
"NULL, --CROSSING_FULL_NAME" & @CRLF & _
"NULL --TRACK_SEGMENT_RANGE" & @CRLF & _
"FROM EGIS.ENG_TOWER t" & @CRLF & _
"LEFT JOIN nsdMatches nsd" & @CRLF & _
"ON t.OBJECTID = nsd.OBJECTID" & @CRLF & _
"LEFT JOIN nrzMatches nrz" & @CRLF & _
"ON t.OBJECTID = nrz.OBJECTID" & @CRLF & _
"LEFT JOIN cityBoundaryMatches" & @CRLF & _
"ON t.OBJECTID = cityBoundaryMatches.OBJECTID" & @CRLF & _
"LEFT JOIN countyMatches" & @CRLF & _
"ON t.OBJECTID = countyMatches.OBJECTID" & @CRLF & _
"LEFT JOIN ITGIS.NSPD_LOCATIONS nl" & @CRLF & _
"ON nrz.BEAT_ID = nl.BEAT" & @CRLF & _
"LEFT JOIN ITGIS.NS_DIVISIONS nd" & @CRLF & _
"ON SUBSTR ( t.LINEID, 0, 2 ) = nd.DIVCODE" & @CRLF & _
"CROSS JOIN PUBDATA.US_CITIES c" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_NN ( c.SHAPE, t.SHAPE, 'sdo_num_res = 1', 1 ) = 'TRUE'" & @CRLF & _
"--AND ROWNUM = 1" & @CRLF & _
"AND 1 = 1" & @CRLF & _
"]'" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
" AND XSCHEMA = 'EGIS'" & @CRLF & _
" AND XTABLE = 'ENG_TOWER'" & @CRLF & _
" AND 1 = 1;" & @CRLF & _
"" & @CRLF & _
"/* ITGIS.COMP_AEI_SCNR */" & @CRLF & _
"UPDATE ITGIS.GLOBALSEARCHMASTER" & @CRLF & _
"SET QUERYTEXT =" & @CRLF & _
" q'[" & @CRLF & _
"WITH nsdMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT s.OBJECTID," & @CRLF & _
"nsd.DIVCODE," & @CRLF & _
"nsd.DIVDESCR," & @CRLF & _
"nsd.DIVABBR," & @CRLF & _
"nsd.DIVNAME" & @CRLF & _
"FROM ITGIS.COMP_AEI_SCNR s" & @CRLF & _
"CROSS JOIN ITGIS.NS_DIVISIONS nsd" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( s.SHAPE, nsd.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"nrzMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT s.OBJECTID," & @CRLF & _
"nrz.BEAT_ID," & @CRLF & _
"nrz.AREA_ID," & @CRLF & _
"nrz.FIELD_OFF," & @CRLF & _
"nrz.COMMON_NAME NSPD_RESP_ZONE" & @CRLF & _
"FROM ITGIS.COMP_AEI_SCNR s" & @CRLF & _
"CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( s.SHAPE, nrz.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"countyMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT s.OBJECTID," & @CRLF & _
"usc.STATE," & @CRLF & _
"usc.COUNTY" & @CRLF & _
"FROM ITGIS.COMP_AEI_SCNR s" & @CRLF & _
"CROSS JOIN PUBDATA.US_COUNTIES usc" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( usc.SHAPE, s.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"cityBoundaryMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT s.OBJECTID," & @CRLF & _
"uscb.NAME CITY" & @CRLF & _
"FROM ITGIS.COMP_AEI_SCNR s" & @CRLF & _
"CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( uscb.SHAPE, s.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")" & @CRLF & _
"SELECT" & @CRLF & _
"***MASTERID***, --MASTERID" & @CRLF & _
"s.OBJECTID, --XUID" & @CRLF & _
"UPPER(s.NAME || ' ' || ITGIS.MAKE_NSPD_MILEPOST_SHORT(TRIM(s.PREFIX),TRIM(s.SUFFIX),s.MP)), --SEARCHTEXT" & @CRLF & _
"UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(s.PREFIX), TRIM(s.SUFFIX), s.MP, nfsac.DISTRICT, nfsac.BRANCH)), --DISPLAYTEXT" & @CRLF & _
"SDO_CS.MAKE_2D ( s.SHAPE, 4326 ), --POINT/LINE/POLYGON" & @CRLF & _
"ROUND ( s.SHAPE.SDO_POINT.y, 6 ), --LATITUDE" & @CRLF & _
"ROUND ( s.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE" & @CRLF & _
"nrz.BEAT_ID, --BEAT" & @CRLF & _
"UPPER(s.POSTAL_CD), --ZIPCODE" & @CRLF & _
"UPPER(COALESCE(cityBoundaryMatches.CITY,c.NAME)), --CITY" & @CRLF & _
"UPPER(countyMatches.COUNTY), --COUNTY" & @CRLF & _
"UPPER(countyMatches.STATE), --STATE" & @CRLF & _
"UPPER(COALESCE(nd.DIVNAME,nsd.DIVNAME)), --DIVISION" & @CRLF & _
"UPPER(nrz.FIELD_OFF), --FIELD_OFFICE" & @CRLF & _
"nrz.AREA_ID, --AREA" & @CRLF & _
"NULL, --JURISDICTION" & @CRLF & _
"NULL, --MUNICIPALITY" & @CRLF & _
"NULL, --LOCATION_ID" & @CRLF & _
"UPPER(ITGIS.MAKE_NSPD_MILEPOST_SHORT(TRIM(s.PREFIX),TRIM(s.SUFFIX),s.MP)), --MILEPOST" & @CRLF & _
"UPPER(TRIM(s.PREFIX)), --MP_PREFIX" & @CRLF & _
"s.MP, --MP_NUMBER" & @CRLF & _
"UPPER(TRIM(s.SUFFIX)), --MP_SUFFIX" & @CRLF & _
"UPPER(s.NAME), --NAME" & @CRLF & _
"NULL, --CROSSING_DOT_NUM" & @CRLF & _
"NULL, --CROSSING_FULL_NAME" & @CRLF & _
"NULL --TRACK_SEGMENT_RANGE" & @CRLF & _
"FROM ITGIS.COMP_AEI_SCNR s" & @CRLF & _
"LEFT JOIN nsdMatches nsd" & @CRLF & _
"ON s.OBJECTID = nsd.OBJECTID" & @CRLF & _
"LEFT JOIN nrzMatches nrz" & @CRLF & _
"ON s.OBJECTID = nrz.OBJECTID" & @CRLF & _
"LEFT JOIN cityBoundaryMatches" & @CRLF & _
"ON s.OBJECTID = cityBoundaryMatches.OBJECTID" & @CRLF & _
"LEFT JOIN countyMatches" & @CRLF & _
"ON s.OBJECTID = countyMatches.OBJECTID" & @CRLF & _
"LEFT JOIN ITGIS.NSPD_LOCATIONS nl" & @CRLF & _
"ON nrz.BEAT_ID = nl.BEAT" & @CRLF & _
"LEFT JOIN ITGIS.NS_DIVISIONS nd" & @CRLF & _
"ON s.DIVCODE = nd.DIVCODE" & @CRLF & _
"LEFT JOIN NFSAPP.CONTROL nfsac" & @CRLF & _
"ON(ITGIS.MAKE_LINEID(s.DIVCODE, s.PREFIX, s.SUFFIX) = nfsac.LINE_ID AND s.MP BETWEEN nfsac.LMP AND nfsac.HMP)" & @CRLF & _
"CROSS JOIN PUBDATA.US_CITIES c" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_NN ( c.SHAPE, s.SHAPE, 'sdo_num_res = 1', 1 ) = 'TRUE'" & @CRLF & _
"--AND ROWNUM = 1" & @CRLF & _
"AND 1 = 1" & @CRLF & _
"]'" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
" AND XSCHEMA = 'ITGIS'" & @CRLF & _
" AND XTABLE = 'COMP_AEI_SCNR'" & @CRLF & _
" AND 1 = 1;" & @CRLF & _
"" & @CRLF & _
"/* ITGIS.NSPD_LOCATIONS*/" & @CRLF & _
"UPDATE ITGIS.GLOBALSEARCHMASTER" & @CRLF & _
"SET QUERYTEXT =" & @CRLF & _
" q'[" & @CRLF & _
"WITH nsdMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT l.OBJECTID," & @CRLF & _
"nsd.DIVCODE," & @CRLF & _
"nsd.DIVDESCR," & @CRLF & _
"nsd.DIVABBR," & @CRLF & _
"nsd.DIVNAME" & @CRLF & _
"FROM ITGIS.NSPD_LOCATIONS l" & @CRLF & _
"CROSS JOIN ITGIS.NS_DIVISIONS nsd" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( l.SHAPE, nsd.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"nrzMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT l.OBJECTID," & @CRLF & _
"nrz.BEAT_ID," & @CRLF & _
"nrz.AREA_ID," & @CRLF & _
"nrz.FIELD_OFF," & @CRLF & _
"nrz.COMMON_NAME NSPD_RESP_ZONE" & @CRLF & _
"FROM ITGIS.NSPD_LOCATIONS l" & @CRLF & _
"CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( l.SHAPE, nrz.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"countyMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT l.OBJECTID," & @CRLF & _
"usc.STATE," & @CRLF & _
"usc.COUNTY" & @CRLF & _
"FROM ITGIS.NSPD_LOCATIONS l" & @CRLF & _
"CROSS JOIN PUBDATA.US_COUNTIES usc" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( usc.SHAPE, l.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"cityBoundaryMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT l.OBJECTID," & @CRLF & _
"uscb.NAME CITY" & @CRLF & _
"FROM ITGIS.NSPD_LOCATIONS l" & @CRLF & _
"CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( uscb.SHAPE, l.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")" & @CRLF & _
"SELECT" & @CRLF & _
"***MASTERID***, --MASTERID" & @CRLF & _
"l.OBJECTID, --XUID" & @CRLF & _
"UPPER(l.OFFICEDESCRIPTION), --SEARCHTEXT" & @CRLF & _
"UPPER(l.ADDRESS), --DISPLAYTEXT" & @CRLF & _
"SDO_CS.MAKE_2D ( l.SHAPE, 4326 ), --POINT/LINE/POLYGON" & @CRLF & _
"ROUND ( l.SHAPE.SDO_POINT.y, 6 ), --LATITUDE" & @CRLF & _
"ROUND ( l.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE" & @CRLF & _
"nrz.BEAT_ID, --BEAT" & @CRLF & _
"UPPER(l.POSTALCODE), --ZIPCODE" & @CRLF & _
"UPPER(COALESCE(l.CITY,cityBoundaryMatches.CITY,c.NAME)), --CITY" & @CRLF & _
"UPPER(countyMatches.COUNTY), --COUNTY" & @CRLF & _
"UPPER(COALESCE(l.STATE,countyMatches.STATE)), --STATE" & @CRLF & _
"UPPER(nsd.DIVNAME), --DIVISION" & @CRLF & _
"UPPER(l.OFFICEDESCRIPTION), --FIELD_OFFICE" & @CRLF & _
"nrz.AREA_ID, --AREA" & @CRLF & _
"NULL, --JURISDICTION" & @CRLF & _
"NULL, --MUNICIPALITY" & @CRLF & _
"NULL, --LOCATION_ID" & @CRLF & _
"NULL, --MILEPOST" & @CRLF & _
"NULL, --MP_PREFIX" & @CRLF & _
"NULL, --MP_NUMBER" & @CRLF & _
"NULL, --MP_SUFFIX" & @CRLF & _
"UPPER(l.OFFICEDESCRIPTION), --NAME" & @CRLF & _
"NULL, --CROSSING_DOT_NUM" & @CRLF & _
"NULL, --CROSSING_FULL_NAME" & @CRLF & _
"NULL --TRACK_SEGMENT_RANGE" & @CRLF & _
"FROM ITGIS.NSPD_LOCATIONS l" & @CRLF & _
"LEFT JOIN nsdMatches nsd" & @CRLF & _
"ON l.OBJECTID = nsd.OBJECTID" & @CRLF & _
"LEFT JOIN nrzMatches nrz" & @CRLF & _
"ON l.OBJECTID = nrz.OBJECTID" & @CRLF & _
"LEFT JOIN cityBoundaryMatches" & @CRLF & _
"ON l.OBJECTID = cityBoundaryMatches.OBJECTID" & @CRLF & _
"LEFT JOIN countyMatches" & @CRLF & _
"ON l.OBJECTID = countyMatches.OBJECTID" & @CRLF & _
"LEFT JOIN ITGIS.NSPD_LOCATIONS nl" & @CRLF & _
"ON nrz.BEAT_ID = nl.BEAT" & @CRLF & _
"--NO DIVCODE FIELD TO JOIN TO" & @CRLF & _
"CROSS JOIN PUBDATA.US_CITIES c" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_NN ( c.SHAPE, l.SHAPE, 'sdo_num_res = 1', 1 ) = 'TRUE'" & @CRLF & _
"--AND ROWNUM = 1" & @CRLF & _
"AND 1 = 1" & @CRLF & _
"]'" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
" AND XSCHEMA = 'ITGIS'" & @CRLF & _
" AND XTABLE = 'NSPD_LOCATIONS'" & @CRLF & _
" AND 1 = 1;" & @CRLF & _
"" & @CRLF & _
"/* NSPD.NSPD_POI */" & @CRLF & _
"UPDATE ITGIS.GLOBALSEARCHMASTER" & @CRLF & _
"SET QUERYTEXT =" & @CRLF & _
"q'[" & @CRLF & _
"WITH nsdMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT poi.ID," & @CRLF & _
"nsd.DIVCODE," & @CRLF & _
"nsd.DIVDESCR," & @CRLF & _
"nsd.DIVABBR," & @CRLF & _
"nsd.DIVNAME" & @CRLF & _
"FROM NSPD.NSPD_POI poi" & @CRLF & _
"CROSS JOIN ITGIS.NS_DIVISIONS nsd" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( poi.SHAPE, nsd.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"nrzMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT poi.ID," & @CRLF & _
"nrz.BEAT_ID," & @CRLF & _
"nrz.AREA_ID," & @CRLF & _
"nrz.FIELD_OFF," & @CRLF & _
"nrz.COMMON_NAME NSPD_RESP_ZONE" & @CRLF & _
"FROM NSPD.NSPD_POI poi" & @CRLF & _
"CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( poi.SHAPE, nrz.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"countyMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT poi.ID," & @CRLF & _
"usc.STATE," & @CRLF & _
"usc.COUNTY" & @CRLF & _
"FROM NSPD.NSPD_POI poi" & @CRLF & _
"CROSS JOIN PUBDATA.US_COUNTIES usc" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( usc.SHAPE, poi.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"cityBoundaryMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT poi.ID," & @CRLF & _
"uscb.NAME CITY" & @CRLF & _
"FROM NSPD.NSPD_POI poi" & @CRLF & _
"CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( uscb.SHAPE, poi.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")" & @CRLF & _
"SELECT" & @CRLF & _
"***MASTERID***, --MASTERID" & @CRLF & _
"poi.ID, --XUID" & @CRLF & _
"UPPER( poi.NAME), --SEARCHTEXT" & @CRLF & _
"UPPER( poi.ADDRESS), --DISPLAYTEXT" & @CRLF & _
"SDO_CS.MAKE_2D ( poi.SHAPE, 4326 ), --POINT/LINE/POLYGON" & @CRLF & _
"ROUND ( poi.SHAPE.SDO_POINT.y, 6 ), --LATITUDE" & @CRLF & _
"ROUND ( poi.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE" & @CRLF & _
"nrz.BEAT_ID, --BEAT" & @CRLF & _
"NULL, --ZIPCODE" & @CRLF & _
"UPPER(COALESCE(CAST(poi.CITY AS NVARCHAR2(100)),cityBoundaryMatches.CITY,c.NAME)), --CITY" & @CRLF & _
"UPPER(COALESCE(CAST(poi.COUNTY AS NVARCHAR2(100)),countyMatches.COUNTY)), --COUNTY" & @CRLF & _
"UPPER(COALESCE(CAST(poi.STATE AS NVARCHAR2(100)),countyMatches.STATE)), --STATE" & @CRLF & _
"UPPER(nsd.DIVNAME), --DIVISION" & @CRLF & _
"UPPER(nrz.FIELD_OFF), --FIELD_OFFICE" & @CRLF & _
"nrz.AREA_ID, --AREA" & @CRLF & _
"NULL, --JURISDICTION" & @CRLF & _
"NULL, --MUNICIPALITY" & @CRLF & _
"NULL, --LOCATION_ID" & @CRLF & _
"NULL, --MILEPOST" & @CRLF & _
"NULL, --MP_PREFIX" & @CRLF & _
"NULL, --MP_NUMBER" & @CRLF & _
"NULL, --MP_SUFFIX" & @CRLF & _
"UPPER( poi.NAME), --NAME" & @CRLF & _
"NULL, --CROSSING_DOT_NUM" & @CRLF & _
"NULL, --CROSSING_FULL_NAME" & @CRLF & _
"NULL --TRACK_SEGMENT_RANGE" & @CRLF & _
"FROM NSPD.NSPD_POI poi" & @CRLF & _
"LEFT JOIN nsdMatches nsd" & @CRLF & _
"ON poi.ID = nsd.ID" & @CRLF & _
"LEFT JOIN nrzMatches nrz" & @CRLF & _
"ON poi.ID = nrz.ID" & @CRLF & _
"LEFT JOIN cityBoundaryMatches" & @CRLF & _
"ON poi.ID = cityBoundaryMatches.ID" & @CRLF & _
"LEFT JOIN countyMatches" & @CRLF & _
"ON poi.ID = countyMatches.ID" & @CRLF & _
"LEFT JOIN ITGIS.NSPD_LOCATIONS nl" & @CRLF & _
"ON nrz.BEAT_ID = nl.BEAT" & @CRLF & _
"--NO DIVCODE FIELD TO JOIN TO" & @CRLF & _
"CROSS JOIN PUBDATA.US_CITIES c" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_NN ( c.SHAPE, poi.SHAPE, 'sdo_num_res = 1', 1 ) = 'TRUE'" & @CRLF & _
"--AND ROWNUM = 1" & @CRLF & _
"AND 1 = 1" & @CRLF & _
"]'" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND XSCHEMA = 'NSPD'" & @CRLF & _
"AND XTABLE = 'NSPD_POI'" & @CRLF & _
"AND 1 = 1;" & @CRLF & _
"" & @CRLF & _
"/* ITGIS.ITGIS_MP100S */" & @CRLF & _
"UPDATE ITGIS.GLOBALSEARCHMASTER" & @CRLF & _
"SET QUERYTEXT =" & @CRLF & _
"q'[" & @CRLF & _
"WITH nsdMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT mp.OBJECTID," & @CRLF & _
"nsd.DIVCODE," & @CRLF & _
"nsd.DIVDESCR," & @CRLF & _
"nsd.DIVABBR," & @CRLF & _
"nsd.DIVNAME" & @CRLF & _
"FROM ITGIS.ITGIS_MP100S mp" & @CRLF & _
"CROSS JOIN ITGIS.NS_DIVISIONS nsd" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( mp.SHAPE, nsd.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"nrzMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT mp.OBJECTID," & @CRLF & _
"nrz.BEAT_ID," & @CRLF & _
"nrz.AREA_ID," & @CRLF & _
"nrz.FIELD_OFF," & @CRLF & _
"nrz.COMMON_NAME NSPD_RESP_ZONE" & @CRLF & _
"FROM ITGIS.ITGIS_MP100S mp" & @CRLF & _
"CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( mp.SHAPE, nrz.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"countyMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT mp.OBJECTID," & @CRLF & _
"usc.STATE," & @CRLF & _
"usc.COUNTY" & @CRLF & _
"FROM ITGIS.ITGIS_MP100S mp" & @CRLF & _
"CROSS JOIN PUBDATA.US_COUNTIES usc" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( usc.SHAPE, mp.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")," & @CRLF & _
"cityBoundaryMatches AS" & @CRLF & _
"(" & @CRLF & _
"SELECT mp.OBJECTID," & @CRLF & _
"uscb.NAME CITY" & @CRLF & _
"FROM ITGIS.ITGIS_MP100S mp" & @CRLF & _
"CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_RELATE ( uscb.SHAPE, mp.SHAPE, 'mask = anyinteract' ) = 'TRUE'" & @CRLF & _
"AND 1 = 1" & @CRLF & _
")" & @CRLF & _
"SELECT" & @CRLF & _
"***MASTERID***, --MASTERID" & @CRLF & _
"mp.OBJECTID, --XUID" & @CRLF & _
"UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS(mp.DIVCODE,TRIM(mp.PREFIX),TRIM(mp.SUFFIX),TRIM(mp.MP))), --SEARCHTEXT" & @CRLF & _
"UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS(mp.DIVCODE,TRIM(mp.PREFIX),TRIM(mp.SUFFIX),TRIM(mp.MP))), --DISPLAYTEXT" & @CRLF & _
"SDO_CS.MAKE_2D ( mp.SHAPE, 4326 ), --POINT/LINE/POLYGON" & @CRLF & _
"ROUND ( mp.SHAPE.SDO_POINT.y, 6 ), --LATITUDE" & @CRLF & _
"ROUND ( mp.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE" & @CRLF & _
"nrz.BEAT_ID, --BEAT" & @CRLF & _
"NULL, --ZIPCODE" & @CRLF & _
"UPPER(COALESCE(cityBoundaryMatches.CITY,c.NAME)), --CITY" & @CRLF & _
"UPPER(countyMatches.COUNTY), --COUNTY" & @CRLF & _
"UPPER(countyMatches.STATE), --STATE" & @CRLF & _
"UPPER(COALESCE(nd.DIVNAME,nsd.DIVNAME)), --DIVISION" & @CRLF & _
"UPPER(nrz.FIELD_OFF), --FIELD_OFFICE" & @CRLF & _
"nrz.AREA_ID, --AREA" & @CRLF & _
"NULL, --JURISDICTION" & @CRLF & _
"NULL, --MUNICIPALITY" & @CRLF & _
"NULL, --LOCATION_ID" & @CRLF & _
"UPPER(ITGIS.MAKE_NSPD_MILEPOST_SHORT(TRIM(mp.PREFIX),TRIM(mp.SUFFIX),mp.MP)), --MILEPOST" & @CRLF & _
"UPPER(TRIM(mp.PREFIX)), --MP_PREFIX" & @CRLF & _
"mp.MP, --MP_NUMBER" & @CRLF & _
"UPPER(TRIM(mp.SUFFIX)), --MP_SUFFIX" & @CRLF & _
"UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS(mp.DIVCODE,TRIM(mp.PREFIX),TRIM(mp.SUFFIX),TRIM(mp.MP))), --NAME" & @CRLF & _
"NULL, --CROSSING_DOT_NUM" & @CRLF & _
"NULL, --CROSSING_FULL_NAME" & @CRLF & _
"NULL --TRACK_SEGMENT_RANGE" & @CRLF & _
"FROM ITGIS.ITGIS_MP100S mp" & @CRLF & _
"LEFT JOIN NFSAPP.CONTROL nfsac" & @CRLF & _
"ON" & @CRLF & _
"(" & @CRLF & _
"ITGIS.MAKE_LINEID ( mp.DIVCODE, TRIM ( mp.PREFIX ), TRIM ( mp.SUFFIX ) ) = nfsac.LINE_ID" & @CRLF & _
"AND mp.MP BETWEEN nfsac.LMP AND nfsac.HMP" & @CRLF & _
")" & @CRLF & _
"LEFT JOIN nrzMatches nrz" & @CRLF & _
"ON mp.OBJECTID = nrz.OBJECTID" & @CRLF & _
"LEFT JOIN nsdMatches nsd" & @CRLF & _
"ON mp.OBJECTID = nsd.OBJECTID" & @CRLF & _
"LEFT JOIN cityBoundaryMatches" & @CRLF & _
"ON mp.OBJECTID = cityBoundaryMatches.OBJECTID" & @CRLF & _
"LEFT JOIN countyMatches" & @CRLF & _
"ON mp.OBJECTID = countyMatches.OBJECTID" & @CRLF & _
"LEFT JOIN ITGIS.NSPD_LOCATIONS nl" & @CRLF & _
"ON nrz.BEAT_ID = nl.BEAT" & @CRLF & _
"LEFT JOIN ITGIS.NS_DIVISIONS nd" & @CRLF & _
"ON mp.DIVCODE = nd.DIVCODE" & @CRLF & _
"CROSS JOIN PUBDATA.US_CITIES c" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND MDSYS.SDO_NN ( c.SHAPE, mp.SHAPE, 'sdo_num_res = 1', 1 ) = 'TRUE'" & @CRLF & _
"--AND ROWNUM = 1" & @CRLF & _
"AND 1 = 1" & @CRLF & _
"]'" & @CRLF & _
"WHERE 1 = 1" & @CRLF & _
"AND XSCHEMA = 'ITGIS'" & @CRLF & _
"AND XTABLE = 'ITGIS_MP100S'" & @CRLF & _
"AND 1 = 1;" & @CRLF & _
"" & @CRLF & _
"-- /* template for any additional data */" & @CRLF & _
"-- UPDATE ITGIS.GLOBALSEARCHMASTER" & @CRLF & _
"-- SET QUERYTEXT =" & @CRLF & _
"-- q'[" & @CRLF & _
"" & @CRLF & _
"-- ]'" & @CRLF & _
"-- WHERE 1 = 1" & @CRLF & _
"-- AND XSCHEMA = ''" & @CRLF & _
"-- AND XTABLE = ''" & @CRLF & _
"-- AND 1 = 1;" & @CRLF & _
"" & @CRLF & _
"--------------------------------------------------------" & @CRLF & _
"-- CONTROLS WHICH TABLE GETS USED IN INSERT/SELECT" & @CRLF & _
"--------------------------------------------------------" & @CRLF & _
"" & @CRLF & _
"INSERT" & @CRLF & _
"INTO ITGIS.GLOBALSEARCHSWITCH" & @CRLF & _
" (" & @CRLF & _
" TABLESWITCH," & @CRLF & _
" PROCEDUREENABLED" & @CRLF & _
" )" & @CRLF & _
" VALUES" & @CRLF & _
" ( " & @CRLF & _
" -1," & @CRLF & _
" 'Y'" & @CRLF & _
" ) ;" & @CRLF & _
"" & @CRLF & _
"INSERT" & @CRLF & _
"INTO ITGIS.GLOBALSEARCHTABLE" & @CRLF & _
" (" & @CRLF & _
" TABLESWITCH," & @CRLF & _
" GLOBALSEARCHTABLE" & @CRLF & _
" )" & @CRLF & _
" VALUES" & @CRLF & _
" ( " & @CRLF & _
" -1," & @CRLF & _
" 'GLOBALSEARCHDATA_A'" & @CRLF & _
" ) ;" & @CRLF & _
"" & @CRLF & _
"INSERT" & @CRLF & _
"INTO ITGIS.GLOBALSEARCHTABLE" & @CRLF & _
" (" & @CRLF & _
" TABLESWITCH," & @CRLF & _
" GLOBALSEARCHTABLE" & @CRLF & _
" )" & @CRLF & _
" VALUES" & @CRLF & _
" ( " & @CRLF & _
" 1," & @CRLF & _
" 'GLOBALSEARCHDATA_B'" & @CRLF & _
" ) ;" & @CRLF & _
"" & @CRLF & _
"--------------------------------------------------------" & @CRLF & _
"-- COMMIT" & @CRLF & _
"--------------------------------------------------------" & @CRLF & _
"" & @CRLF & _
"COMMIT;" & @CRLF & _
"" & @CRLF & _
"--------------------------------------------------------" & @CRLF & _
"-- EXECUTE PROCEDURE TO GENERATE DATA" & @CRLF & _
"--------------------------------------------------------" & @CRLF & _
"" & @CRLF & _
"--EXECUTE NSPD.FILLGLOBALSEARCHTABLE_PR;" & @CRLF & _
""
Local $aArray = StringRegExp($sString, $sRegex, $STR_REGEXPARRAYGLOBALFULLMATCH)
Local $aFullArray[0]
For $i = 0 To UBound($aArray) -1
_ArrayConcatenate($aFullArray, $aArray[$i])
Next
$aArray = $aFullArray
; Present the entire match result
_ArrayDisplay($aArray, "Result")
Please keep in mind that these code samples are automatically generated and are not guaranteed to work. If you find any syntax errors, feel free to submit a bug report. For a full regex reference for AutoIt, please visit: https://www.autoitscript.com/autoit3/docs/functions/StringRegExp.htm