Using database functions in an MDM Studio project. A useful example for extending MDM to compare multivalued attributes.
Article ID: KB0085466
Updated On:
| Products | Versions |
|---|---|
| TIBCO MDM | - |
| Not Applicable | - |
Description
Resolution:
Currently, the duplicate function available in MDM does not support multivalued (MVT) attributes. When requiring new records to not be created if a subset of attributes, including if some MVTs are among the attributes, matches with one or more existing records, the "duplicate" function cannot be used.
1). Provide a database function that provides the missing functionality and then reference the function in an MDM Studio tableSQL(...) call. A function with a signature of:
COMPAREMVSTRING(<EnterpriseName>,
<RepositoryName>,
<MultiValueAttributeName>,
<SourceRecord>.<MultiValueList>,
<SourceRecord>.PRODUCTID,
<SourceRecord>.PRODUCTIDEXT,
<TargetRecord>.PRODUCTID,
<TargetRecord>.PRODUCTIDEXT,
<LATESTorCONFIRMED>)
and returning :
* 1 when the values in <MultiValueList> for the Source Record match those in the Target for either LATEST or CONFIRMED versions.
* 0 otherwise.
for comparing MultiValue String values is used as an example.
Of particular note in the above signature, is the form taken by the parameter <SourceRecord>.<MultiValueList> :
* when <SourceRecord>.<MultiValueList> = ('XNOVALUEX'), the LATEST or CONFIRMED version of the <MultiValueAttributeName> attribute in the database is used.
* when <SourceRecord>.<MultiValueList> = T_STRTAB(...List of Values...), a database object of type T_STRTAB, populated with the ...List of Values..., is used.
Note that in the second case, a database object of type T_STRTAB is being passed to the database function :
T_STRTAB(<SourceRecord>.<MultiValueList>)
It is actually a type constructor, with a variable length set of values as attributes that are being passed. The type T_STRTAB is defined as:
create or replace TYPE T_STRTAB AS TABLE OF VARCHAR2(256); -- T_STRTAB: String Table (for multivalues)
If a simple attempt is made to pass the source record MultiValue attribute as in:
COMPAREMVSTRING(<EnterpriseName>,
<RepositoryName>,
<MultiValueAttributeName>,
<SourceRecord>.<MultiValueList>,
<SourceRecord>.PRODUCTID,
<SourceRecord>.PRODUCTIDEXT,
<TargetRecord>.PRODUCTID,
<TargetRecord>.PRODUCTIDEXT,
<LATESTorCONFIRMED>)
MDM replaces <SourceRecord>.<MultiValueAttribute> with the list of values in-line. As a result, MDM ends up creating the function call:
COMPAREMVSTRING(<EnterpriseName>,
<RepositoryName>,
<MultiValueAttributeName>,
<MV1>,
<MV2>,
<MV3>,
...
<SourceRecord>.PRODUCTID,
<SourceRecord>.PRODUCTIDEXT,
<TargetRecord>.PRODUCTID,
<TargetRecord>.PRODUCTIDEXT,
<LATESTorCONFIRMED>)
This function call is then rejected as having the wrong number or type of arguments. By adding the T_STRTAB constructor, MDM creates the following function call instead:
COMPAREMVSTRING(<EnterpriseName>,
<RepositoryName>,
<MultiValueAttributeName>,
T_STRTAB(<MV1>,<MV2>,<MV3>,...),
<SourceRecord>.PRODUCTID,
<SourceRecord>.PRODUCTIDEXT,
<TargetRecord>.PRODUCTID,
<TargetRecord>.PRODUCTIDEXT,
<LATESTorCONFIRMED>)
This function call is correct. Note that the example shown is for Oracle. SQL Server uses a slightly different syntax for type constructors. For the function signature given, it is assumed that for the function behind the signature:
* The <TargetRecord>.<MultiValueList> values are always taken from the database.
* The final parameter, <LATESTorCONFIRMED> is used to determine whether the LATEST or CONFIRMED version of <SourceRecord> or <TargetRecord> <MultiValueList> values are to be taken from the database where the <SourceRecord> <MultiValueList> values are not provided in the <SourceRecord>.<MultiValueList> parameter).
Why is <SourceRecord>.<MultiValueList> provided in the function call? Can't the LATEST values for the Source record just be read from the database?
The short answer is no. The long answer is because when a record is modified, the latest values are not stored in the database. Instead, they are held directly by the code processing the form, so it is necessary to pull the values directly from the code. This is why the function call allows for <SourceRecord>.<MultiValueList> to be provided in the call.
The function behind the call does not assume that the record identified by PRODUCTID/PRODUCTIDEXT actually exists in the database at all. If it does, then the associated database PRODUCTKEYID is used, otherwise a value of null is set for the key (which is still correct even if <SourceRecord>. <MultiValueList> = ('XNOVALUEX'). In this case, if no corresponding database record exists for PRODUCTID/PRODUCTIDEXT, then the <SourceRecord>. <MultiValueList> "taken from the database" will also be null). The function behind the function call correctly handles the following cases:
* Comparing a Source MultiValue String Attribute when the parameter <SourceRecord>.<MultiValueList> is set (that is, taken from the Modify/Add Form) with Target records from the same repository.
* Comparing a Source MultiValue String Attribute when the parameter <SourceRecord>.<MultiValueList> is not set (that is, taken from the Database) with Target records from the same repository.
In both cases, where source PRODUCTID/PRODUCTIDEXT = target PRODUCTID/PRODUCTIDEXT, no comparison is made, as they are assumed to be the same record (keys the same).
Finally, the function behind the function call COMPAREMVSTRING(...) compares <SourceRecord> and <TargetRecord> <MultiValueList> values without regard to order. For example, ('MV1,'MV2','MV3')=('MV1,'MV3','MV2')=('MV3,'MV1','MV2')=...
To compare MultiValues of other types, it is only necessary to copy the function COMPAREMVSTRING to comparable functions for comparing decimals (COMPAREMVDECIMAL), comparing numbers (COMPAREMVNUMBER), comparing dates (COMPAREMVDATE) and so on, and changing the function parameter :
P_MVSTRL IN T_STRTAB, -- Current Source (MultiValue) String List
to
P_MVDECL IN T_DECTAB, -- Current Source (MultiValue) Decimal List
or P_MVNUML IN T_NUMTAB, -- Current Source (MultiValue) Number List
or P_MVDTEL IN T_DTETAB, -- Current Source (MultiValue) Date List
or whatever. Additionally creating the types :
create or replace TYPE T_DECTAB AS TABLE OF DECIMAL; -- T_DECTAB: Decimal Table (for multivalues)
create or replace TYPE T_NUMTAB AS TABLE OF NUMBER; -- T_NUMTAB: Number Table (for multivalues)
create or replace TYPE T_DTETAB AS TABLE OF DATE; -- T_DTETAB: Date Table (for multivalues)
referenced, and then changing the function call to:
COMPAREMVSTRING(<EnterpriseName>, COMPAREMVSTRING(<EnterpriseName>, and so on...
<RepositoryName>, <RepositoryName>,
<MultiValueAttributeName>, <MultiValueAttributeName>,
T_DECTAB(<MV1>,<MV2>,<MV3>,...), T_NUMTAB(<MV1>,<MV2>,<MV3>,...),
<SourceRecord>.PRODUCTID, <SourceRecord>.PRODUCTID,
<SourceRecord>.PRODUCTIDEXT, <SourceRecord>.PRODUCTIDEXT,
<TargetRecord>.PRODUCTID, <TargetRecord>.PRODUCTID,
<TargetRecord>.PRODUCTIDEXT, <TargetRecord>.PRODUCTIDEXT,
<LATESTorCONFIRMED>) <LATESTorCONFIRMED>)
3. Generalizing the approach.
The approach taken here can be extended out to other functionality as required for all sorts of possibilities, so enabling MDM to tackle a wider range of problems than would otherwise have been possible.
4. Using the MuliValue Comparison function.
To use the comparison function is a case of referencing it in the (MDM Studio Rulebase construct) tableSQL function:
tableSQL({ <RepositoryLinkVariable>.<Attribute_D> },
where <CurrentRecord.Attribute_X>> = <RepositoryLinkVariable>.<Attribute_X> and
COMPAREMVSTRING(<EnterpriseName>,
<RepositoryName>,
<MultiValueAttributeName>,
T_STRTAB(<MV1>,<MV2>,<MV3>,...),
<SourceRecord>.PRODUCTID,
<SourceRecord>.PRODUCTIDEXT,
<TargetRecord>.PRODUCTID,
<TargetRecord>.PRODUCTIDEXT,
<LATESTorCONFIRMED>) = 1 )
For example.
tableSQL(varLinkMVR.PRODUCTID,where TYPE = varLinkMVR.TYPE and COMPAREMVSTRING('ExampleEnterprise','ExampleRepository','MULTIVAL',
T_MVTABLE(varMVList),PRODUCTID,varIDEXT,varLinkMVR.PRODUCTID,varLinkMVR.PRODUCTIDEXT) = 1)
Note that such a call fails where varMVList or varIDEXT or both = null. In the worst case, MDM generates a function call of :
COMPAREMVSTRING(<EnterpriseName>,<RepositoryName>,<MultiValueAttributeName>,T_STRTAB(<MV1>,<MV2>,<MV3>,...),<SourceRecord>.PRODUCTID,<TargetRecord>.PRODUCTID,<TargetRecord>.PRODUCTIDEXT,<LATESTorCONFIRMED>)
COMPAREMVSTRING(<EnterpriseName>,<RepositoryName>,<MultiValueAttributeName>,<SourceRecord>.PRODUCTID,<SourceRecord>.PRODUCTIDEXT,<TargetRecord>.PRODUCTID,<TargetRecord>.PRODUCTIDEXT,<LATESTorCONFIRMED>)
COMPAREMVSTRING(<EnterpriseName>,<RepositoryName>,<MultiValueAttributeName>,<SourceRecord>.PRODUCTID,<TargetRecord>.PRODUCTID,<TargetRecord>.PRODUCTIDEXT,<LATESTorCONFIRMED>)
which is then rejected as having the wrong number or type of arguments. MDM does not handle nulls well when generating function calls. To overcome this, if varMVList is null, it is given the list value ('XNOVALUEX'), while if varIDEXT is null, it is given the value 'XNOVALUEX'. The function detects this and replaces the values ('XNOVALUEX') / 'XNOVALUEX' with null.
5). For reference, the function definition, type definition and sample MDM Studio project that makes use of the function COMPAREMVSTRING are attached in the file (Filename: MVAttributeDuplicateCheck.zip).
Currently, the duplicate function available in MDM does not support multivalued (MVT) attributes. When requiring new records to not be created if a subset of attributes, including if some MVTs are among the attributes, matches with one or more existing records, the "duplicate" function cannot be used.
1). Provide a database function that provides the missing functionality and then reference the function in an MDM Studio tableSQL(...) call. A function with a signature of:
COMPAREMVSTRING(<EnterpriseName>,
<RepositoryName>,
<MultiValueAttributeName>,
<SourceRecord>.<MultiValueList>,
<SourceRecord>.PRODUCTID,
<SourceRecord>.PRODUCTIDEXT,
<TargetRecord>.PRODUCTID,
<TargetRecord>.PRODUCTIDEXT,
<LATESTorCONFIRMED>)
and returning :
* 1 when the values in <MultiValueList> for the Source Record match those in the Target for either LATEST or CONFIRMED versions.
* 0 otherwise.
for comparing MultiValue String values is used as an example.
Of particular note in the above signature, is the form taken by the parameter <SourceRecord>.<MultiValueList> :
* when <SourceRecord>.<MultiValueList> = ('XNOVALUEX'), the LATEST or CONFIRMED version of the <MultiValueAttributeName> attribute in the database is used.
* when <SourceRecord>.<MultiValueList> = T_STRTAB(...List of Values...), a database object of type T_STRTAB, populated with the ...List of Values..., is used.
Note that in the second case, a database object of type T_STRTAB is being passed to the database function :
T_STRTAB(<SourceRecord>.<MultiValueList>)
It is actually a type constructor, with a variable length set of values as attributes that are being passed. The type T_STRTAB is defined as:
create or replace TYPE T_STRTAB AS TABLE OF VARCHAR2(256); -- T_STRTAB: String Table (for multivalues)
If a simple attempt is made to pass the source record MultiValue attribute as in:
COMPAREMVSTRING(<EnterpriseName>,
<RepositoryName>,
<MultiValueAttributeName>,
<SourceRecord>.<MultiValueList>,
<SourceRecord>.PRODUCTID,
<SourceRecord>.PRODUCTIDEXT,
<TargetRecord>.PRODUCTID,
<TargetRecord>.PRODUCTIDEXT,
<LATESTorCONFIRMED>)
MDM replaces <SourceRecord>.<MultiValueAttribute> with the list of values in-line. As a result, MDM ends up creating the function call:
COMPAREMVSTRING(<EnterpriseName>,
<RepositoryName>,
<MultiValueAttributeName>,
<MV1>,
<MV2>,
<MV3>,
...
<SourceRecord>.PRODUCTID,
<SourceRecord>.PRODUCTIDEXT,
<TargetRecord>.PRODUCTID,
<TargetRecord>.PRODUCTIDEXT,
<LATESTorCONFIRMED>)
This function call is then rejected as having the wrong number or type of arguments. By adding the T_STRTAB constructor, MDM creates the following function call instead:
COMPAREMVSTRING(<EnterpriseName>,
<RepositoryName>,
<MultiValueAttributeName>,
T_STRTAB(<MV1>,<MV2>,<MV3>,...),
<SourceRecord>.PRODUCTID,
<SourceRecord>.PRODUCTIDEXT,
<TargetRecord>.PRODUCTID,
<TargetRecord>.PRODUCTIDEXT,
<LATESTorCONFIRMED>)
This function call is correct. Note that the example shown is for Oracle. SQL Server uses a slightly different syntax for type constructors. For the function signature given, it is assumed that for the function behind the signature:
* The <TargetRecord>.<MultiValueList> values are always taken from the database.
* The final parameter, <LATESTorCONFIRMED> is used to determine whether the LATEST or CONFIRMED version of <SourceRecord> or <TargetRecord> <MultiValueList> values are to be taken from the database where the <SourceRecord> <MultiValueList> values are not provided in the <SourceRecord>.<MultiValueList> parameter).
Why is <SourceRecord>.<MultiValueList> provided in the function call? Can't the LATEST values for the Source record just be read from the database?
The short answer is no. The long answer is because when a record is modified, the latest values are not stored in the database. Instead, they are held directly by the code processing the form, so it is necessary to pull the values directly from the code. This is why the function call allows for <SourceRecord>.<MultiValueList> to be provided in the call.
The function behind the call does not assume that the record identified by PRODUCTID/PRODUCTIDEXT actually exists in the database at all. If it does, then the associated database PRODUCTKEYID is used, otherwise a value of null is set for the key (which is still correct even if <SourceRecord>. <MultiValueList> = ('XNOVALUEX'). In this case, if no corresponding database record exists for PRODUCTID/PRODUCTIDEXT, then the <SourceRecord>. <MultiValueList> "taken from the database" will also be null). The function behind the function call correctly handles the following cases:
* Comparing a Source MultiValue String Attribute when the parameter <SourceRecord>.<MultiValueList> is set (that is, taken from the Modify/Add Form) with Target records from the same repository.
* Comparing a Source MultiValue String Attribute when the parameter <SourceRecord>.<MultiValueList> is not set (that is, taken from the Database) with Target records from the same repository.
In both cases, where source PRODUCTID/PRODUCTIDEXT = target PRODUCTID/PRODUCTIDEXT, no comparison is made, as they are assumed to be the same record (keys the same).
Finally, the function behind the function call COMPAREMVSTRING(...) compares <SourceRecord> and <TargetRecord> <MultiValueList> values without regard to order. For example, ('MV1,'MV2','MV3')=('MV1,'MV3','MV2')=('MV3,'MV1','MV2')=...
To compare MultiValues of other types, it is only necessary to copy the function COMPAREMVSTRING to comparable functions for comparing decimals (COMPAREMVDECIMAL), comparing numbers (COMPAREMVNUMBER), comparing dates (COMPAREMVDATE) and so on, and changing the function parameter :
P_MVSTRL IN T_STRTAB, -- Current Source (MultiValue) String List
to
P_MVDECL IN T_DECTAB, -- Current Source (MultiValue) Decimal List
or P_MVNUML IN T_NUMTAB, -- Current Source (MultiValue) Number List
or P_MVDTEL IN T_DTETAB, -- Current Source (MultiValue) Date List
or whatever. Additionally creating the types :
create or replace TYPE T_DECTAB AS TABLE OF DECIMAL; -- T_DECTAB: Decimal Table (for multivalues)
create or replace TYPE T_NUMTAB AS TABLE OF NUMBER; -- T_NUMTAB: Number Table (for multivalues)
create or replace TYPE T_DTETAB AS TABLE OF DATE; -- T_DTETAB: Date Table (for multivalues)
referenced, and then changing the function call to:
COMPAREMVSTRING(<EnterpriseName>, COMPAREMVSTRING(<EnterpriseName>, and so on...
<RepositoryName>, <RepositoryName>,
<MultiValueAttributeName>, <MultiValueAttributeName>,
T_DECTAB(<MV1>,<MV2>,<MV3>,...), T_NUMTAB(<MV1>,<MV2>,<MV3>,...),
<SourceRecord>.PRODUCTID, <SourceRecord>.PRODUCTID,
<SourceRecord>.PRODUCTIDEXT, <SourceRecord>.PRODUCTIDEXT,
<TargetRecord>.PRODUCTID, <TargetRecord>.PRODUCTID,
<TargetRecord>.PRODUCTIDEXT, <TargetRecord>.PRODUCTIDEXT,
<LATESTorCONFIRMED>) <LATESTorCONFIRMED>)
3. Generalizing the approach.
The approach taken here can be extended out to other functionality as required for all sorts of possibilities, so enabling MDM to tackle a wider range of problems than would otherwise have been possible.
4. Using the MuliValue Comparison function.
To use the comparison function is a case of referencing it in the (MDM Studio Rulebase construct) tableSQL function:
tableSQL({ <RepositoryLinkVariable>.<Attribute_D> },
where <CurrentRecord.Attribute_X>> = <RepositoryLinkVariable>.<Attribute_X> and
COMPAREMVSTRING(<EnterpriseName>,
<RepositoryName>,
<MultiValueAttributeName>,
T_STRTAB(<MV1>,<MV2>,<MV3>,...),
<SourceRecord>.PRODUCTID,
<SourceRecord>.PRODUCTIDEXT,
<TargetRecord>.PRODUCTID,
<TargetRecord>.PRODUCTIDEXT,
<LATESTorCONFIRMED>) = 1 )
For example.
tableSQL(varLinkMVR.PRODUCTID,where TYPE = varLinkMVR.TYPE and COMPAREMVSTRING('ExampleEnterprise','ExampleRepository','MULTIVAL',
T_MVTABLE(varMVList),PRODUCTID,varIDEXT,varLinkMVR.PRODUCTID,varLinkMVR.PRODUCTIDEXT) = 1)
Note that such a call fails where varMVList or varIDEXT or both = null. In the worst case, MDM generates a function call of :
COMPAREMVSTRING(<EnterpriseName>,<RepositoryName>,<MultiValueAttributeName>,T_STRTAB(<MV1>,<MV2>,<MV3>,...),<SourceRecord>.PRODUCTID,<TargetRecord>.PRODUCTID,<TargetRecord>.PRODUCTIDEXT,<LATESTorCONFIRMED>)
COMPAREMVSTRING(<EnterpriseName>,<RepositoryName>,<MultiValueAttributeName>,<SourceRecord>.PRODUCTID,<SourceRecord>.PRODUCTIDEXT,<TargetRecord>.PRODUCTID,<TargetRecord>.PRODUCTIDEXT,<LATESTorCONFIRMED>)
COMPAREMVSTRING(<EnterpriseName>,<RepositoryName>,<MultiValueAttributeName>,<SourceRecord>.PRODUCTID,<TargetRecord>.PRODUCTID,<TargetRecord>.PRODUCTIDEXT,<LATESTorCONFIRMED>)
which is then rejected as having the wrong number or type of arguments. MDM does not handle nulls well when generating function calls. To overcome this, if varMVList is null, it is given the list value ('XNOVALUEX'), while if varIDEXT is null, it is given the value 'XNOVALUEX'. The function detects this and replaces the values ('XNOVALUEX') / 'XNOVALUEX' with null.
5). For reference, the function definition, type definition and sample MDM Studio project that makes use of the function COMPAREMVSTRING are attached in the file (Filename: MVAttributeDuplicateCheck.zip).
Issue/Introduction
Using database functions in an MDM Studio project. A useful example for extending MDM to compare multivalued attributes.
Attachments
Feedback
Yes
No
Powered by
