Please enable JavaScript to view this site.

R:BASE 11 Help

Navigation: Command Index > S > SELECT

WHERE

Scroll Prev Top Next More

This clause determines which rows of data to include.

 

Sel_wher

 

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.

 

WhereCond

 

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 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.