WHERE

WHERE (Short Name: WHER)

Use a WHERE clause in commands to qualify or restrict the rows affected by a command.

Options

AND
Indicates the following condition must be met along with the preceding condition.

condition
Identifies requirements to be in the WHERE syntax.

NOT
Reverses the meaning of a connecting operator. AND NOT, for example, indicates that the first condition must be met and the following condition must not be met.

OR
Indicates the following condition can be met instead of the preceding condition.

About the WHERE Clause

In most commands, a WHERE clause follows the syntax diagram above. The JOIN command does not use that syntax diagram because it uses only comparison operators in a WHERE clause to compare two columns.

The two main elements in any WHERE clause are conditions and connecting operators.

We now support "COUNT = LAST" in two different ways. If the entire WHERE clause is "WHERE COUNT = LAST" then R:BASE works like it always has to quickly fetch the last row of the table. The NEW functionality is to have other conditions in the WHERE clause and you want the last row of whatever qualifies.

To make it work this way specify the other conditions and then add "AND COUNT = LAST".

Here is an example:

SELECT * FROM Customer WHERE CustID > 100 AND COUNT = LAST

WHERE Clause Conditions

The following syntax diagram and table show the basic formats for WHERE clause conditions, which can be used alone or together.

Note: Placing NOT before most text operators (such as NULL or BETWEEN) reverses their meaning.

Basic WHERE Clause Conditions

Condition Syntax
Description

colname op DEFAULT
True if a column value compares correctly with the DEFAULT value for the column. Op can be =, <>, >=, >, <=, or <.

colname = USER
True if a column value equals the current user identifier.

item1 IS NULL
True if item1 has a null value. Item1 can be a column name, value, or expression. A null value cannot be used in a comparison with an operator.

item1 op item2
True if the relationship between two items is true as defined by an operator. Item1 can be a column name, value, or expression; item2 can be a column name, value, expression, or sub-SELECT statement.

COUNT=INSERT
Refers to the last row inserted by the current user, even if it has been modified by another user.

COUNT=LAST
Refers to the last row in a table. COUNT cannot be used with a view.

COUNT op value
Refers to a number of rows defined by op and value.

LIMIT=value
Specifies a number of rows affected by a command. A LIMIT condition should be the last condition in a WHERE clause.

EXISTS (sub-SELECT statement)
True if sub-SELECT statement returns one or more rows.

item1 BETWEEN item2 AND item3
True if the value of item1 is greater than or equal to the value of item2, and if the value of item1 is less than or equal to the value of item3.

colname LIKE 'string '
True if a column value equals the text string. With LIKE, a string can also be a DATE, TIME, or DATETIME value. The text string can contain R:BASE wildcard characters.

colname LIKE 'string ' ESCAPE 'chr '
True if a column value equals a text string. If you want to use a wildcard character as a text character in the string, specify the ESCAPE character 1chr. In the string, use chr in front of the wildcard character.

colname CONTAINS 'string '
True if a column value contains the text string.

colname SOUNDS 'string '
True if the soundex value of a column matches the soundex value of the text string.

item1 IN (vallist)
True if item1 is in the value list.

item1 IN (sub-SELECT statement)
True if item1 is in the rows selected by a sub-SELECT.

item1 op ALL (sub-SELECT statement)
True if the relationship between item1 and every row returned by a sub-SELECT statement matches an operator.

item1 op ANY(sub-SELECT statement)
True if the relationship between item1 and at least one value returned by a sub-SELECT statement matches an operator.

item1 op SOME (sub-SELECT statement)
ANY and SOME are equivalent.

Note: When a SELECT statement is part of a WHERE clause, it is called a sub-SELECT clause. A sub-SELECT clause can contain only one column name (not a column list or *), expression, or function. The INTO and ORDER BY clauses in a sub-SELECT are ignored.

You can only use the current wildcard characters to compare a column to a text value when using the LIKE comparison. The default wildcard characters are the percent sign (% ), which is used for one or more characters, and the underscore (_), which is used for a single character.

If you compare a column with a value, you can either enter the value or specify a global variable. If you specify a variable, R:BASE compares the column with the current value of the variable.

To significantly reduce processing time for a WHERE clause, use INDEX processing. To use indexes, the following conditions must be met:

A condition in the WHERE clause compares an indexed column.

If the WHERE clause contains more than one condition, R:BASE selects the condition that places the greatest restriction on the WHERE clause.

Conditions are not joined by the OR operator.

The comparison value is not an expression.

Connecting Operators

When you use more than one condition in a WHERE clause, the conditions are connected using the connecting operators AND, OR, AND NOT, and OR NOT.

The connecting operator AND requires that both conditions it separates must be satisfied. The connecting operator OR requires that either condition it separates must be satisfied.

The connecting operator AND NOT requires that the preceding condition must be satisfied, and the following condition must not be satisfied. The connecting operator OR NOT requires that either the preceding condition must be satisfied, or any condition except the following condition must be satisfied.

In WHERE clauses with multiple conditions, conditions that are connected by AND or AND NOT are evaluated before those connected by OR or OR NOT. However, you can control the order in which conditions are evaluated by either placing parentheses around conditions or using the SET AND command. If you set AND off, conditions are always evaluated from left to right.

