Adding pre-written SQL queries

To add pre-written SQL queries, use the following procedure:

1. Determine which search method should be used in the search statement.
   A search statement is defined by its input criteria and the entity being searched, for example, Person vs. Organization.

   Note: Adding or updating a pre-written SQL statement which handles the same combination of input parameters as an existing searchBy<predefined criteria> method hides the original searchBy<predefined criteria> method. Ensure that the SQL search statement you are creating is unique, unless you intend to override an existing search statement.
2. Write an SQL statement for the search.
3. Identify the search input class that this pre-written SQL statement belongs to.
4. Identify both input search criteria and output search fields for the given pre-written SQL in the CDSRCHFLD table.
5. Insert the data in the following tables for the given SQL:
   • SQLSTATEMENT
   • SEARCHSQL
   • SEARCHCRITERION
   • SEARCHRESULTFIELD

   The following is an example SQL script to insert data into these tables:

   INSERT INTO SQLSTATEMENT VALUES
      (3,
       'SELECT PS.GIVEN_NAME_ONE, PS.GIVEN_NAME_TWO, ...',
       CURRENT TIMESTAMP);
   
   
   INSERT INTO SEARCHSQL VALUES
      (3,
       'com.dwl.tcrm.coreParty.search.TCRMPersonSearchInput',
       3,
       'Search Party by field1, field2, field3, ...',
       'com.dwl.tcrm.coreParty.component.TCRMPersonSearchResultSetProcessor',
       'Y',
       CURRENT TIMESTAMP);
   
   
   INSERT INTO SEARCHCRITERION VALUES (3, 1, 47, 2, 'N', CURRENT TIMESTAMP);
   INSERT INTO SEARCHCRITERION VALUES (3, 2, 48, 1, 'N', CURRENT TIMESTAMP);
   INSERT INTO SEARCHCRITERION VALUES (3, 3, 34, 1, 'N', CURRENT TIMESTAMP);
   INSERT INTO SEARCHCRITERION VALUES (3, 4, 35, 1, 'N', CURRENT TIMESTAMP);
   
   
   INSERT INTO SEARCHRESULTFIELD VALUES (3, 1, 51, CURRENT TIMESTAMP);
   INSERT INTO SEARCHRESULTFIELD VALUES (3, 2, 52, CURRENT TIMESTAMP);
   INSERT INTO SEARCHRESULTFIELD VALUES (3, 3, 53, CURRENT TIMESTAMP);
   INSERT INTO SEARCHRESULTFIELD VALUES (3, 4, 54, CURRENT TIMESTAMP);
   INSERT INTO SEARCHRESULTFIELD VALUES (3, 5, 55, CURRENT TIMESTAMP);
   INSERT INTO SEARCHRESULTFIELD VALUES (3, 6, 43, CURRENT TIMESTAMP);
   COMMIT;

   Important: The insert into SQLSTATEMENT table for DB2® on z/OS® does not work properly if the SQL statement column value is greater than 256 chars. In this case, you must import the data into the SQLSTATEMENT table instead of inserting it.
6. Restart the application servers to allow for the changes to take effect.
7. Test the new search SQL statement by running a search transaction with a set of input parameters, which match the SQL statement criteria.