Release 10.1A: OpenEdge Data Management:
SQL Reference

> ## Quantified Predicate

> The quantified predicate compares a value with a collection of values using a relational operator. A quantified predicate has the same form as a basic predicate with the query_expression being preceded by the ALL, ANY, or SOME keyword. The result table returned by query_expression can contain only a single column.

> When you specify ALL, the predicate evaluates to true if the query_expression returns no values or the specified relationship is true for all the values returned.

> When you specify SOME or ANY, the predicate evaluates to true if the specified relationship is true for at least one value returned by the query_expression. There is no difference between the SOME and ANY keywords. The predicate evaluates to false if the query_expression returns no values or if the specified relationship is false for all the values returned.

> This is the syntax for a quantified_predicate:

> Syntax
>     expression relop { ALL | ANY | SOME } ( query_expression )

> Example

>     10 < ANY ( SELECT COUNT(*)
>              FROM order_tbl
>              GROUP BY custid ;
>               )

> ##### BETWEEN Predicate

> The BETWEEN predicate can be used to determine if a value is within a specified value range or not. The first expression specifies the lower bound of the range and the second expression specifies the upper bound of the range.

> The predicate evaluates to true if the value is greater than or equal to the lower bound of the range and less than or equal to the upper bound of the range.

> This is the syntax for a between_predicate:

> Syntax
>     expression [ NOT ] BETWEEN expression AND expression

> Example

>     salary BETWEEN 20000.00 AND 100000.00

> ##### NULL Predicate

> The NULL predicate can be used for testing null values of database table columns.

> This is the syntax for a null_predicate.

> Syntax
>     column_name IS [ NOT ] NULL

> Example

>     contact_name IS NOT NULL

> ##### LIKE Predicate

> The LIKE predicate searches for strings that have a certain pattern. The pattern is specified after the LIKE keyword in a string constant. The pattern can be specified by a string in which the underscore ( _ ) and percent sign ( % ) characters have special semantics.

> Use the ESCAPE clause to disable the special semantics given to the characters ( _ ) and
> ( % ). The escape character specified must precede the special characters in order to disable their special semantics.

> This is the syntax for a like_predicate:

> Syntax
>     column_name  [  NOT  ] LIKE string_constant  [  ESCAPE escape_character  ]

> #### Note

> - The `column_name` specified in the `LIKE` predicate can be a column, a string constant, or an arbitrary character expression (such as `SUBSTRING` or `LTRIM`).

> - The `string_constant` may be a string constant or a scalar function call.

> - The `escape_character` must be a one character string constant.

> - A percent sign ( % ) in the pattern matches zero or more characters of the column string.

> - A underscore symbol ( _ ) in the pattern matches any single character of the column string.

> - The `LIKE` predicate is multi-byte enabled. The `string_constant` and the `escape_character` may contain multi-byte characters, and the `escape_character` can be a multi-byte character. A percent sign ( % ) or an underscore ( _ ) in the `string_constant` can represent a multi-byte character. However, the percent sign or underscore itself must be the single-byte ASCII encoding.

> Example

> This example illustrates three ways to use the LIKE predicate:

>     cust_name LIKE ’%Computer%’

>     cust_name LIKE ’___’

>     item_name LIKE ’%\_%’ ESCAPE ’\’

> In the first LIKE clause, for all strings with the substring ’Computer’ the predicate evaluates to true. In the second LIKE clause, for all strings which are exactly three characters long the predicate evaluates to true. In the third LIKE clause the backslash character ( \ ) is specified as the escape character, which means that the special interpretation given to the underscore character ( _ ) is disabled. The pattern evaluates to TRUE if the item_name column has embedded underscore characters.

> ##### EXISTS Predicate

> The EXISTS predicate can be used to check for the existence of specific rows. The query_expression returns rows rather than values. The predicate evaluates to true if the number of rows returned by the query_expression is nonzero.

> This is the syntax for an exists_predicate:

> #### Syntax

>     EXISTS (query_expression)

> Example

> In this example, the predicate evaluates to true if the specified customer has any orders:

>     EXISTS (SELECT * FROM order_tbl
>          WHERE order_tbl.custid = ’888’ ;)

> ##### IN Predicate

> The IN predicate can be used to compare a value with a set of values. If an IN predicate specifies a query expression, then the result table it returns can contain only a single column.

> This is the syntax for an in_predicate:

> Syntax
>     expression [ NOT ] IN
>       { (query_expression) | (constant , constant [ , ... ] )  }

> Example

>     address.state IN (‘MA’, ‘NH’)

> ##### OUTER JOIN Predicate

> An outer join predicate specifies two tables and returns a result table that contains all the rows from one of the tables, even if there is no matching row in the other table.

> This is the syntax for an outer_join_predicate:

> Syntax
>     [ table_name.]column = [ table_name.]column (+)
>       | [ table_name.]column (+) = [ table_name.]column

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095