SQL lookup constraints
In order for the pre-written SQL statement to be successfully fetched in the FETCH algorithm, there are a number of points to keep in mind while designing the search SQL statements.
- The SQL lookup algorithm only considers the search input parameters passed into the search business object to perform the lookup.
- A pre-written SQL will only be selected if each non-null and non-blank input parameter passed into the request has a corresponding criterion element in the SQL criteria and the SQL does not contain any additional non-supplementary criterion elements.
- A criterion element may appear multiple times in the SQL. The algorithm only uses the unique collection of criterion elements to look up the SQL and ignores the duplicate instances of the same criterion element. The same input parameter is used while executing the query as a value for all instances of that criterion element.
- Lookup only considers the criterion element including the name and the comparison operator. Other elements of the SQL criteria are not considered. For example, the combination operator (AND, OR), and the order of criterion elements are not considered during the lookup.
- If there are multiple SQL statements with the same collection of criterion elements, InfoSphere® MDM selects the first one in the list, and ignores the other SQL statements. This scenario should be avoided.
The following table provides examples of the algorithm results
for different combinations of search input parameters and the SQL
criteria.
| # | Input Parameters | SQL Criteria | Match | Comments |
|---|---|---|---|---|
| 1 | [A,=] -see note [B,=] |
[B,=] [A,=] |
Yes | Order of elements is not relevant. |
| 2 | [A,LIKE] [B,=] |
[B,=] [A,=] |
No | A's comparison operator is different |
| 3 | [A,=] [B,=] |
[B,=] [C,=] [A,=] |
No | C is a non-supplementary criterion whose input parameter is not provided |
| 4 | [A,=] [B,=] |
[B,=] [C,=,supplementary] [A,=] |
Yes | All non-supplementary elements are provided. |
| 5 | [A,=] [B,=] [C,=] |
[C,=] [A,=] |
No | B does not have a criterion element. |
| 6 | [A,=] [B,=] |
[A,=] [B,=] [B,=] |
Yes | B is duplicate but the unique collection of criterion elements has a complete match. |
Note: Where A is the search field name, for example, last
name or address line or date of birth, and "=" is the comparison operator.
These
constraints are only valid if the SQL is included in the pre-written
SQL collection. If the SQL is being dynamically constructed based
on the input parameters, the constraints do not apply.