# If you'd like to omit non-matching lines from the result; add ';d' to the end of the expression.
sed -E 's/(')(&[A-Za-z_]*)(')/$2/g;t' <<< "SELECT '&TRIGGER_VAL' AS TRIGGER_VAL,
DLYTRN.DLYTRN_ID AS TRANSACTION_ID,
DLYTRN.TRNDTE AS TRANSACTION_DATE,
DLYTRN.OPRCOD AS OPERATION_CODE,
DLYTRN.ACTCOD AS ACTIVITY_CODE,
DLYTRN.LODNUM AS LOAD_NUMBER,
DLYTRN.SUBNUM AS SUB_NUMBER,
DLYTRN.DTLNUM AS DETAIL_NUMBER,
DLYTRN.TO_LODNUM AS TO_LOAD_NUMBER,
DLYTRN.TO_SUBNUM AS TO_SUB_NUMBER,
DLYTRN.TO_DTLNUM AS TO_DETAIL_NUMBER,
DLYTRN.PRTNUM AS ITEM_NUMBER,
DLYTRN.PRT_CLIENT_ID AS PART_CLIENT_CODE,
DLYTRN.ORGCOD AS ORIGIN_CODE,
DLYTRN.REVLVL AS REVISION_LEVEL,
DLYTRN.LOTNUM AS LOT_NUMBER,
DLYTRN.SUPNUM AS SUPPLIER_NUMBER,
DLYTRN.MOVREF AS MOVE_REFERENCE,
DLYTRN.REACOD AS REASON_CODE,
DLYTRN.TRNQTY AS TRANSACTION_QUANTITY,
DLYTRN.FR_ARECOD AS FROM_AREA,
DLYTRN.FRSTOL AS FROM_LOCATION,
DLYTRN.FRINVS AS FROM_INVENTORY_STATUS,
DLYTRN.TO_ARECOD AS TO_AREA,
DLYTRN.TOSTOL AS TO_LOCATION,
DLYTRN.TOINVS AS TO_INVENTORY_STATUS,
DLYTRN.TRLR_NUM AS TRAILER_NUMBER,
DLYTRN.CARCOD AS CARRIER_CODE,
DLYTRN.SHIP_ID AS SHIPMENT_IDENTIFIER,
DLYTRN.CLIENT_ID AS CLIENT_CODE,
DLYTRN.ORDNUM AS ORDER_NUMBER,
DLYTRN.ADJ_REF1 AS ADJUSTMENT_REFERENCE1,
DLYTRN.ADJ_REF2 AS ADJUSTMENT_REFERENCE2,
DLYTRN.USR_ID AS USER_NAME,
DLYTRN.DEVCOD AS DEVICE_CODE,
DLYTRN.ASSET_TYP AS ASSET_TYPE,
DLYTRN.ASSET_ID AS ASSET_IDENTIFIER,
DLYTRN.ASSET_STAT AS ASSET_STATUS,
DLYTRN.WH_ID AS WAREHOUSE_CODE,
DLYTRN.UOMCOD AS UNIT_OF_MEASURE,
DLYTRN.FTPCOD AS FOOTPRINT_CODE,
DLYTRN.INS_DT AS INSERT_DATE,
DLYTRN.LAST_UPD_DT AS LAST_UPDATED_DATE,
DLYTRN.INS_USER_ID AS INSERT_USER_ID,
DLYTRN.LAST_UPD_USER_ID AS LAST_UPDATED_USER_ID,
PRTMST.PRTFAM AS PART_FAMILY,
prtftp_view.ftpcod AS FOOTPRINT_CODE,
prtftp_view.untcas AS UNITS_PER_CASE,
prtftp_view.untpak AS UNITS_PER_PACK,
prtftp_view.untpal AS UNITS_PER_PALLET,
prtftp_view.paluom AS PALLET_UOM,
prtftp_view.casuom AS CASE_UOM,
prtftp_view.rcvuom AS RECEIVE_UOM,
prtftp_view.pallen AS PALLET_LENGTH,
prtftp_view.palhgt AS PALLET_HEIGHT,
prtftp_view.palwid AS PALLET_WIDTH,
prtftp_view.caslen AS CASE_LENGTH,
prtftp_view.cashgt AS CASE_HEIGHT,
prtftp_view.caswid AS CASE_WIDTH,
prtftp_view.untlen AS UNIT_LENGTH,
prtftp_view.unthgt AS UNIT_HEIGHT,
prtftp_view.untwid AS UNIT_WIDTH,
prtftp_view.grswgt AS UOM_GROSS_WEIGHT,
prtftp_view.netwgt AS UOM_NET_WEIGHT,
prtftp_view.caslvl AS CASE_PER_LEVEL,
aremst1.sigflg AS FROM_AREA_SIGNIFICANT_FLAG,
aremst1.fwiflg AS FROM_AREA_FOUR_WALL_INV_FLAG,
aremst1.pckcod AS FROM_AREA_PICK_CODE,
aremst1.bldg_id AS FROM_AREA_BUILDING_ID,
aremst1.praflg AS FROM_AREA_PROCESSING_FLAG,
aremst1.expflg AS FROM_AREA_EXPECTED_RECEIPT_FLAG,
aremst1.rcv_dck_flg AS FROM_AREA_RECEIVE_DOCK_FLAG,
aremst1.adjflg AS FROM_AREA_ADJUSTMENTS_FLAG,
aremst1.cntflg AS FROM_AREA_COUNTABLE_FLAG,
aremst1.wipflg AS FROM_AREA_WORK_IN_PROCESS_FLAG,
aremst1.xdaflg AS FROM_AREA_CROSS_DOCK_FLAG,
aremst2.sigflg AS TO_AREA_SIGNIFICANT_FLAG,
aremst2.fwiflg AS TO_AREA_FOUR_WALL_INV_FLAG,
aremst2.pckcod AS TO_AREA_PICK_CODE,
aremst2.bldg_id AS TO_AREA_BUILDING_ID,
aremst2.praflg AS TO_AREA_PROCESSING_FLAG,
aremst2.expflg AS TO_AREA_EXPECTED_RECEIPT_FLAG,
aremst2.rcv_dck_flg AS TO_AREA_FROM_AREA_RECEIVE_DOCK_FLAG,
aremst2.adjflg AS TO_AREA_ADJUSTMENTS_FLAG,
aremst2.cntflg AS TO_AREA_COUNTABLE_FLAG,
aremst2.wipflg AS TO_AREA_WORK_IN_PROCESS_FLAG,
aremst2.xdaflg AS TO_AREA_CROSS_DOCK_FLAG,
locmst1.repflg AS FROM_LOCATION_REPLENISHMENT_FLAG,
locmst1.useflg AS FROM_LOCATION_USEABLE_FLAG,
locmst1.stoflg AS FROM_LOCATION_STORABLE_FLAG,
locmst1.pckflg AS FROM_LOCATION_PICKABLE_FLAG,
locmst1.trvseq AS FROM_LOCATION_TRAVEL_SEQUENCE,
locmst2.repflg AS TO_LOCATION_REPLENISHMENT_FLAG,
locmst2.useflg AS TO_LOCATION_USEABLE_FLAG,
locmst2.stoflg AS TO_LOCATION_STORABLE_FLAG,
locmst2.pckflg AS TO_LOCATION_PICKABLE_FLAG,
locmst2.trvseq AS TO_LOCATION_TRAVEL_SEQUENCE,
DLYTRN.WH_ID AS FACILITY_TZ,
NULL AS POLL_NAME
FROM dlytrn dlytrn WITH (NOLOCK)
LEFT OUTER JOIN prtmst prtmst WITH (NOLOCK)
ON dlytrn.prtnum = prtmst.prtnum
AND dlytrn.prt_client_id = prtmst.prt_client_id
AND dlytrn.wh_id = prtmst.wh_id_tmpl
LEFT OUTER JOIN prtftp_view prtftp_view WITH (NOLOCK)
ON prtftp_view.prtnum = prtmst.prtnum
AND prtftp_view.prt_client_id = prtmst.prt_client_id
AND prtftp_view.wh_id = prtmst.wh_id_tmpl
AND prtftp_view.ftpcod = ISNULL(dlytrn.ftpcod, prtftp_view.ftpcod)
AND prtftp_view.defftp_flg = (CASE
WHEN dlytrn.ftpcod IS NULL THEN 1
ELSE prtftp_view.defftp_flg
END)
LEFT OUTER JOIN aremst aremst1 WITH (NOLOCK)
ON aremst1.arecod = dlytrn.fr_arecod
AND aremst1.wh_id = dlytrn.wh_id
LEFT OUTER JOIN aremst aremst2 WITH (NOLOCK)
ON dlytrn.to_arecod = aremst2.arecod
AND aremst2.wh_id = dlytrn.wh_id
LEFT OUTER JOIN locmst locmst1 WITH (NOLOCK)
ON dlytrn.frstol = locmst1.stoloc
AND locmst1.arecod = dlytrn.fr_arecod
AND locmst1.wh_id = dlytrn.wh_id
LEFT OUTER JOIN locmst locmst2 WITH (NOLOCK)
ON dlytrn.tostol = locmst2.stoloc
AND locmst2.arecod = dlytrn.TO_ARECOD
AND locmst2.wh_id = dlytrn.wh_id
WHERE dlytrn.actcod NOT IN ('SLCHG', 'OLCHG', 'SLNEW', 'OLNEW', 'SCHG', 'OLDEL', 'SLDEL', 'OCHG')
AND dlytrn.trndte >=
CAST('&YEAR' + '-' + '&MONTH' + '-' + '&BEG_DAY' + ' ' + '&BEG_HOUR' + ':00' + ':00.000' AS DATETIME)
AND dlytrn.trndte <=
CAST('&YEAR' + '-' + '&MONTH' + '-' + '&END_DAY' + ' ' + '&END_HOUR' + ':59' + ':59.998' AS DATETIME)
UNION ALL
SELECT '&TRIGGER_VAL' AS TRIGGER_VAL,
NULL AS TRANSACTION_ID,
NULL AS TRANSACTION_DATE,
NULL AS OPERATION_CODE,
NULL AS ACTIVITY_CODE,
NULL AS LOAD_NUMBER,
NULL AS SUB_NUMBER,
NULL AS DETAIL_NUMBER,
NULL AS TO_LOAD_NUMBER,
NULL AS TO_SUB_NUMBER,
NULL AS TO_DETAIL_NUMBER,
NULL AS ITEM_NUMBER,
NULL AS PART_CLIENT_CODE,
NULL AS ORIGIN_CODE,
NULL AS REVISION_LEVEL,
NULL AS LOT_NUMBER,
NULL AS SUPPLIER_NUMBER,
NULL AS MOVE_REFERENCE,
NULL AS REASON_CODE,
NULL AS TRANSACTION_QUANTITY,
NULL AS FROM_AREA,
NULL AS FROM_LOCATION,
NULL AS FROM_INVENTORY_STATUS,
NULL AS TO_AREA,
NULL AS TO_LOCATION,
NULL AS TO_INVENTORY_STATUS,
NULL AS TRAILER_NUMBER,
NULL AS CARRIER_CODE,
NULL AS SHIPMENT_IDENTIFIER,
NULL AS CLIENT_CODE,
NULL AS ORDER_NUMBER,
NULL AS ADJUSTMENT_REFERENCE1,
NULL AS ADJUSTMENT_REFERENCE2,
NULL AS USER_NAME,
NULL AS DEVICE_CODE,
NULL AS ASSET_TYPE,
NULL AS ASSET_IDENTIFIER,
NULL AS ASSET_STATUS,
NULL AS WAREHOUSE_CODE,
NULL AS UNIT_OF_MEASURE,
NULL AS FOOTPRINT_CODE,
NULL AS INSERT_DATE,
NULL AS LAST_UPDATED_DATE,
NULL AS INSERT_USER_ID,
NULL AS LAST_UPDATED_USER_ID,
NULL AS PART_FAMILY,
NULL AS FOOTPRINT_CODE,
NULL AS UNITS_PER_CASE,
NULL AS UNITS_PER_PACK,
NULL AS UNITS_PER_PALLET,
NULL AS PALLET_UOM,
NULL AS CASE_UOM,
NULL AS RECEIVE_UOM,
NULL AS PALLET_LENGTH,
NULL AS PALLET_HEIGHT,
NULL AS PALLET_WIDTH,
NULL AS CASE_LENGTH,
NULL AS CASE_HEIGHT,
NULL AS CASE_WIDTH,
NULL AS UNIT_LENGTH,
NULL AS UNIT_HEIGHT,
NULL AS UNIT_WIDTH,
NULL AS UOM_GROSS_WEIGHT,
NULL AS UOM_NET_WEIGHT,
NULL AS CASE_PER_LEVEL,
NULL AS FROM_AREA_SIGNIFICANT_FLAG,
NULL AS FROM_AREA_FOUR_WALL_INV_FLAG,
NULL AS FROM_AREA_PICK_CODE,
NULL AS FROM_AREA_BUILDING_ID,
NULL AS FROM_AREA_PROCESSING_FLAG,
NULL AS FROM_AREA_EXPECTED_RECEIPT_FLAG,
NULL AS FROM_AREA_RECEIVE_DOCK_FLAG,
NULL AS FROM_AREA_ADJUSTMENTS_FLAG,
NULL AS FROM_AREA_COUNTABLE_FLAG,
NULL AS FROM_AREA_WORK_IN_PROCESS_FLAG,
NULL AS FROM_AREA_CROSS_DOCK_FLAG,
NULL AS TO_AREA_SIGNIFICANT_FLAG,
NULL AS TO_AREA_FOUR_WALL_INV_FLAG,
NULL AS TO_AREA_PICK_CODE,
NULL AS TO_AREA_BUILDING_ID,
NULL AS TO_AREA_PROCESSING_FLAG,
NULL AS TO_AREA_EXPECTED_RECEIPT_FLAG,
NULL AS TO_AREA_FROM_AREA_RECEIVE_DOCK_FLAG,
NULL AS TO_AREA_ADJUSTMENTS_FLAG,
NULL AS TO_AREA_COUNTABLE_FLAG,
NULL AS TO_AREA_WORK_IN_PROCESS_FLAG,
NULL AS TO_AREA_CROSS_DOCK_FLAG,
NULL AS FROM_LOCATION_REPLENISHMENT_FLAG,
NULL AS FROM_LOCATION_USEABLE_FLAG,
NULL AS FROM_LOCATION_STORABLE_FLAG,
NULL AS FROM_LOCATION_PICKABLE_FLAG,
NULL AS FROM_LOCATION_TRAVEL_SEQUENCE,
NULL AS TO_LOCATION_REPLENISHMENT_FLAG,
NULL AS TO_LOCATION_USEABLE_FLAG,
NULL AS TO_LOCATION_STORABLE_FLAG,
NULL AS TO_LOCATION_PICKABLE_FLAG,
NULL AS TO_LOCATION_TRAVEL_SEQUENCE,
NULL AS FACILITY_TZ,
'&POLL_NAME' AS POLL_NAME
FROM dual"
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 SED, please visit: https://www.gnu.org/software/sed/manual/html_node/The-_0022s_0022-Command.html