SQL is a powerful tool for querying data from iSeries databases. Part of what makes SQL so powerful is its predicates, used to select certain rows or groups of rows. A predicate, according to IBM's documentation, "specifies a condition that is true, false, or unknown about a given row or group." In an SQL SELECT phrase, the predicate is what is usually coded with the WHERE and HAVING clauses.

So, what are the predicates? There are several, giving us a wide variety of ways to select or reject data.

The Basic Predicate

The basic predicate is a simple comparison of two values:

The relation operator (rel-op) is one of these:

• = Equal to
• <> Not equal to
• < Less than
• <= Less than or equal to
• > Greater than
• >= Greater than or equal to

The two expressions must be of the same data type. An expression can be a subselect that returns one row with one column.

The expressions are evaluated and then compared according to rel-op. The predicate will be true or false, unless one or both of the expressions evaluate to null; in this case, the predicate is unknown. Either expression can contain host variables.

The Quantified Predicate

The quantified predicate compares the value of an expression to each of a group of values from a subselect. It uses this form:

The subselect selects a single column/value and returns any number of rows, including zero rows (an empty subselect). Expression1 is then compared to the values of all the rows, using a quantifier:

• ALL--If the quantifier is ALL, the predicate is true if the comparisons between the expression and each of the values are all true. The predicate is also true if the subselect is empty.
• ANY--The predicate is true if the comparison between the expression and at least one of the values is true. The predicate is false if the subselect is empty.

To better understand quantified predicates, consider the following simple quantified predicate:

Let's say the subselect returns a table of five rows: (2, 4, 6, 8, 10). We are using ALL. So in order for the predicate to be true, VAR must be greater than or equal to (>=) each value in the subselect's table. If VAR is 15, the predicate is true, because 15 is greater than or equal to 2, 4, 6, 8, and 10. If VAR is 9, then the predicate is false because it is not greater than or equal to 10 even though it is greater than or equal to 2, 4, 6, and 8. The comparison between VAR and the values does not evaluate to true for ALL the values.

Now consider this:

In order for the predicate using ANY to be true, VAR must be greater than or equal to (>=) at least one of the values in the table. If VAR is 9, the predicate is true, since VAR is greater than or equal to four of the values. If VAR is 1, the predicate is false, because VAR is not greater than or equal to ANY of the values.

SOME can also be used as a quantifier; it is the same as ANY.

For an ALL predicate, if there are no false relationships and any of the values are null, the predicate is unknown. For ANY, if there are no true relationships and any of the values are null, the predicate is unknown.

The BETWEEN Predicate

This predicate compares an expression to a range of expression values.

The predicate is true if Expression1 is between ExpressionA and ExpressionB. In other words, Expression1 >= ExpressionA and Expression1 <= ExpressionB.

You can use NOT BETWEEN as well. In this case, the predicate is true if Expression1 is not within the range.

Here's an example:

If VAR is 5, the predicate is true. If VAR is 15, the predicate is false.

If VAR is 5, the predicate is false. If VAR is 15, the predicate is true.

The DISTINCT Predicate

This predicate is similar to the basic predicate, with the following differences:

• Only equality and inequality are determined.
• Null values are accounted for, so this predicate will not be unknown.

This predicate is true if Expression1 has a different value than Expression2. If one expression but not the other is null, the predicate is true; these are different values. If both expressions are null, the predicate is false; these are the same value.

This predicate is true if Expression1 has the same value as Expression2. If one expression but not the other is null, the predicate is false; these are different values. If both expressions are null, the predicate is true; these are the same value.

The EXISTS Predicate

This predicate simply tests to see if a subselect returns rows.

If the subselect returns rows, the predicate is true. If the subselect returns zero rows, the predicate is false. The values of the rows don't matter.

You can use NOT EXISTS as well. In this case, the predicate is true if the subselect does not return rows.

This is a good predicate to use to select only rows from a table where matching rows exist in another table:

In this statement, rows in FileA are selected only if the value in field KeyA can be found in the field KeyB in FileB.

The IN Predicate

This predicate determines whether an expression can be found among a set list of values. There are two forms. The first form explicitly lists the set of values to check:

