Sample: Searching with SQL queries

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 = ?

The following diagram shows how the preceding SQL can be represented using the classes provided by the search framework.

Depicts the SQL statement in this topic using the classes provided by the search framework

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 = ?)

This SQL statement would be matched on only when exactly 2 categories were specified as search criteria, and requires that the SAME_CRITERION_SEQ value of the category name search criterion be specified (and unique, starting from 1, incremented by 1).

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 = ?)

This requires a single CRITERIONELEMENT to be specified with a SAME_CRITERION_SEQ value of null or 1 to be specified.

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.