The following examples describe situations when you need to create a WHERE clause to specify the rows of data to use.
Example 1: Listing customer transaction information for February
You can view the TransMaster table using the Data Browser. However, the customers are identified by the customer ID number, not the company name, and we only want to see transactions for February. The WHERE clause for this example must list all the rows in the TransMaster table where the transaction date is in February, and must also list the corresponding company column.
First, to view transactions with a company name, link the TransMaster table and the Customer table--we want to identify who made each transaction. The customer ID number in the TransMaster table matches a customer ID in the Customer table--the link is the custid column. (By default, when you select two tables, R:BASE automatically links them, and this part of the WHERE clause is automatically entered. )
Next, to view transactions for a specific month, specify a date range for which to list transactions, or where the transaction date (the transdate column) is between February 1st and February 28th.
Note: In the WHERE clause, T1 refers to the first table in the clause, T2 refers to the second table, and so on.
Example 2: Listing customer transaction information for February
Another way of specifying this date range is to use a function--the IMON function looks at the number of the month (January is 1, February is 2, and so on). An alternative WHERE clause can use this function to list transactions for February, or where the month number of the transdate column is 2.
Example 3: Listing all companies that start with "C"
To list all customers (companies) that start with a "C," create a WHERE clause that uses a wild card. The WHERE clause for this example lists the rows in the Customer table where the company column starts with "C." The "%" operator means more than one character. Therefore, R:BASE lists all rows where the value in the company column starts with a "C" and is followed by one or more characters.
Example 4: Listing all sales for two employees (Coffin and Wilson)
To link employees to their sales transactions, link the Employees table and the TransMaster table. Next, specify the employees whose sales you want to list: Coffin and Wilson.
The WHERE clause for this example uses the empid column (employee ID) to link the Employees table and the TransMaster table, then lists transactions that are linked to employees with the last name "Coffin" or "Wilson"--the value of the emplname column. Note that the parentheses treat this part of the clause as a single condition.