Examples

The following WHERE clause chooses sales amounts that are less than the value of a variable containing the daily average.

... WHERE amount < .dailyave

The following WHERE clause specifies the seventh row.

... WHERE COUNT = 7

The following WHERE clause specifies each row from the employeetable that contains both the first name June and the last name Wilson.

SELECT * FROM employee WHERE empfname = 'june' AND emplname = 'wilson'

The following WHERE clause selects dates in the actdate column that are greater than dates in the begdate column or are less than dates in the enddate column.

... WHERE actdate BETWEEN begdate AND enddate

The next three WHERE clauses use the following data:

empfname emplname
-------- --------
    Mary Jones
    John Smith
   Agnes Smith
    John Brown

In both of the following clauses, R:BASE first evaluates the conditions connected by AND, selecting John Smith. Then R:BASE adds any Marys to the list because the connecting operator is OR. The final result includes John Smith and Mary Jones.

...WHERE empfname = 'Mary' OR empfname = 'John' +
AND emplname = 'Smith'

...WHERE empfname = 'Mary' OR (empfname = 'John' +
AND emplname = 'Smith')

By moving the parentheses around the conditions connected by OR, you can select only John Smith. In the following WHERE clause, the first name can be either Mary or John, but the last name must be Smith.

...WHERE (empfname = 'Mary' OR empfname = 'John') AND +
emplname = 'Smith'

The following example illustrates a sub-SELECT in a WHERE clause. Assume you wanted a list of all sales representatives that had transactions greater than $100,000, and the information for such a list was contained in two tables, employee and transmaster. The relevant columns in these tables are:

employee transmaster
empid  emplname empid    netamount
----- --------- ----- ------------
  102 Wilson      133   $32,400.00
  129 Hernandez   160    $9,500.00
  133 Coffin      129    $6,400.00
  165 Williams    102  $176,000.00
  166 Chou        160  $194,750.00
  167 Watson      129   $34,125.00
  160 Smith       131  $152,250.00
  131 Simpson     102   $87,500.00
  102                   $22,500.00
  102                   $40,500.00

131 $108,750.00

  131                   $80,500.00
  129                   $56,250.00
  102                   $57,500.00
  160                  $140,300.00
  129                   $95,500.00
  129                  $155,500.00
  133                   $88,000.00
  131                  $130,500.00
  102                    $3,060.00
  165                    $3,060.00
  167                    $3,830.00
  133                   $12,740.00
  165                   $26,310.00

To display a list of employees in the transmaster table with a transaction larger than $100,000, enter the following command:

SELECT empid, emplname FROM employee WHERE empid IN +
   (SELECT empid FROM transmaster WHERE netamount > 100000)

R:BASE displays the following list:

    empid emplname
--------- ----------------
      102 Wilson
      129 Hernandez
      131 Simpson
      160 Smith

Note: You can use a sub-SELECT in any command that allows a full WHERE clause.

Condition Syntax	Description
colname op DEFAULT	True if a column value compares correctly with the DEFAULT value for the column. Op can be =, <>, >=, >, <=, or <.
colname = USER	True if a column value equals the current user identifier.
item1 IS NULL	True if item1 has a null value. Item1 can be a column name, value, or expression. A null value cannot be used in a comparison with an operator.
item1 op item2	True if the relationship between two items is true as defined by an operator. Item1 can be a column name, value, or expression; item2 can be a column name, value, expression, or sub-SELECT statement.
COUNT=INSERT	Refers to the last row inserted by the current user, even if it has been modified by another user.
COUNT=LAST	Refers to the last row in a table. COUNT cannot be used with a view.
COUNT op value	Refers to a number of rows defined by op and value.
LIMIT=value	Specifies a number of rows affected by a command. A LIMIT condition should be the last condition in a WHERE clause.
EXISTS (sub-SELECT statement)	True if sub-SELECT statement returns one or more rows.
item1 BETWEEN item2 AND item3	True if the value of item1 is greater than or equal to the value of item2, and if the value of item1 is less than or equal to the value of item3.
colname LIKE 'string '	True if a column value equals the text string. With LIKE, a string can also be a DATE, TIME, or DATETIME value. The text string can contain R:BASE wildcard characters.
colname LIKE 'string ' ESCAPE 'chr '	True if a column value equals a text string. If you want to use a wildcard character as a text character in the string, specify the ESCAPE character 1chr. In the string, use chr in front of the wildcard character.
colname CONTAINS 'string '	True if a column value contains the text string.
colname SOUNDS 'string '	True if the soundex value of a column matches the soundex value of the text string.
item1 IN (vallist)	True if item1 is in the value list.
item1 IN (sub-SELECT statement)	True if item1 is in the rows selected by a sub-SELECT.
item1 op ALL (sub-SELECT statement)	True if the relationship between item1 and every row returned by a sub-SELECT statement matches an operator.
item1 op ANY(sub-SELECT statement)	True if the relationship between item1 and at least one value returned by a sub-SELECT statement matches an operator.
item1 op SOME (sub-SELECT statement)	ANY and SOME are equivalent.