Using the Relational Data Framework › Use the WHERE Clause to Select Values

Use the WHERE Clause to Select Values

There are various tools that you can use to perform searches using the WHERE clause. Following is a list of each of these tools:

• A predicate is an expression that can be true or false and can contain either uppercase or lowercase characters. You can specify various types of predicates, including comparison predicates, the IN predicate, and the LIKE predicate.
• The substring function allows you to retrieve parts of character strings that are to be used as search criteria. The substring function may be used in conjunction with the predicates previously mentioned.
• A join allows you to select values from multiple tables to be used in your search. You need only specify the table names in your statement; the joining of the tables actually occurs internally in CA OPS/MVS.
• A subquery allows you to perform a query function that may be nested in another expression. Subqueries appear in parentheses in an SQL statement, and they can use all of the tools previously mentioned.
• Aggregate functions perform simple numeric calculations, mostly on values in a specified column in a table.

To select values in a WHERE clause

Use the following syntax:

WHERE
   [NOT]{predicate}
   [AND|OR [NOT] predicate]

Example: WHERE Clause

Suppose you want all rows from the APPLICATIONS table where the last updated date is equal to 2009-02-13 and the status is equal to UP. To select these rows, use this WHERE clause:

WHERE UPDATE = DATE '2009-02-13' AND STATUS = 'UP'

By including the NOT operator, you can use a WHERE clause to find all rows except those that were last updated on February 13, 2009:

WHERE NOT UPDATE = DATE '2009-02-13'