The following examples illustrate how the search framework classes define the structure of a SQL statement.
Simple example
In this example, InfoSphere® MDM is searching for a person's last name, given name one and party ID, using parts of the person's address information: the address line 1, city name and the province code.
The following SQL statement can
be used to perform this search transaction.
SELECT P.CONT_ID, P.LAST_NAME, P.GIVEN_NAME_ONE
FROM PERSONNAME P, ADDRESS A, LOCATIONGROUP LG, ADDRESSGROUP AG
WHERE P.CONT_ID = LG.CONT_ID
AND LG.LOCATION_GROUP_ID = AG.LOCATION_GROUP_ID
AND AG.ADDRESS_ID = A.ADDRESS_ID
AND A.ADDR_LINE_ONE LIKE ?
AND A.CITY_NAME = ?
AND A.PROV_STATE_TP_CD = ?Complex example
In the
following example, the search framework is set up to search for products
given a product type id and several category names. There are two
ways in which multi-occurring search criteria can be specified in
the predefined SQL. One is to explicitly identify each repeated element,
as in the following example SQL:
SELECT PROD.NAME, PROD.PRODUCT_TYPE_ID, PROD.SHORT_DESCRIPTION,
PROD.PROD_STRUC_TP_CD, PROD.STATUS_TP_CD, PROD.PRODUCT_ID
FROM PRODUCT PROD, CATEGORY CAT, PRODUCTCATEGORYASSOC PCASS
WHERE PROD.PRODUCT_ID = PCASS.PRODUCT_ID
AND CAT.CATEGORY_ID = PCASS.CATEGORY_ID
AND PROD.PRODUCT_TYPE_ID = ?
AND (CAT.NAME = ? OR CAT.NAME = ?)If,
however, you wish for the multi-occurring attribute (also known as
repeatable criteria) to be of an unspecified number and simply wish
for them to be ORed with each other (as is typically the default behavior
for multi-occurring fields), use the “/*<” and “>*/” place
holders in the prewritten SQL. This SQL will be reconstructed using
what is specified within these placeholders. So if the following SQL
is configured:
SELECT PROD.NAME, PROD.PRODUCT_TYPE_ID, PROD.SHORT_DESCRIPTION,
PROD.PROD_STRUC_TP_CD, PROD.STATUS_TP_CD, PROD.PRODUCT_ID
FROM PRODUCT PROD, CATEGORY CAT, PRODUCTCATEGORYASSOC PCASS
WHERE PROD.PRODUCT_ID = PCASS.PRODUCT_ID
AND CAT.CATEGORY_ID = PCASS.CATEGORY_ID
AND PROD.PRODUCT_TYPE_ID = ?
AND /*<CAT.NAME=?>*/If four category names are present
as search criteria, then the following SQL would result before the
query is executed:
SELECT PROD.NAME, PROD.PRODUCT_TYPE_ID, PROD.SHORT_DESCRIPTION,
PROD.PROD_STRUC_TP_CD, PROD.STATUS_TP_CD, PROD.PRODUCT_ID
FROM PRODUCT PROD, CATEGORY CAT, PRODUCTCATEGORYASSOC PCASS
WHERE PROD.PRODUCT_ID = PCASS.PRODUCT_ID
AND CAT.CATEGORY_ID = PCASS.CATEGORY_ID
AND PROD.PRODUCT_TYPE_ID = ?
AND (CAT.NAME = ? OR CAT.NAME = ? OR CAT.NAME = ? OR CAT.NAME = ?)Note: Errors
will result in the application if you try to mix the preceding two
variations for the same criteria name. That is, for example, the two
independent strings, "CAT.NAME = " and /*<CAT.NAME=?>*/,
must never appear within the same SQL statement and the category name
criterion elements must be set up for only one or the other type.