The WHERE Builder dialog box creates a WHERE clause. A WHERE clause allows you to specify which rows of data to use.
For example, you can display the contents of the entire customer table. However, if you wanted to display data for only those customers who live in Washington state, you must use a WHERE clause to specify this. In this case, the WHERE clause would indicate that you want to list all the rows in the customer table where the state column is equal to 'WA'.
The Where Builder allows you to create a WHERE clause two ways. You can select columns from tables and choose operators (=, +, & and so on) by pointing and clicking, or if you are familiar with the WHERE command and syntax, you can enter the actual WHERE clause text in the space provided.
Tables
Table Name - Select the table from which you want information
Select Columns - Select the column from which you want information
Add Column - Click this button to accept the column you selected and enter it in the WHERE clause.
Function Templates - If you want to use a function, select it from this list.
Use - Click this button to add the function to the WHERE clause, then enter the variables as needed in the "Where Clause" panel. For example, if you choose the function ABS(arg), you must enter a value for "arg".
Operators and Keywords - Click on the following operators and keywords as needed:
Operator/Keyword |
Description |
= |
Equal to |
<> |
Not equal to |
<= |
Less than or equal to |
>= |
Greater than or equal to |
< |
Less than |
> |
Greater than |
+ |
Addition/Concatenation |
- |
Subtraction |
* |
Multiplication |
/ |
Division |
( |
Left parenthesis |
) |
Right parenthesis |
& |
Concatenation (a space between values) |
and |
The condition following AND must be met in addition to the condition before it |
or |
Either the condition before OR or the condition following it must be met |
in |
Value of column exists in selected rows |
not |
Negative |
like |
Column equals the string following LIKE |
contains |
Contains the string following CONTAINS |
between |
Greater than or equal to value 1 and less than or equal to value 2 |
is null |
Column contains no value (null) |
is not null |
Column contains any value |
WHERE Clause - The WHERE clause appears in this section. The WHERE clause conditions may be edited directly within the panel.
ORDER BY Clause - The ORDER BY clause appears in this section. The ORDER BY clause may be edited directly within the panel, or built by selecting the "ORDER BY" button.
GROUP BY Clause - The GROUP BY clause appears in this section. The GROUP BY clause may be edited directly within the panel, or built by selecting the "GROUP BY" button.
HAVING Clause - The HAVING clause appears in this section. The HAVING clause may be edited directly within the panel.
Test Clause - The WHERE clause may be tested for mistakes or problems with the syntax.
Data Dictionary - The green book button across the bottom may be used to launch the Data Dictionary, where the information may be captured to the clipboard.
OK - returns to the originating source from which the WHERE Builder was launched
Cancel - closes the WHERE Builder
Help - launches the R:BASE Help covering the WHERE Builder
ARRANGE BY
When running a form or printing a report/label which contains slave tables, the WHERE Builder will include the functionality to specify "ARRANGE TableName BY ColumnName" parameters. To add an ARRANGE BY to the WHERE clause select the table from the list of selected tables in the Table Name drop-down list. After you pick the desired table, the "ARRANGE BY" button is enabled.
The "ARRANGE <TableName> BY" dialog may be used to order the columns in slave tables. Select a column from the list, choose whether the sort order is "Ascending" or "Descending", and press the "Add" button. Repeat this for all columns you wish to have ordered.
After selecting the columns and their desired sort order, you can rearrange them by selecting a column in the "Current Selection" list, and using the "Move Up" or "Move Down" button respectively. If you wish to change the sort order for a selected column, right click on the column to display the speed menu for changing the order. You can also remove an unwanted sort on a column by selecting the column from the "Current Selection" list, and pressing the "Remove" button.
