OP
Member
Joined: Aug 2024
Posts: 1 |
I am trying to beautify a merge expression in my package. Here is my expression, manually indented, WITHOUT the beautifier:
MERGE INTO Kap_Formatted_Fm_Black_List t
USING (SELECT Id,
Bl.Sourcerecid,
Bl.Name_Lat,
CASE
WHEN Bl.Rowid IS NULL THEN
1
END AS Delete_Flag
FROM Fmblacklist Bl
FULL OUTER JOIN Kap_Formatted_Fm_Black_List Kpbl
USING (Id)) Src
ON (t.Id = Src.Id)
WHEN MATCHED THEN
UPDATE
SET t.Sourcerecid = Src.Sourcerecid,
t.Name_Lat = Src.Name_Lat,
t.First_Word = UPPER(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 1), '[^a-zA-Z0-9'' ]', '')),
t.Second_Word = UPPER(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 2), '[^a-zA-Z0-9'' ]', '')),
t.Third_Word = UPPER(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 3), '[^a-zA-Z0-9'' ]', '')),
t.Length_First_Word = NVL(LENGTH(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 1), '[^a-zA-Z0-9'' ]', '')), 0),
t.Length_Second_Word = NVL(LENGTH(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 2), '[^a-zA-Z0-9'' ]', '')), 0),
t.Length_Third_Word = NVL(LENGTH(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 3), '[^a-zA-Z0-9'' ]', '')), 0),
t.Exists_First_Word = NVL2(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 1), '[^a-zA-Z0-9'' ]', ''), 1, 0),
t.Exists_Second_Word = NVL2(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 2), '[^a-zA-Z0-9'' ]', ''), 1, 0),
t.Exists_Third_Word = CASE
WHEN LENGTH(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 3), '[^a-zA-Z0-9'' ]', '')) > 2 THEN
1
ELSE
0
END
WHERE t.Name_Lat <> Src.Name_Lat
OR t.Sourcerecid <> Src.Sourcerecid --
DELETE
WHERE Src.Delete_Flag = 1 --
WHEN NOT MATCHED THEN --
INSERT
(t.Id,
t.Sourcerecid,
t.Name_Lat,
t.First_Word,
t.Second_Word,
t.Third_Word,
t.Length_First_Word,
t.Length_Second_Word,
t.Length_Third_Word,
t.Exists_First_Word,
t.Exists_Second_Word,
t.Exists_Third_Word) --
VALUES
(Src.Id,
Src.Sourcerecid,
Src.Name_Lat,
UPPER(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 1), '[^a-zA-Z0-9'' ]', '')),
UPPER(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 2), '[^a-zA-Z0-9'' ]', '')),
UPPER(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 3), '[^a-zA-Z0-9'' ]', '')),
NVL(LENGTH(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 1), '[^a-zA-Z0-9'' ]', '')), 0),
NVL(LENGTH(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 2), '[^a-zA-Z0-9'' ]', '')), 0),
NVL(LENGTH(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 3), '[^a-zA-Z0-9'' ]', '')), 0),
NVL2(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 1), '[^a-zA-Z0-9'' ]', ''), 1, 0),
NVL2(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 2), '[^a-zA-Z0-9'' ]', ''), 1, 0),
CASE WHEN LENGTH(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 3), '[^a-zA-Z0-9'' ]', '')) > 2 THEN 1 ELSE 0 END);
Here is the result WITH the beautifier:
MERGE INTO Kap_Formatted_Fm_Black_List t
USING (SELECT Id,
Bl.Sourcerecid,
Bl.Name_Lat,
CASE
WHEN Bl.Rowid IS NULL THEN
1
END AS Delete_Flag
FROM Fmblacklist Bl
FULL OUTER JOIN Kap_Formatted_Fm_Black_List Kpbl
USING (Id)) Src
ON (t.Id = Src.Id)
WHEN MATCHED THEN
UPDATE
SET t.Sourcerecid = Src.Sourcerecid,
t.Name_Lat = Src.Name_Lat,
t.First_Word = UPPER(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 1), '[^a-zA-Z0-9'' ]', '')),
t.Second_Word = UPPER(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 2), '[^a-zA-Z0-9'' ]', '')),
t.Third_Word = UPPER(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 3), '[^a-zA-Z0-9'' ]', '')),
t.Length_First_Word = NVL(LENGTH(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 1), '[^a-zA-Z0-9'' ]', '')), 0),
t.Length_Second_Word = NVL(LENGTH(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 2), '[^a-zA-Z0-9'' ]', '')), 0),
t.Length_Third_Word = NVL(LENGTH(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 3), '[^a-zA-Z0-9'' ]', '')), 0),
t.Exists_First_Word = NVL2(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 1), '[^a-zA-Z0-9'' ]', ''), 1, 0),
t.Exists_Second_Word = NVL2(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 2), '[^a-zA-Z0-9'' ]', ''), 1, 0),
t.Exists_Third_Word = CASE
WHEN LENGTH(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 3), '[^a-zA-Z0-9'' ]', '')) > 2 THEN
1
ELSE
0
END
WHERE t.Name_Lat <> Src.Name_Lat
OR t.Sourcerecid <> Src.Sourcerecid --
DELETE
WHERE Src.Delete_Flag = 1 --
WHEN NOT MATCHED THEN --
INSERT(t.Id, t.Sourcerecid, t.Name_Lat, t.First_Word, t.Second_Word, t.Third_Word, t.Length_First_Word, t.Length_Second_Word, t.Length_Third_Word, t.Exists_First_Word, t.Exists_Second_Word, t.Exists_Third_Word) --
VALUES(Src.Id, Src.Sourcerecid, Src.Name_Lat, UPPER(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 1), '[^a-zA-Z0-9'' ]', '')), UPPER(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 2), '[^a-zA-Z0-9'' ]', '')), UPPER(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 3), '[^a-zA-Z0-9'' ]', '')), NVL(LENGTH(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 1), '[^a-zA-Z0-9'' ]', '')), 0), NVL(LENGTH(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 2), '[^a-zA-Z0-9'' ]', '')), 0), NVL(LENGTH(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 3), '[^a-zA-Z0-9'' ]', '')), 0), NVL2(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 1), '[^a-zA-Z0-9'' ]', ''), 1, 0), NVL2(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 2), '[^a-zA-Z0-9'' ]', ''), 1, 0),CASE
WHEN LENGTH(REGEXP_REPLACE(REGEXP_SUBSTR(Src.Name_Lat, '\S+', 1, 3), '[^a-zA-Z0-9'' ]', '')) > 2 THEN
1
ELSE
0
END);
Please advice how to fix the problem or is it a bug in the beautifier?
|
