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.
R:BASE supports "COUNT = LAST" in two different methods. 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 added 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.
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 in a table by the current user, even if it has been modified by another user. The COUNT=INSERT condition can be used with a single-table view, but not with a multi-table view. If there is not a newly inserted row in the table, then COUNT=INSERT performs the same action as COUNT=LAST, and fetches the current end row of the table. |
COUNT=LAST |
Refers to the last row in a table. The COUNT=LAST condition can be used with a single-table view, but not with a multi-table 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 chr. In the string, use chr in front of the wildcard character. |
colname CONTAINS 'string ' |
True if a column value contains the text string. The text string can contain R:BASE wildcard characters. |
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. |
Notes:
•Placing NOT before most text operators (such as NULL or BETWEEN) reverses their meaning.
•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 condition. If you set AND off, conditions are always evaluated from left to right.
WHERE Builder
When launching the WHERE Clause Builder, the following window will appear:
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.
