Merge rules in scripting
So it has been a bit busy over here with OpenWorld coming up (are you all going???)... but I recently played around with the match merge case discussed earlier seeing if I can script some pieces of it and that was quite a bit of fun. But here it is...
Keep in mind that this does the entire set of MERGE rules for the operator (I've already created the rest via the UI). Also I'm using some custom rules which is placed in a variable and then loaded.
OMBCC 'MY_PROJECT/TARGET'
#Creating a match bin
OMBALTER MAPPING 'CUST_MATCH_MERGE'
MODIFY OPERATOR 'MATCHMERGE'
SET PROPERTIES (MATCH_KEYS) VALUES('INGRP1.NAME_L')
#Creating a merge rule set
#Rule 1:
#Note that the order of the last two settings has to be exactly like this! TYPE before Attribute.
OMBALTER MAPPING 'CUST_MATCH_MERGE'
ADD MERGE_RULES 'ME_1_NAMEM'
OF OPERATOR 'MATCHMERGE' SET PROPERTIES (TYPE) VALUES ('MM_MIN_MAX')
MODIFY MERGE_RULES 'ME_1_NAMEM'
OF OPERATOR 'MATCHMERGE' SET PROPERTIES (ATTRIBUTE_NAME) VALUES ('NAME_M')
MODIFY MERGE_RULES 'ME_1_NAMEM'
OF OPERATOR 'MATCHMERGE' SET PROPERTIES (MIN_MAX_TYPE) VALUES ('MM_LONGEST')
MODIFY MERGE_RULES 'ME_1_NAMEM'
OF OPERATOR 'MATCHMERGE' SET PROPERTIES (MIN_MAX_ATTRIBUTE) VALUES ('NAME_M')
#Rule 2:
#Custom rule text is added to a variable and called from OMB.
#Note the escape characters in the PL/SQL text:
# Double quotes are escaped by a slash
# Single quotes are escaped by a single quote
set custom_rule2 "fName varchar2(2000) := null;
BEGIN
-- return the longest first name from table a
-- in table a, CUST_SEQ is not null
FOR i IN M_MATCHES.FIRST .. M_MATCHES.LAST LOOP
IF M_MATCHES(i)."NAME_F" IS NOT NULL and
M_MATCHES(i)."CUST_SEQ" is not null THEN
IF fName IS NULL OR LENGTH(RTRIM(M_MATCHES(i)."NAME_F")) > LENGTH(RTRIM(fName)) THEN
fName := M_MATCHES(i)."NAME_F";
END IF;
END IF;
END LOOP;
RETURN fName;
END;"
OMBALTER MAPPING 'CUST_MATCH_MERGE'
ADD MERGE_RULES 'ME_2_NAMEF'
OF OPERATOR 'MATCHMERGE' SET PROPERTIES (TYPE) VALUES ('MM_CUSTOM')
MODIFY MERGE_RULES 'ME_2_NAMEF'
OF OPERATOR 'MATCHMERGE' SET PROPERTIES (ATTRIBUTE_NAME) VALUES ('NAME_F')
MODIFY MERGE_RULES 'ME_2_NAMEF'
OF OPERATOR 'MATCHMERGE' SET PROPERTIES (CUSTOM_TEXT) VALUES ('$custom_rule2')
#Rule 3:
#Custom rule text is added to a variable and called from OMB.
#Note the escape characters in the PL/SQL text:
# Double quotes are escaped by a slash
# Single quotes are escaped by a single quote
set custom_rule3 "BEGIN\n
type info_type is table of varchar2(4000) index by binary_integer;
type infocnt_type is table of pls_integer index by binary_integer;
v_infos info_type;
v_infocnt infocnt_type;
retval varchar2(20);
retcnt pls_integer;
found boolean;
indx pls_integer := 1;
begin
-- return the modal value of "SSN" that is not null
for i in M_MATCHES.FIRST .. M_MATCHES.LAST loop
if M_MATCHES(i)."SSN" is not null and M_MATCHES(i)."SSN" != ''999-99-9999'' then
found := false;
if v_infos.count > 0 then
for j in v_infos.first .. v_infos.last loop
if found = false and v_infos(j) = M_MATCHES(i)."SSN" then
-- found it
v_infocnt(j) := v_infocnt(j) + 1;
found := true;
end if;
end loop;
end if; -- v_infos is not null
if found = false then
v_infos(indx) := M_MATCHES(i)."SSN";
v_infocnt(indx) := 1;
indx := indx + 1;
end if;
end if;
end loop;
-- find value with max count
if v_infos.count > 0 then
for i in v_infos.first .. v_infos.last loop
if retval is null then
retval := v_infos(i);
retcnt := v_infocnt(i);
else
if (v_infocnt(i) > retcnt) then
retcnt := v_infocnt(i);
retval := v_infos(i);
indx := indx + 1;
end if;
end if;
end loop;
end if;
return retval;
nEND;"
OMBALTER MAPPING 'CUST_MATCH_MERGE'
ADD MERGE_RULES 'ME_3_SSN'
OF OPERATOR 'MATCHMERGE' SET PROPERTIES (TYPE) VALUES ('MM_CUSTOM')
MODIFY MERGE_RULES 'ME_3_SSN'
OF OPERATOR 'MATCHMERGE' SET PROPERTIES (ATTRIBUTE_NAME) VALUES ('SSN')
MODIFY MERGE_RULES 'ME_3_SSN'
OF OPERATOR 'MATCHMERGE' SET PROPERTIES (CUSTOM_TEXT) VALUES ('$custom_rule3')
#Rule 4:
#Custom rule text is added to a variable and called from OMB.
#Note the escape characters in the PL/SQL text:
# Double quotes are escaped by a slash
# Single quotes are escaped by a single quote
set custom_rule4 "BEGIN
-- select the cust_seq from the same record as the merged SSN.
for i in M_MATCHES.FIRST .. M_MATCHES.LAST loop
if M_MATCHES(i)."SSN" is not null and
M_MATCHES(i)."CUST_SEQ" is not null and
M_MATCHES(i)."SSN" = M_MERGE."SSN" then
return M_MATCHES(i)."CUST_SEQ" ;
end if;
end loop;
return M_MERGE."CUST_SEQ"; -- previously selected cust sequence
END;"
OMBALTER MAPPING 'CUST_MATCH_MERGE'
ADD MERGE_RULES 'ME_4_CUSTSEQ'
OF OPERATOR 'MATCHMERGE' SET PROPERTIES (TYPE) VALUES ('MM_CUSTOM')
MODIFY MERGE_RULES 'ME_4_CUSTSEQ'
OF OPERATOR 'MATCHMERGE' SET PROPERTIES (ATTRIBUTE_NAME) VALUES ('NAME_F')
MODIFY MERGE_RULES 'ME_4_CUSTSEQ'
OF OPERATOR 'MATCHMERGE' SET PROPERTIES (CUSTOM_TEXT) VALUES ('$custom_rule4')
#Rule 5:
#Custom rule text is added to a variable and called from OMB.
#Note the escape characters in the PL/SQL text:
# Double quotes are escaped by a slash
# Single quotes are escaped by a single quote
OMBALTER MAPPING 'CUST_MATCH_MERGE'
ADD MERGE_RULES 'ME_5_SAPCUSTID'
OF OPERATOR 'MATCHMERGE' SET PROPERTIES (TYPE) VALUES ('MM_RANK')
MODIFY MERGE_RULES 'ME_5_SAPCUSTID'
OF OPERATOR 'MATCHMERGE' SET PROPERTIES (ATTRIBUTE_NAME) VALUES ('SAP_CUST_ID')
ADD RANK_RULES 'SAP_RULE'
OF MERGE_RULES 'ME_5_SAPCUSTID'
OF OPERATOR 'MATCHMERGE'
MODIFY RANK_RULES 'SAP_RULE'
OF MERGE_RULES 'ME_5_SAPCUSTID'
OF OPERATOR 'MATCHMERGE' SET PROPERTIES (EXPRESSION) VALUES ('length(INGRP1.SAP_CUST_ID) = 7')