If Expression1 evaluates to any of the values in the set, the predicate is true.

The other form is a set produced by a subselect:

The subselect selects a single column/value and returns any number of rows. If Expression1 is equal to any of the values in the result set, the predicate is true. This predicate is equivalent to the quantified predicate:

You can use NOT IN as well. In this case, the predicate is true if Expression1 does not evaluate to any of the values in the set.

Here's an example:

If VAR is 6, the predicate is true.

If VAR is 6, the predicate is false.

The LIKE Predicate

This predicate matches a string to a string pattern. This is the format:

If Expression1 matches the Pattern, the predicate is true.

You can use NOT LIKE as well. In this case, the predicate is true if Expression1 does not match the Pattern.

The string pattern has wildcard characters to define how the string should match:

• _ The underscore represents one character. The character could be anything.
• % The percent sign represents zero or more characters.
• Any other character, including the space, represents itself.

Consider the string 'Donovan McNabb'. The following patterns would match:

• 'D_n_v_n McN_bb'--Each underscore represents a character (the vowels in this case).
• 'D%'--The letter D followed by zero or more characters.
• 'D%M%'--The letter D followed by zero or more characters, followed by the letter M, followed by zero or more characters.
• '% _%'--Zero or more characters followed by a space, followed by one character, followed by zero or more characters.
• '%McNabb'--Zero or more characters followed by McNabb.
• '%M_Nabb%'--Zero or more characters followed by the letter M, followed by any one character, followed by zero or more characters.

The following patterns would not match:

• '%McNabb '--Why not? The string has no spaces at the end. This pattern has a space at the end. So the pattern is looking for a space at the end that isn't in the string.
• '%M__Nabb%'--Why not? There is only one character between the M and Nabb, not two.
• 'DONOVAN%'--Why not? The pattern is case-sensitive.
• 'McNabb%'--Why not? There is no wildcard at the beginning of the pattern, and the string does not begin with McNabb.

What if you need to look for an underscore or a percent sign in the string? In other words, you can't use it as a wildcard because it is actually in the string? Then you use this form of the LIKE predicate:

The EscapeChar is a single character used as an escape character. When you need to look for the existence of the underscore, the percent sign, or the escape character itself in the string, precede it with the escape character.

Consider the string 'Tom_Brady'. Assume your escape char is a backslash (). The pattern 'Tom_%' would mean Tom, followed by any single character, followed by zero or more characters. 'Tom_Brady' would match, but so would 'Tom Brady' or 'Tom*Brady'.

However, the string contains an actual underscore, and if you wanted to include that in your pattern, the pattern would be 'Tom_%'. Because of the escape character, the underscore becomes a literal underscore that has to exist in the string, instead of a wildcard. The pattern now would mean 'Tom', followed by the underscore, followed by zero or more characters. 'Tom_Brady' would match this pattern, but 'Tom Brady', and 'Tom*Brady' would not.

Remember, trailing spaces in a pattern are part of the pattern. If a string does not have the same trailing spaces, it will not match. This becomes an issue if you use a host variable for the pattern. The best way around it is to pad your host variable with the percent symbol (%). For example, a 10-character host variable VAR would contain 'Donovan%%%' or 'Tom%%%%%%%'.

The NULL Predicate

This predicate compares an expression to the null value.

If Expression1 is null, the predicate is true.

You can use IS NOT NULL as well. In this case, the predicate is true if Expression1 is not null.

Combining Predicates

Predicates can be combined using AND and OR. The results of the combined predicates are shown here, from the truth table from IBM's documentation. P and Q are any predicates.


(Click image to enlarge.)

Predicates are SQL's powerful means of selecting rows of data from your files. Hopefully, this review will help you to make use of them. Query away!

Doug Eckersley is the iSeries programmer with a premier homebuilder in Columbus. He has been programming on the iSeries for 10 years and has been in the business for 15. He is certified by IBM. He is also a long-suffering Philadelphia Eagles fan.

Also Read

TechTip: Prevent Unintentional Deletion of Objects with RESTRICT ON DROP

SQL 101: DDL Recap—Improving Our Basic SQL Tables, Part 2

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions