Creating searches that contain a JOIN

As with any other search, you can name, save, and retrieve the search variables.

The procedure for a joined search differs from the procedure described for other types of complex searches. Observe the following general rules when creating a search that includes a JOIN:

To create a search that contains a JOIN

1. Perform a search for an object.For more information, see Using-SEARCH-to-generate-lists-based-on-object-attributes.
2. On the Search panel, do not enter any values in the Column Name fields that see the object attributes.
3. In the Edit a WHERE clause field, enter Y.

4. Complete the WHERE field as follows:

   1. Type a comma ( , ) as the first character.

   2. Type the qualified names of the additional catalog tables to be included, separated by commas.

      Note

      Because some tables have identical column names and the column names in a join must be unique, type a correlation identifier after each qualified table name.

      An example follows:

      , SYSIBM.SYSTABLES B
   3. Type the WHERE operator and WHERE clause. For example:

     , SYSIBM.SYSTABLES B
   WHERE A.DBNAME IN ('DSNDB01','DSNDB06')       
     AND A.IBMREQD<>  'Y'                        
     AND A.TBNAME = B.NAME                       
     AND A.TBCREATOR = B.CREATOR                 
     AND B.TSNAME NOT IN ('SYSPKAGE','SYSPLAN')

   Note

   Remember that you must use correct SQL case, punctuation, syntax, and wildcards that are acceptable to Db2 whenever you use the WHERE clause. For the default object, use a correlation identifier of A.