Issue 17663: re.sub not replacing all (original) (raw)

fails to replace last occurrence of string v_wofstgvw, reproduced in 3.2.3, 3.2.4 and 3.3.1

a='''

-- V_INT_CTRC_ENTRY_DATE

REPLACE VIEW V_WOFSTGVW.V_INT_CTRC_ENTRY_DATE AS LOCKING ROW FOR ACCESS SELECT
D.DY DY,
D.DW_CRRT_FL CURRENT_FLAG,
D.MTH CURRENT_MTH,
(CAST(D.DY AS DATE) - EXTRACT(DAY FROM (CAST( D.DY AS DATE)))+1) CURRENT_MTH_BEG_DT,
ADD_MONTHS ((CAST(D.DY AS DATE) - EXTRACT(DAY FROM (CAST( D.DY AS DATE)))+1) , 1) - 1 CURRENT_MTH_END_DT,
D.WOFC_MTH_END_FLG CURRENT_MTH_END_FLG,
D.WOFC_QTR_END_FLG CURRENT_QTR_END_FL,
D.YR CURRENT_YEAR,
((D.YR||'01/01')(DATE)) CURRENT_YEAR_BEG_DT ,
((D.YR||'12/31')(DATE)) CURRENT_YEAR_END_DT ,
D.WOFC_YR_END_FLG CURRENT_YEAR_END_FL,
D.HDAY_FLG HOLIDAY_FLG,
CAST(ADD_MONTHS (CASE WOFC_MTH_END_FLG WHEN 'Y' THEN WOFCO_FRST_DY_MTH ELSE WOFCO_BEGIN_MTH_DT END, -1)AS CHAR(7)) PREV_REP_MTH, ADD_MONTHS (CASE WOFC_MTH_END_FLG
WHEN 'Y'
THEN WOFCO_FRST_DY_MTH
ELSE WOFCO_BEGIN_MTH_DT
END,
-1 ) PREV_REP_MTH_BEG_DT,
ADD_MONTHS (CASE WOFC_MTH_END_FLG
WHEN 'Y'
THEN D.DY
ELSE D.WOFCO_MTH_END_DT
END,
-1 ) PREV_REP_MTH_END_DT
, D.PREV_MTH PRIOR_MTH,
PSETM.PRIOR_SET_CURRENT_MTH,
PSETM.PRIOR_SET_CURRENT_MTH_BGN_DT,
PSETM.PRIOR_SET_CURRENT_MTH_END_DT,
PREPSETM.PRIOR_SET_REP_MTH,
PREPSETM.PRIOR_SET_REP_MTH_BGN_DT,
PREPSETM.PRIOR_SET_REP_MTH_END_DT,
CASE WOFC_MTH_END_FLG
WHEN 'Y'
THEN MTH
ELSE PREV_MTH
END REP_MTH,
CASE WOFC_MTH_END_FLG
WHEN 'Y'
THEN WOFCO_FRST_DY_MTH
ELSE WOFCO_BEGIN_MTH_DT
END REP_MTH_BEG_DT,
CASE WOFC_MTH_END_FLG
WHEN 'Y'
THEN D.DY
ELSE D.WOFCO_MTH_END_DT
END REP_MTH_END_DT, CASE WOFC_MTH_END_FLG
WHEN 'Y'
THEN ADD_MONTHS (D.WOFCO_FRST_DY_MTH,
12 )
ELSE ADD_MONTHS (D.WOFCO_BEGIN_MTH_DT, 12)
END REP_MTH_NEXT_YEAR_BEG_DT,
CASE WOFC_MTH_END_FLG
WHEN 'Y'
THEN ADD_MONTHS (D.DY, 12)
ELSE D.WOFCO_MTH_NXT_YR_DT
END REP_MTH_NEXT_YEAR_END_DT,
CASE WOFC_MTH_END_FLG
WHEN 'Y'
THEN D.YR
ELSE SUBSTR (PREV_MTH, 1, 4)
END REP_YEAR,
CASE WOFC_MTH_END_FLG
WHEN 'Y'
THEN (CAST((CAST(D.YR AS DATE FORMAT 'YYYY')||'/01/01') AS DATE FORMAT 'YYYY/MM/DD')) ELSE (CAST((SUBSTR (PREV_MTH, 1, 4) || '/01/01')AS DATE FORMAT 'YYYY/MM/DD' )) END REP_YEAR_BEG_DT,
CASE WOFC_MTH_END_FLG
WHEN 'Y'
THEN (CAST((CAST(D.YR AS DATE FORMAT 'YYYY' )||'/12/31') AS DATE FORMAT 'YYYY/MM/DD'))
ELSE (CAST((SUBSTR (PREV_MTH, 1, 4) || '/12/31') AS DATE FORMAT 'YYYY/MM/DD' ))
END REP_YEAR_END_DT, D.SET_MTH SET_CURRENT_MTH,
D.SET_BEGIN_MTH_DT SET_CURRENT_MTH_BGN_DT,
D.SET_MTH_END_DT SET_CURRENT_MTH_END_DT,
D.SET_YR SET_CURRENT_YEAR,
D1.SET_CURRENT_BEG_DT SET_CURRENT_YEAR_BEG_DT,
D2.SET_CURRENT_END_DT SET_CURRENT_YEAR_END_DT,
D.SET_MTH_END_FLG SET_MTH_END_FLAG,
D.SET_QTR_END_FLG SET_QTR_END_FLAG,
CASE D.SET_MTH_END_FLG
WHEN 'Y'
THEN (CAST (D.SET_BEGIN_MTH_DT AS CHAR(7)))
ELSE (CAST (SET_MTH_BEG_PREV_DT AS CHAR (7)))
END SET_REP_MTH,
CASE D.SET_MTH_END_FLG
WHEN 'Y'
THEN D.SET_BEGIN_MTH_DT
ELSE SET_MTH_BEG_PREV_DT
END SET_REP_MTH_BEG_DT,
CASE D.SET_MTH_END_FLG
WHEN 'Y'
THEN D.SET_MTH_END_DT
ELSE SET_MTH_END_PREV_DT
END SET_REP_MTH_END_DT,
SUBSTR (CASE D.SET_MTH_END_FLG
WHEN 'Y'
THEN (CAST (D.SET_BEGIN_MTH_DT AS CHAR(7)))
ELSE (CAST (SET_MTH_BEG_PREV_DT AS CHAR(7)))
END,
1, 4)SET_REP_YEAR ,
RD1.SET_RPT_YEAR_BEG_DT SET_RPT_YEAR_BEG_DT,
SYB.SET_RPT_YEAR_END_DT,
D.SET_YR_END_FLG SET_YEAR_END_FLAG
FROM V_WOFSTGVW.D_DT D LEFT OUTER JOIN (SELECT D.SET_YR,
MIN (D.SET_BEGIN_YR_DT) SET_CURRENT_BEG_DT
FROM V_WOFSTGVW.D_DT D
WHERE (CAST (D.SET_BEGIN_YR_DT AS CHAR(4))) =
D.SET_YR
GROUP BY D.SET_YR) D1
ON D1.SET_YR = D.SET_YR
LEFT OUTER JOIN (SELECT D_SET_CURR_END.SET_YR, D_SET_CURR_END.DY SET_CURRENT_END_DT
FROM V_WOFSTGVW.D_DT D_SET_CURR_END
WHERE D_SET_CURR_END.SET_YR_END_FLG = 'Y') D2
ON D2.SET_YR = D.SET_YR LEFT OUTER JOIN (SELECT CASE WOFC_MTH_END_FLG
WHEN 'Y'
THEN D.YR
ELSE SUBSTR (D.PREV_MTH, 1, 4)
END SET_RPT_YR,
MIN (D.SET_BEGIN_YR_DT) SET_RPT_YEAR_BEG_DT
FROM V_WOFSTGVW.D_DT D
WHERE D.PREV_MTH IS NOT NULL
AND SET_BEGIN_YR_DT IS NOT NULL
GROUP BY CASE WOFC_MTH_END_FLG
WHEN 'Y'
THEN D.YR
ELSE SUBSTR (D.PREV_MTH, 1, 4)
END) RD1
ON RD1.SET_RPT_YR =
(CASE D.SET_MTH_END_FLG
WHEN 'Y'
THEN (CAST(D.SET_BEGIN_MTH_DT AS CHAR(4)))
ELSE (CAST (D.SET_MTH_BEG_PREV_DT AS CHAR(4)))
END
)
LEFT OUTER JOIN (SELECT D.SET_MTH, MIN (D.SET_MTH_END_DT) SET_RPT_YEAR_END_DT
FROM V_WOFSTGVW.D_DT D
WHERE D.SET_MTH =
CASE D.SET_MTH_END_FLG
WHEN 'Y'
THEN (CAST(D.SET_BEGIN_MTH_DT AS CHAR(4)))
ELSE (CAST (SET_MTH_BEG_PREV_DT AS CHAR(4)))
END
GROUP BY D.SET_MTH) SYB
ON ( SYB.SET_MTH =
CASE WOFC_MTH_END_FLG
WHEN 'Y'
THEN D.YR
ELSE SUBSTR (D.PREV_MTH, 1, 4)
END
|| '/12' )
LEFT OUTER JOIN (SELECT DISTINCT D.SET_MTH_END_FLG SET_MTH_END_FLAG,
D.SET_MTH PRIOR_SET_CURRENT_MTH,
D.SET_BEGIN_MTH_DT PRIOR_SET_CURRENT_MTH_BGN_DT,
D.SET_MTH_END_DT PRIOR_SET_CURRENT_MTH_END_DT
FROM V_WOFSTGVW.D_DT D) PSETM
ON PSETM.PRIOR_SET_CURRENT_MTH =
(CAST (ADD_MONTHS (D.SET_BEGIN_MTH_DT, -1) AS CHAR(7)))
AND PSETM.SET_MTH_END_FLAG = D.SET_MTH_END_FLG LEFT OUTER JOIN (SELECT DISTINCT D.SET_MTH_END_FLG SET_MTH_END_FLAG,
D.SET_MTH PRIOR_SET_REP_MTH,
D.SET_BEGIN_MTH_DT PRIOR_SET_REP_MTH_BGN_DT,
D.SET_MTH_END_DT PRIOR_SET_REP_MTH_END_DT
FROM V_WOFSTGVW.D_DT D) PREPSETM
ON PREPSETM.PRIOR_SET_REP_MTH =
(CAST (ADD_MONTHS (CASE D.SET_MTH_END_FLG
WHEN 'Y'
THEN D.SET_BEGIN_MTH_DT
ELSE SET_MTH_BEG_PREV_DT
END, -1 ) AS CHAR(7)) )
AND PREPSETM.SET_MTH_END_FLAG = D.SET_MTH_END_FLG WHERE V_WOFSTGVW.D.DY <> DATE '9999-12-31';'''

re.sub('v_wofstgvw','d_dscteve', a, re.MULTILINE, re.IGNORECASE)