$re = '/(LEFT|RIGHT|CROSS)\s{0,}(INNER|OUTER){0,}\s{0,}JOIN.{0,}$|FROM.{0,}$/m';
$str = '--------------------------------------------------------
-- INSERT PLACEHOLDER RECORDS (fill in query later)
--------------------------------------------------------
INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES (\'OPERATING STATION\', \'ITGIS\', \'COMP_OP_STN\', \'OBJECTID\', \'SHAPE\', \'POINT\', \'OK \', \'Y\');
INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES (\'CROSSING\', \'ENGXING\', \'CI_LOCATIONANDCLASSIFICATION\', \'ASSET_PK\', \'SHAPE\', \'POINT\', \'OK \', \'Y\');
INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES (\'MILEPOST\', \'EGIS\', \'ENG_MPMARK\', \'OBJECTID\', \'SHAPE\', \'POINT\', \'OK \', \'Y\');
INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES (\'SWITCH\', \'EGIS\', \'ENG_SWITCH\', \'OBJECTID\', \'SHAPE\', \'POINT\', \'OK \', \'Y\');
INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES (\'TOWER\', \'EGIS\', \'ENG_TOWER\', \'OBJECTID\', \'SHAPE\', \'POINT\', \'OK \', \'Y\');
INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES (\'AEI SCANNER\', \'ITGIS\', \'COMP_AEI_SCNR\', \'OBJECTID\', \'SHAPE\', \'POINT\', \'OK \', \'Y\');
INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES (\'NSPD LOCATION\', \'ITGIS\', \'NSPD_LOCATIONS\', \'OBJECTID\', \'SHAPE\', \'POINT\', \'OK \', \'Y\');
INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES (\'NSPD POI\', \'NSPD\', \'NSPD_POI\', \'ID\', \'SHAPE\', \'POINT\', \'OK \', \'Y\');
INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES (\'MILEPOST 100S\', \'ITGIS\', \'ITGIS_MP100S\', \'OBJECTID\', \'SHAPE\', \'POINT\', \'OK \', \'Y\');
--------------------------------------------------------
-- UPDATE ACTUAL QUERIES
--------------------------------------------------------
/* ITGIS.COMP_OP_STN */
UPDATE ITGIS.GLOBALSEARCHMASTER
SET QUERYTEXT =
q\'[
WITH nsdMatches AS
(
SELECT cos.OBJECTID,
nsd.DIVCODE,
nsd.DIVDESCR,
nsd.DIVABBR,
nsd.DIVNAME
FROM ITGIS.COMP_OP_STN cos
CROSS JOIN ITGIS.NS_DIVISIONS nsd
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( cos.SHAPE, nsd.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
nrzMatches AS
(
SELECT cos.OBJECTID,
nrz.BEAT_ID,
nrz.AREA_ID,
nrz.FIELD_OFF,
nrz.COMMON_NAME NSPD_RESP_ZONE
FROM ITGIS.COMP_OP_STN cos
CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( cos.SHAPE, nrz.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
countyMatches AS
(
SELECT cos.OBJECTID,
usc.STATE,
usc.COUNTY
FROM ITGIS.COMP_OP_STN cos
CROSS JOIN PUBDATA.US_COUNTIES usc
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( usc.SHAPE, cos.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
cityBoundaryMatches AS
(
SELECT cos.OBJECTID,
uscb.NAME CITY
FROM ITGIS.COMP_OP_STN cos
CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( uscb.SHAPE, cos.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
)
SELECT
***MASTERID***, --MASTERID
cos.OBJECTID, --XUID
UPPER(cos.TCNTRL_STN_NAME), --SEARCHTEXT
UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(cos.PREFIX), TRIM(cos.SUFFIX), cos.MP, nfsac.DISTRICT, nfsac.BRANCH)), --DISPLAYTEXT
SDO_CS.MAKE_2D ( cos.SHAPE, 4326 ), --POINT/LINE/POLYGON
ROUND ( cos.SHAPE.SDO_POINT.y, 6 ), --LATITUDE
ROUND ( cos.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE
nrz.BEAT_ID, --BEAT
UPPER(cos.POSTAL_CD), --ZIPCODE
UPPER(COALESCE(cityBoundaryMatches.CITY,c.NAME)), --CITY
UPPER(countyMatches.COUNTY), --COUNTY
UPPER(COALESCE(CAST(cos.ST_ABBR AS NVARCHAR2(2)),CAST(countyMatches.STATE AS NVARCHAR2(2)))), --STATE
UPPER(COALESCE(nd.DIVNAME,nsd.DIVNAME)), --DIVISION
UPPER(nrz.FIELD_OFF), --FIELD_OFFICE
nrz.AREA_ID, --AREA
NULL, --JURISDICTION
NULL, --MUNICIPALITY
NULL, --LOCATION_ID
UPPER(ITGIS.MAKE_NSPD_MILEPOST_SHORT(TRIM(cos.PREFIX),TRIM(cos.SUFFIX),cos.MP)), --MILEPOST
UPPER(TRIM(cos.PREFIX)), --MP_PREFIX
cos.MP, --MP_NUMBER
UPPER(TRIM(cos.SUFFIX)), --MP_SUFFIX
UPPER(cos.TCNTRL_STN_NAME), --NAME
NULL, --CROSSING_DOT_NUM
NULL, --CROSSING_FULL_NAME
NULL --TRACK_SEGMENT_RANGE
FROM ITGIS.COMP_OP_STN cos
LEFT JOIN nsdMatches nsd
ON cos.OBJECTID = nsd.OBJECTID
LEFT JOIN nrzMatches nrz
ON cos.OBJECTID = nrz.OBJECTID
LEFT JOIN cityBoundaryMatches
ON cos.OBJECTID = cityBoundaryMatches.OBJECTID
LEFT JOIN countyMatches
ON cos.OBJECTID = countyMatches.OBJECTID
LEFT JOIN ITGIS.NSPD_LOCATIONS nl
ON nrz.BEAT_ID = nl.BEAT
LEFT JOIN ITGIS.NS_DIVISIONS nd
ON cos.DIVCODE = nd.DIVCODE
LEFT JOIN NFSAPP.CONTROL nfsac
ON(ITGIS.MAKE_LINEID(cos.DIVCODE, cos.PREFIX, cos.SUFFIX) = nfsac.LINE_ID AND cos.MP BETWEEN nfsac.LMP AND nfsac.HMP)
CROSS JOIN PUBDATA.US_CITIES c
WHERE 1 = 1
AND MDSYS.SDO_NN ( c.SHAPE, cos.SHAPE, \'sdo_num_res = 1\', 1 ) = \'TRUE\'
--AND ROWNUM = 1
AND 1 = 1
]\'
WHERE 1 = 1
AND XSCHEMA = \'ITGIS\'
AND XTABLE = \'COMP_OP_STN\'
AND 1 = 1;
/* ENGXING.DOTLIST */
UPDATE ITGIS.GLOBALSEARCHMASTER
SET QUERYTEXT = TO_CLOB(q\'[
WITH nsdMatches AS
(
SELECT cils.CROSSINGID,
nsd.DIVCODE,
nsd.DIVDESCR,
nsd.DIVABBR,
nsd.DIVNAME
FROM ENGXING.CI_LOCATIONANDCLASSIFICATION cils
CROSS JOIN ITGIS.NS_DIVISIONS nsd
WHERE 1 = 1
AND MDSYS.SDO_RELATE(cils.SHAPE, nsd.SHAPE, \'mask = anyinteract\') = \'TRUE\'
AND 1 = 1
),
nrzMatches AS
(
SELECT cils.CROSSINGID,
nrz.BEAT_ID,
nrz.AREA_ID,
nrz.FIELD_OFF,
nrz.COMMON_NAME NSPD_RESP_ZONE
FROM ENGXING.CI_LOCATIONANDCLASSIFICATION cils
CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz
WHERE 1 = 1
AND MDSYS.SDO_RELATE(cils.SHAPE, nrz.SHAPE, \'mask = anyinteract\') = \'TRUE\'
AND 1 = 1
),
countyMatches AS
(
SELECT cils.CROSSINGID,
usc.STATE,
usc.COUNTY
FROM ENGXING.CI_LOCATIONANDCLASSIFICATION cils
CROSS JOIN PUBDATA.US_COUNTIES usc
WHERE 1 = 1
AND MDSYS.SDO_RELATE(usc.SHAPE, cils.SHAPE, \'mask = anyinteract\') = \'TRUE\'
AND 1 = 1
),
cityBoundaryMatches AS
(
SELECT cils.CROSSINGID,
uscb.NAME CITY
FROM ENGXING.CI_LOCATIONANDCLASSIFICATION cils
CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb
WHERE 1 = 1
AND MDSYS.SDO_RELATE(uscb.SHAPE, cils.SHAPE, \'mask = anyinteract\') = \'TRUE\'
AND 1 = 1
)
SELECT
***MASTERID***, --MASTERID
cils.ASSET_PK,
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
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
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
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
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
nrz.BEAT_ID, --BEAT
NULL, --ZIPCODE
CASE WHEN q.INVDATE IS NOT NULL THEN UPPER(CAST(cils.CITYNAME AS NVARCHAR2(100))) ELSE UPPER(COALESCE(cityBoundaryMatches.CITY, c.NAME)) END, --CITY
CASE WHEN q.INVDATE IS NOT NULL THEN UPPER(CAST(cils.COUNTYNAME AS NVARCHAR2(50))) ELSE UPPER(countyMatches.COUNTY) END, --COUNTY
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
CASE WHEN q.INVDATE IS NOT NULL THEN UPPER(CAST(cior.RRDIV AS NVARCHAR2(50))) ELSE UPPER(COALESCE(nd.DIVNAME, nsd.DIVNAME)) END, --DIVISION
UPPER(nrz.FIELD_OFF), --FIELD_OFFICE
nrz.AREA_ID, --AREA]\') || TO_CLOB(q\'[
NULL, --JURISDICTION
NULL, --MUNICIPALITY
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
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
CASE WHEN q.INVDATE IS NOT NULL THEN TRIM(cior.PRFXMILEPOST) ELSE TRIM(ec.PREFIX) END, --MP_PREFIX
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
CASE WHEN q.INVDATE IS NOT NULL THEN TRIM(cior.SFXMILEPOST) ELSE TRIM(ec.SUFFIX) END, --MP_SUFFIX
CASE WHEN q.INVDATE IS NOT NULL THEN UPPER(cils.STREET || \' (\' || cils.HIGHWAY || \')\') ELSE UPPER(ec.STREETNAME) END, --NAME
UPPER(dl.DOTNUM), --CROSSING_DOT_NUM
NULL, --CROSSING_FULL_NAME
NULL --TRACK_SEGMENT_RANGE
FROM ENGXING.DOTLIST dl
INNER JOIN ENGXING.CI_LOCATIONANDCLASSIFICATION cils
ON dl.DOTNUM = cils.CROSSINGID
LEFT JOIN ENGXING.INVENTORY_QUE q
ON dl.DOTNUM = q.DOTNUM
LEFT JOIN nsdMatches nsd
ON dl.DOTNUM = nsd.CROSSINGID
LEFT JOIN ENGXING.CI_OPERATINGRAILROAD cior
ON dl.DOTNUM = cior.CROSSINGID
LEFT JOIN nrzMatches nrz
ON cils.CROSSINGID = nrz.CROSSINGID
LEFT JOIN cityBoundaryMatches
ON cils.CROSSINGID = cityBoundaryMatches.CROSSINGID
LEFT JOIN countyMatches
ON cils.CROSSINGID = countyMatches.CROSSINGID
LEFT JOIN ITGIS.NSPD_LOCATIONS nl
ON nrz.BEAT_ID = nl.BEAT
LEFT JOIN ITGIS.NS_DIVISIONS nd
ON cior.DIVCODE = nd.DIVCODE
LEFT JOIN ENG.ENG_CROSSING ec
ON(dl.DOTNUM = ec.DOTNUM AND ec.SUBLOC = \'C\')
LEFT JOIN NFSAPP.CONTROL nfsac
ON(ITGIS.MAKE_LINEID(ec.DIVCODE, ec.PREFIX, ec.SUFFIX) = nfsac.LINE_ID AND ec.MP BETWEEN nfsac.LMP AND nfsac.HMP)
CROSS JOIN PUBDATA.US_CITIES c
WHERE 1 = 1
AND MDSYS.SDO_NN(c.SHAPE, cils.SHAPE, \'sdo_num_res = 1\', 1) = \'TRUE\'
--AND ROWNUM = 1
AND 1 = 1
]\')
WHERE 1 = 1
AND XSCHEMA = \'ENGXING\'
AND XTABLE = \'CI_LOCATIONANDCLASSIFICATION\'
AND 1 = 1;
/* EGIS.ENG_MPMARK */
UPDATE ITGIS.GLOBALSEARCHMASTER
SET QUERYTEXT =
q\'[
WITH nsdMatches AS
(
SELECT mp.OBJECTID,
nsd.DIVCODE,
nsd.DIVDESCR,
nsd.DIVABBR,
nsd.DIVNAME
FROM EGIS.ENG_MPMARK mp
CROSS JOIN ITGIS.NS_DIVISIONS nsd
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( mp.SHAPE, nsd.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
nrzMatches AS
(
SELECT mp.OBJECTID,
nrz.BEAT_ID,
nrz.AREA_ID,
nrz.FIELD_OFF,
nrz.COMMON_NAME NSPD_RESP_ZONE
FROM EGIS.ENG_MPMARK mp
CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( mp.SHAPE, nrz.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
countyMatches AS
(
SELECT mp.OBJECTID,
usc.STATE,
usc.COUNTY
FROM EGIS.ENG_MPMARK mp
CROSS JOIN PUBDATA.US_COUNTIES usc
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( usc.SHAPE, mp.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
cityBoundaryMatches AS
(
SELECT mp.OBJECTID,
uscb.NAME CITY
FROM EGIS.ENG_MPMARK mp
CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( uscb.SHAPE, mp.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
)
SELECT
***MASTERID***, --MASTERID
mp.OBJECTID, --XUID
UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(mp.PREFIX), TRIM(mp.SUFFIX), mp.MPMARKER, nfsac.DISTRICT, nfsac.BRANCH)), --SEARCHTEXT
UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(mp.PREFIX), TRIM(mp.SUFFIX), mp.MPMARKER, nfsac.DISTRICT, nfsac.BRANCH)), --DISPLAYTEXT
SDO_CS.MAKE_2D ( mp.SHAPE, 4326 ), --POINT/LINE/POLYGON
ROUND ( mp.SHAPE.SDO_POINT.y, 6 ), --LATITUDE
ROUND ( mp.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE
nrz.BEAT_ID, --BEAT
NULL, --ZIPCODE
UPPER(COALESCE(cityBoundaryMatches.CITY,c.NAME)), --CITY
UPPER(countyMatches.COUNTY), --COUNTY
UPPER(countyMatches.STATE), --STATE
UPPER(COALESCE(nd.DIVNAME,nsd.DIVNAME)), --DIVISION
UPPER(nrz.FIELD_OFF), --FIELD_OFFICE
nrz.AREA_ID, --AREA
UPPER(nl.OFFICEDESCRIPTION), --JURISDICTION
UPPER(COALESCE(cityBoundaryMatches.CITY,c.NAME)), --MUNICIPALITY
NULL, --LOCATION_ID
UPPER(ITGIS.MAKE_NSPD_MILEPOST_SHORT ( TRIM(mp.PREFIX), TRIM(mp.SUFFIX), mp.MPMARKER )), --MILEPOST
UPPER(TRIM(mp.PREFIX)), --MP_PREFIX
mp.MPMARKER, --MP_NUMBER
UPPER(TRIM(mp.SUFFIX)), --MP_SUFFIX
UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(mp.PREFIX), TRIM(mp.SUFFIX), mp.MPMARKER, nfsac.DISTRICT, nfsac.BRANCH)), --NAME
NULL, --CROSSING_DOT_NUM
NULL, --CROSSING_FULL_NAME
NULL --TRACK_SEGMENT_RANGE
FROM EGIS.ENG_MPMARK mp
LEFT JOIN nrzMatches nrz
ON mp.OBJECTID = nrz.OBJECTID
LEFT JOIN nsdMatches nsd
ON mp.OBJECTID = nsd.OBJECTID
LEFT JOIN cityBoundaryMatches
ON mp.OBJECTID = cityBoundaryMatches.OBJECTID
LEFT JOIN countyMatches
ON mp.OBJECTID = countyMatches.OBJECTID
LEFT JOIN ITGIS.NSPD_LOCATIONS nl
ON nrz.BEAT_ID = nl.BEAT
LEFT JOIN ITGIS.NS_DIVISIONS nd
ON mp.DIVCODE = nd.DIVCODE
LEFT JOIN NFSAPP.CONTROL nfsac
ON(ITGIS.MAKE_LINEID(mp.DIVCODE, mp.PREFIX, mp.SUFFIX) = nfsac.LINE_ID AND mp.MPMARKER BETWEEN nfsac.LMP AND nfsac.HMP)
CROSS JOIN PUBDATA.US_CITIES c
WHERE 1 = 1
AND MDSYS.SDO_NN ( c.SHAPE, mp.SHAPE, \'sdo_num_res = 1\', 1 ) = \'TRUE\'
--AND ROWNUM = 1
AND 1 = 1
]\'
WHERE 1 = 1
AND XSCHEMA = \'EGIS\'
AND XTABLE = \'ENG_MPMARK\'
AND 1 = 1;
/* EGIS.ENG_SWITCH */
UPDATE ITGIS.GLOBALSEARCHMASTER
SET QUERYTEXT =
q\'[
WITH nsdMatches AS
(
SELECT s.OBJECTID,
nsd.DIVCODE,
nsd.DIVDESCR,
nsd.DIVABBR,
nsd.DIVNAME
FROM EGIS.ENG_SWITCH s
CROSS JOIN ITGIS.NS_DIVISIONS nsd
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( s.SHAPE, nsd.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
nrzMatches AS
(
SELECT s.OBJECTID,
nrz.BEAT_ID,
nrz.AREA_ID,
nrz.FIELD_OFF,
nrz.COMMON_NAME NSPD_RESP_ZONE
FROM EGIS.ENG_SWITCH s
CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( s.SHAPE, nrz.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
countyMatches AS
(
SELECT s.OBJECTID,
usc.STATE,
usc.COUNTY
FROM EGIS.ENG_SWITCH s
CROSS JOIN PUBDATA.US_COUNTIES usc
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( usc.SHAPE, s.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
cityBoundaryMatches AS
(
SELECT s.OBJECTID,
uscb.NAME CITY
FROM EGIS.ENG_SWITCH s
CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( uscb.SHAPE, s.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
)
SELECT
***MASTERID***, --MASTERID
s.OBJECTID, --XUID
UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(s.PREFIX), TRIM(s.SUFFIX), s.MP, nfsac.DISTRICT, nfsac.BRANCH)), --SEARCHTEXT
UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(s.PREFIX), TRIM(s.SUFFIX), s.MP, nfsac.DISTRICT, nfsac.BRANCH)), --DISPLAYTEXT
SDO_CS.MAKE_2D ( s.SHAPE, 4326 ), --POINT/LINE/POLYGON
ROUND ( s.SHAPE.SDO_POINT.y, 6 ), --LATITUDE
ROUND ( s.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE
nrz.BEAT_ID, --BEAT
NULL, --ZIPCODE
UPPER(COALESCE(cityBoundaryMatches.CITY,c.NAME)), --CITY
UPPER(countyMatches.COUNTY), --COUNTY
UPPER(countyMatches.STATE), --STATE
UPPER(COALESCE(nd.DIVNAME,nsd.DIVNAME)), --DIVISION
UPPER(nrz.FIELD_OFF), --FIELD_OFFICE
nrz.AREA_ID, --AREA
NULL, --JURISDICTION
NULL, --MUNICIPALITY
NULL, --LOCATION_ID
UPPER(ITGIS.MAKE_NSPD_MILEPOST_SHORT(TRIM(s.PREFIX), TRIM(s.SUFFIX), s.MP)), --MILEPOST
UPPER(TRIM(s.PREFIX)), --MP_PREFIX
s.MP, --MP_NUMBER
UPPER(TRIM(s.SUFFIX)), --MP_SUFFIX
UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(s.PREFIX), TRIM(s.SUFFIX), s.MP, nfsac.DISTRICT, nfsac.BRANCH)), --NAME
NULL, --CROSSING_DOT_NUM
NULL, --CROSSING_FULL_NAME
NULL --TRACK_SEGMENT_RANGE
FROM EGIS.ENG_SWITCH s
LEFT JOIN nsdMatches nsd
ON s.OBJECTID = nsd.OBJECTID
LEFT JOIN nrzMatches nrz
ON s.OBJECTID = nrz.OBJECTID
LEFT JOIN cityBoundaryMatches
ON s.OBJECTID = cityBoundaryMatches.OBJECTID
LEFT JOIN countyMatches
ON s.OBJECTID = countyMatches.OBJECTID
LEFT JOIN ITGIS.NSPD_LOCATIONS nl
ON nrz.BEAT_ID = nl.BEAT
LEFT JOIN ITGIS.NS_DIVISIONS nd
ON s.DIVCODE = nd.DIVCODE
LEFT JOIN NFSAPP.CONTROL nfsac
ON(ITGIS.MAKE_LINEID(s.DIVCODE, s.PREFIX, s.SUFFIX) = nfsac.LINE_ID AND s.MP BETWEEN nfsac.LMP AND nfsac.HMP)
CROSS JOIN PUBDATA.US_CITIES c
WHERE 1 = 1
AND MDSYS.SDO_NN ( c.SHAPE, s.SHAPE, \'sdo_num_res = 1\', 1 ) = \'TRUE\'
--AND ROWNUM = 1
AND 1 = 1
]\'
WHERE 1 = 1
AND XSCHEMA = \'EGIS\'
AND XTABLE = \'ENG_SWITCH\'
AND 1 = 1;
-- /* EGIS.ENG_TOWER */
UPDATE ITGIS.GLOBALSEARCHMASTER
SET QUERYTEXT =
q\'[
WITH nsdMatches AS
(
SELECT t.OBJECTID,
nsd.DIVCODE,
nsd.DIVDESCR,
nsd.DIVABBR,
nsd.DIVNAME
FROM EGIS.ENG_TOWER t
CROSS JOIN ITGIS.NS_DIVISIONS nsd
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( t.SHAPE, nsd.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
nrzMatches AS
(
SELECT t.OBJECTID,
nrz.BEAT_ID,
nrz.AREA_ID,
nrz.FIELD_OFF,
nrz.COMMON_NAME NSPD_RESP_ZONE
FROM EGIS.ENG_TOWER t
CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( t.SHAPE, nrz.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
countyMatches AS
(
SELECT t.OBJECTID,
usc.STATE,
usc.COUNTY
FROM EGIS.ENG_TOWER t
CROSS JOIN PUBDATA.US_COUNTIES usc
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( usc.SHAPE, t.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
cityBoundaryMatches AS
(
SELECT t.OBJECTID,
uscb.NAME CITY
FROM EGIS.ENG_TOWER t
CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( uscb.SHAPE, t.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
)
SELECT
***MASTERID***, --MASTERID
t.OBJECTID, --XUID
UPPER(t.SITENAME || \' \' || ITGIS.MAKE_NSPD_MP_DB_LINEID(t.LINEID, REGEXP_SUBSTR(t.MILEPOSTCONCAT, \'\\d{1,}\\.{0,}\\d{0,}\'))), --SEARCHTEXT
UPPER(ITGIS.MAKE_NSPD_MP_DB_LINEID(t.LINEID, REGEXP_SUBSTR(t.MILEPOSTCONCAT, \'\\d{1,}\\.{0,}\\d{0,}\'))), --DISPLAYTEXT
SDO_CS.MAKE_2D ( t.SHAPE, 4326 ), --POINT/LINE/POLYGON
ROUND ( t.SHAPE.SDO_POINT.y, 6 ), --LATITUDE
ROUND ( t.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE
nrz.BEAT_ID, --BEAT
NULL, --ZIPCODE
UPPER(COALESCE(cityBoundaryMatches.CITY,c.NAME)), --CITY
UPPER(countyMatches.COUNTY), --COUNTY
UPPER(countyMatches.STATE), --STATE
UPPER(COALESCE(nd.DIVNAME,nsd.DIVNAME)), --DIVISION
UPPER(nrz.FIELD_OFF), --FIELD_OFFICE
nrz.AREA_ID, --AREA
NULL, --JURISDICTION
NULL, --MUNICIPALITY
NULL, --LOCATION_ID
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
UPPER(ITGIS.MAKE_PREFIX_LINEID(t.LINEID)), --MP_PREFIX
REGEXP_SUBSTR(t.MILEPOSTCONCAT, \'\\d{1,}\\.{0,}\\d{0,}\'), --MP_NUMBER
UPPER(ITGIS.MAKE_SUFFIX_LINEID(t.LINEID)), --MP_SUFFIX
UPPER(t.SITENAME), --NAME
NULL, --CROSSING_DOT_NUM
NULL, --CROSSING_FULL_NAME
NULL --TRACK_SEGMENT_RANGE
FROM EGIS.ENG_TOWER t
LEFT JOIN nsdMatches nsd
ON t.OBJECTID = nsd.OBJECTID
LEFT JOIN nrzMatches nrz
ON t.OBJECTID = nrz.OBJECTID
LEFT JOIN cityBoundaryMatches
ON t.OBJECTID = cityBoundaryMatches.OBJECTID
LEFT JOIN countyMatches
ON t.OBJECTID = countyMatches.OBJECTID
LEFT JOIN ITGIS.NSPD_LOCATIONS nl
ON nrz.BEAT_ID = nl.BEAT
LEFT JOIN ITGIS.NS_DIVISIONS nd
ON SUBSTR ( t.LINEID, 0, 2 ) = nd.DIVCODE
CROSS JOIN PUBDATA.US_CITIES c
WHERE 1 = 1
AND MDSYS.SDO_NN ( c.SHAPE, t.SHAPE, \'sdo_num_res = 1\', 1 ) = \'TRUE\'
--AND ROWNUM = 1
AND 1 = 1
]\'
WHERE 1 = 1
AND XSCHEMA = \'EGIS\'
AND XTABLE = \'ENG_TOWER\'
AND 1 = 1;
/* ITGIS.COMP_AEI_SCNR */
UPDATE ITGIS.GLOBALSEARCHMASTER
SET QUERYTEXT =
q\'[
WITH nsdMatches AS
(
SELECT s.OBJECTID,
nsd.DIVCODE,
nsd.DIVDESCR,
nsd.DIVABBR,
nsd.DIVNAME
FROM ITGIS.COMP_AEI_SCNR s
CROSS JOIN ITGIS.NS_DIVISIONS nsd
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( s.SHAPE, nsd.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
nrzMatches AS
(
SELECT s.OBJECTID,
nrz.BEAT_ID,
nrz.AREA_ID,
nrz.FIELD_OFF,
nrz.COMMON_NAME NSPD_RESP_ZONE
FROM ITGIS.COMP_AEI_SCNR s
CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( s.SHAPE, nrz.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
countyMatches AS
(
SELECT s.OBJECTID,
usc.STATE,
usc.COUNTY
FROM ITGIS.COMP_AEI_SCNR s
CROSS JOIN PUBDATA.US_COUNTIES usc
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( usc.SHAPE, s.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
cityBoundaryMatches AS
(
SELECT s.OBJECTID,
uscb.NAME CITY
FROM ITGIS.COMP_AEI_SCNR s
CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( uscb.SHAPE, s.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
)
SELECT
***MASTERID***, --MASTERID
s.OBJECTID, --XUID
UPPER(s.NAME || \' \' || ITGIS.MAKE_NSPD_MILEPOST_SHORT(TRIM(s.PREFIX),TRIM(s.SUFFIX),s.MP)), --SEARCHTEXT
UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(s.PREFIX), TRIM(s.SUFFIX), s.MP, nfsac.DISTRICT, nfsac.BRANCH)), --DISPLAYTEXT
SDO_CS.MAKE_2D ( s.SHAPE, 4326 ), --POINT/LINE/POLYGON
ROUND ( s.SHAPE.SDO_POINT.y, 6 ), --LATITUDE
ROUND ( s.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE
nrz.BEAT_ID, --BEAT
UPPER(s.POSTAL_CD), --ZIPCODE
UPPER(COALESCE(cityBoundaryMatches.CITY,c.NAME)), --CITY
UPPER(countyMatches.COUNTY), --COUNTY
UPPER(countyMatches.STATE), --STATE
UPPER(COALESCE(nd.DIVNAME,nsd.DIVNAME)), --DIVISION
UPPER(nrz.FIELD_OFF), --FIELD_OFFICE
nrz.AREA_ID, --AREA
NULL, --JURISDICTION
NULL, --MUNICIPALITY
NULL, --LOCATION_ID
UPPER(ITGIS.MAKE_NSPD_MILEPOST_SHORT(TRIM(s.PREFIX),TRIM(s.SUFFIX),s.MP)), --MILEPOST
UPPER(TRIM(s.PREFIX)), --MP_PREFIX
s.MP, --MP_NUMBER
UPPER(TRIM(s.SUFFIX)), --MP_SUFFIX
UPPER(s.NAME), --NAME
NULL, --CROSSING_DOT_NUM
NULL, --CROSSING_FULL_NAME
NULL --TRACK_SEGMENT_RANGE
FROM ITGIS.COMP_AEI_SCNR s
LEFT JOIN nsdMatches nsd
ON s.OBJECTID = nsd.OBJECTID
LEFT JOIN nrzMatches nrz
ON s.OBJECTID = nrz.OBJECTID
LEFT JOIN cityBoundaryMatches
ON s.OBJECTID = cityBoundaryMatches.OBJECTID
LEFT JOIN countyMatches
ON s.OBJECTID = countyMatches.OBJECTID
LEFT JOIN ITGIS.NSPD_LOCATIONS nl
ON nrz.BEAT_ID = nl.BEAT
LEFT JOIN ITGIS.NS_DIVISIONS nd
ON s.DIVCODE = nd.DIVCODE
LEFT JOIN NFSAPP.CONTROL nfsac
ON(ITGIS.MAKE_LINEID(s.DIVCODE, s.PREFIX, s.SUFFIX) = nfsac.LINE_ID AND s.MP BETWEEN nfsac.LMP AND nfsac.HMP)
CROSS JOIN PUBDATA.US_CITIES c
WHERE 1 = 1
AND MDSYS.SDO_NN ( c.SHAPE, s.SHAPE, \'sdo_num_res = 1\', 1 ) = \'TRUE\'
--AND ROWNUM = 1
AND 1 = 1
]\'
WHERE 1 = 1
AND XSCHEMA = \'ITGIS\'
AND XTABLE = \'COMP_AEI_SCNR\'
AND 1 = 1;
/* ITGIS.NSPD_LOCATIONS*/
UPDATE ITGIS.GLOBALSEARCHMASTER
SET QUERYTEXT =
q\'[
WITH nsdMatches AS
(
SELECT l.OBJECTID,
nsd.DIVCODE,
nsd.DIVDESCR,
nsd.DIVABBR,
nsd.DIVNAME
FROM ITGIS.NSPD_LOCATIONS l
CROSS JOIN ITGIS.NS_DIVISIONS nsd
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( l.SHAPE, nsd.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
nrzMatches AS
(
SELECT l.OBJECTID,
nrz.BEAT_ID,
nrz.AREA_ID,
nrz.FIELD_OFF,
nrz.COMMON_NAME NSPD_RESP_ZONE
FROM ITGIS.NSPD_LOCATIONS l
CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( l.SHAPE, nrz.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
countyMatches AS
(
SELECT l.OBJECTID,
usc.STATE,
usc.COUNTY
FROM ITGIS.NSPD_LOCATIONS l
CROSS JOIN PUBDATA.US_COUNTIES usc
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( usc.SHAPE, l.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
cityBoundaryMatches AS
(
SELECT l.OBJECTID,
uscb.NAME CITY
FROM ITGIS.NSPD_LOCATIONS l
CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( uscb.SHAPE, l.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
)
SELECT
***MASTERID***, --MASTERID
l.OBJECTID, --XUID
UPPER(l.OFFICEDESCRIPTION), --SEARCHTEXT
UPPER(l.ADDRESS), --DISPLAYTEXT
SDO_CS.MAKE_2D ( l.SHAPE, 4326 ), --POINT/LINE/POLYGON
ROUND ( l.SHAPE.SDO_POINT.y, 6 ), --LATITUDE
ROUND ( l.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE
nrz.BEAT_ID, --BEAT
UPPER(l.POSTALCODE), --ZIPCODE
UPPER(COALESCE(l.CITY,cityBoundaryMatches.CITY,c.NAME)), --CITY
UPPER(countyMatches.COUNTY), --COUNTY
UPPER(COALESCE(l.STATE,countyMatches.STATE)), --STATE
UPPER(nsd.DIVNAME), --DIVISION
UPPER(l.OFFICEDESCRIPTION), --FIELD_OFFICE
nrz.AREA_ID, --AREA
NULL, --JURISDICTION
NULL, --MUNICIPALITY
NULL, --LOCATION_ID
NULL, --MILEPOST
NULL, --MP_PREFIX
NULL, --MP_NUMBER
NULL, --MP_SUFFIX
UPPER(l.OFFICEDESCRIPTION), --NAME
NULL, --CROSSING_DOT_NUM
NULL, --CROSSING_FULL_NAME
NULL --TRACK_SEGMENT_RANGE
FROM ITGIS.NSPD_LOCATIONS l
LEFT JOIN nsdMatches nsd
ON l.OBJECTID = nsd.OBJECTID
LEFT JOIN nrzMatches nrz
ON l.OBJECTID = nrz.OBJECTID
LEFT JOIN cityBoundaryMatches
ON l.OBJECTID = cityBoundaryMatches.OBJECTID
LEFT JOIN countyMatches
ON l.OBJECTID = countyMatches.OBJECTID
LEFT JOIN ITGIS.NSPD_LOCATIONS nl
ON nrz.BEAT_ID = nl.BEAT
--NO DIVCODE FIELD TO JOIN TO
CROSS JOIN PUBDATA.US_CITIES c
WHERE 1 = 1
AND MDSYS.SDO_NN ( c.SHAPE, l.SHAPE, \'sdo_num_res = 1\', 1 ) = \'TRUE\'
--AND ROWNUM = 1
AND 1 = 1
]\'
WHERE 1 = 1
AND XSCHEMA = \'ITGIS\'
AND XTABLE = \'NSPD_LOCATIONS\'
AND 1 = 1;
/* NSPD.NSPD_POI */
UPDATE ITGIS.GLOBALSEARCHMASTER
SET QUERYTEXT =
q\'[
WITH nsdMatches AS
(
SELECT poi.ID,
nsd.DIVCODE,
nsd.DIVDESCR,
nsd.DIVABBR,
nsd.DIVNAME
FROM NSPD.NSPD_POI poi
CROSS JOIN ITGIS.NS_DIVISIONS nsd
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( poi.SHAPE, nsd.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
nrzMatches AS
(
SELECT poi.ID,
nrz.BEAT_ID,
nrz.AREA_ID,
nrz.FIELD_OFF,
nrz.COMMON_NAME NSPD_RESP_ZONE
FROM NSPD.NSPD_POI poi
CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( poi.SHAPE, nrz.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
countyMatches AS
(
SELECT poi.ID,
usc.STATE,
usc.COUNTY
FROM NSPD.NSPD_POI poi
CROSS JOIN PUBDATA.US_COUNTIES usc
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( usc.SHAPE, poi.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
cityBoundaryMatches AS
(
SELECT poi.ID,
uscb.NAME CITY
FROM NSPD.NSPD_POI poi
CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( uscb.SHAPE, poi.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
)
SELECT
***MASTERID***, --MASTERID
poi.ID, --XUID
UPPER( poi.NAME), --SEARCHTEXT
UPPER( poi.ADDRESS), --DISPLAYTEXT
SDO_CS.MAKE_2D ( poi.SHAPE, 4326 ), --POINT/LINE/POLYGON
ROUND ( poi.SHAPE.SDO_POINT.y, 6 ), --LATITUDE
ROUND ( poi.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE
nrz.BEAT_ID, --BEAT
NULL, --ZIPCODE
UPPER(COALESCE(CAST(poi.CITY AS NVARCHAR2(100)),cityBoundaryMatches.CITY,c.NAME)), --CITY
UPPER(COALESCE(CAST(poi.COUNTY AS NVARCHAR2(100)),countyMatches.COUNTY)), --COUNTY
UPPER(COALESCE(CAST(poi.STATE AS NVARCHAR2(100)),countyMatches.STATE)), --STATE
UPPER(nsd.DIVNAME), --DIVISION
UPPER(nrz.FIELD_OFF), --FIELD_OFFICE
nrz.AREA_ID, --AREA
NULL, --JURISDICTION
NULL, --MUNICIPALITY
NULL, --LOCATION_ID
NULL, --MILEPOST
NULL, --MP_PREFIX
NULL, --MP_NUMBER
NULL, --MP_SUFFIX
UPPER( poi.NAME), --NAME
NULL, --CROSSING_DOT_NUM
NULL, --CROSSING_FULL_NAME
NULL --TRACK_SEGMENT_RANGE
FROM NSPD.NSPD_POI poi
LEFT JOIN nsdMatches nsd
ON poi.ID = nsd.ID
LEFT JOIN nrzMatches nrz
ON poi.ID = nrz.ID
LEFT JOIN cityBoundaryMatches
ON poi.ID = cityBoundaryMatches.ID
LEFT JOIN countyMatches
ON poi.ID = countyMatches.ID
LEFT JOIN ITGIS.NSPD_LOCATIONS nl
ON nrz.BEAT_ID = nl.BEAT
--NO DIVCODE FIELD TO JOIN TO
CROSS JOIN PUBDATA.US_CITIES c
WHERE 1 = 1
AND MDSYS.SDO_NN ( c.SHAPE, poi.SHAPE, \'sdo_num_res = 1\', 1 ) = \'TRUE\'
--AND ROWNUM = 1
AND 1 = 1
]\'
WHERE 1 = 1
AND XSCHEMA = \'NSPD\'
AND XTABLE = \'NSPD_POI\'
AND 1 = 1;
/* ITGIS.ITGIS_MP100S */
UPDATE ITGIS.GLOBALSEARCHMASTER
SET QUERYTEXT =
q\'[
WITH nsdMatches AS
(
SELECT mp.OBJECTID,
nsd.DIVCODE,
nsd.DIVDESCR,
nsd.DIVABBR,
nsd.DIVNAME
FROM ITGIS.ITGIS_MP100S mp
CROSS JOIN ITGIS.NS_DIVISIONS nsd
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( mp.SHAPE, nsd.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
nrzMatches AS
(
SELECT mp.OBJECTID,
nrz.BEAT_ID,
nrz.AREA_ID,
nrz.FIELD_OFF,
nrz.COMMON_NAME NSPD_RESP_ZONE
FROM ITGIS.ITGIS_MP100S mp
CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( mp.SHAPE, nrz.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
countyMatches AS
(
SELECT mp.OBJECTID,
usc.STATE,
usc.COUNTY
FROM ITGIS.ITGIS_MP100S mp
CROSS JOIN PUBDATA.US_COUNTIES usc
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( usc.SHAPE, mp.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
),
cityBoundaryMatches AS
(
SELECT mp.OBJECTID,
uscb.NAME CITY
FROM ITGIS.ITGIS_MP100S mp
CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb
WHERE 1 = 1
AND MDSYS.SDO_RELATE ( uscb.SHAPE, mp.SHAPE, \'mask = anyinteract\' ) = \'TRUE\'
AND 1 = 1
)
SELECT
***MASTERID***, --MASTERID
mp.OBJECTID, --XUID
UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS(mp.DIVCODE,TRIM(mp.PREFIX),TRIM(mp.SUFFIX),TRIM(mp.MP))), --SEARCHTEXT
UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS(mp.DIVCODE,TRIM(mp.PREFIX),TRIM(mp.SUFFIX),TRIM(mp.MP))), --DISPLAYTEXT
SDO_CS.MAKE_2D ( mp.SHAPE, 4326 ), --POINT/LINE/POLYGON
ROUND ( mp.SHAPE.SDO_POINT.y, 6 ), --LATITUDE
ROUND ( mp.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE
nrz.BEAT_ID, --BEAT
NULL, --ZIPCODE
UPPER(COALESCE(cityBoundaryMatches.CITY,c.NAME)), --CITY
UPPER(countyMatches.COUNTY), --COUNTY
UPPER(countyMatches.STATE), --STATE
UPPER(COALESCE(nd.DIVNAME,nsd.DIVNAME)), --DIVISION
UPPER(nrz.FIELD_OFF), --FIELD_OFFICE
nrz.AREA_ID, --AREA
NULL, --JURISDICTION
NULL, --MUNICIPALITY
NULL, --LOCATION_ID
UPPER(ITGIS.MAKE_NSPD_MILEPOST_SHORT(TRIM(mp.PREFIX),TRIM(mp.SUFFIX),mp.MP)), --MILEPOST
UPPER(TRIM(mp.PREFIX)), --MP_PREFIX
mp.MP, --MP_NUMBER
UPPER(TRIM(mp.SUFFIX)), --MP_SUFFIX
UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS(mp.DIVCODE,TRIM(mp.PREFIX),TRIM(mp.SUFFIX),TRIM(mp.MP))), --NAME
NULL, --CROSSING_DOT_NUM
NULL, --CROSSING_FULL_NAME
NULL --TRACK_SEGMENT_RANGE
FROM ITGIS.ITGIS_MP100S mp
LEFT JOIN NFSAPP.CONTROL nfsac
ON
(
ITGIS.MAKE_LINEID ( mp.DIVCODE, TRIM ( mp.PREFIX ), TRIM ( mp.SUFFIX ) ) = nfsac.LINE_ID
AND mp.MP BETWEEN nfsac.LMP AND nfsac.HMP
)
LEFT JOIN nrzMatches nrz
ON mp.OBJECTID = nrz.OBJECTID
LEFT JOIN nsdMatches nsd
ON mp.OBJECTID = nsd.OBJECTID
LEFT JOIN cityBoundaryMatches
ON mp.OBJECTID = cityBoundaryMatches.OBJECTID
LEFT JOIN countyMatches
ON mp.OBJECTID = countyMatches.OBJECTID
LEFT JOIN ITGIS.NSPD_LOCATIONS nl
ON nrz.BEAT_ID = nl.BEAT
LEFT JOIN ITGIS.NS_DIVISIONS nd
ON mp.DIVCODE = nd.DIVCODE
CROSS JOIN PUBDATA.US_CITIES c
WHERE 1 = 1
AND MDSYS.SDO_NN ( c.SHAPE, mp.SHAPE, \'sdo_num_res = 1\', 1 ) = \'TRUE\'
--AND ROWNUM = 1
AND 1 = 1
]\'
WHERE 1 = 1
AND XSCHEMA = \'ITGIS\'
AND XTABLE = \'ITGIS_MP100S\'
AND 1 = 1;
-- /* template for any additional data */
-- UPDATE ITGIS.GLOBALSEARCHMASTER
-- SET QUERYTEXT =
-- q\'[
-- ]\'
-- WHERE 1 = 1
-- AND XSCHEMA = \'\'
-- AND XTABLE = \'\'
-- AND 1 = 1;
--------------------------------------------------------
-- CONTROLS WHICH TABLE GETS USED IN INSERT/SELECT
--------------------------------------------------------
INSERT
INTO ITGIS.GLOBALSEARCHSWITCH
(
TABLESWITCH,
PROCEDUREENABLED
)
VALUES
(
-1,
\'Y\'
) ;
INSERT
INTO ITGIS.GLOBALSEARCHTABLE
(
TABLESWITCH,
GLOBALSEARCHTABLE
)
VALUES
(
-1,
\'GLOBALSEARCHDATA_A\'
) ;
INSERT
INTO ITGIS.GLOBALSEARCHTABLE
(
TABLESWITCH,
GLOBALSEARCHTABLE
)
VALUES
(
1,
\'GLOBALSEARCHDATA_B\'
) ;
--------------------------------------------------------
-- COMMIT
--------------------------------------------------------
COMMIT;
--------------------------------------------------------
-- EXECUTE PROCEDURE TO GENERATE DATA
--------------------------------------------------------
--EXECUTE NSPD.FILLGLOBALSEARCHTABLE_PR;
';
preg_match_all($re, $str, $matches, PREG_SET_ORDER, 0);
// Print the entire match result
var_dump($matches);
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 PHP, please visit: http://php.net/manual/en/ref.pcre.php