Use the SELECT command to display rows of data from a table or view. To display the data in the order you want, modify the SELECT command by using various clauses.
The SELECT command is a very powerful data retrieval command. By learning this command, and all of its parts you can greatly enhance your ability to work with any other R:BASE command that uses those same portions. For example, learning to use a WHERE clause with SELECT will help you work with WHERE clauses on other commands.
You can use the SELECT command to do the following:
•Display rows of information from a table or view
•Extract information from a table or view by using a sub-SELECT command (a nested SELECT statement) in a WHERE command
•Extract information from a table or view by using a SELECT clause in another command
A SELECT command is essentially a process of elimination. A SELECT command can contain a number of clauses (two are required), each of which begins with a keyword, such as FROM or WHERE.
The diagram below shows the different clauses in a SELECT command.
Each of the SELECT clauses has a specific purpose for determining what data you want. The operators are processed in the order in which they appear in the preceding diagram.
Note:
•Many of the SELECT clauses use the same options, such as expression or colname. These common options are described only once in "SELECT Command Clause" below.
SELECT Command Clause
The required SELECT command clause specifies which columns to include. You can:
•Select all columns by entering SELECT with an asterisk.
•Name the columns you want to select.
•Use expressions and SELECT functions to perform calculations whose results will also appear as a column in the final result.
•Select the column or expression values and load them into variables.
Syntax:
Options
*
Specifies all columns.
,
Indicates that this part of the command is repeatable.
ALL
Specifies all rows returned by the other clauses.
#c
Specifies a column, where #c is the column number shown in the output of the LIST TABLES command. You can enter a table or correlation name before the #c.
colname
Specifies a column name. In a command, you can enter #c, where #c is the column number shown when the columns are listed with the LIST TABLES command. In an SQL command, a column name can be preceded by a table or correlation name and a period (tblname.colname). You can enter tblname.* to specify all columns in the table.
corr_name
Correlation name. A nickname or alias for a table or view name. Use corr_name to refer to the same table twice within the command, or to explicitly specify a column in more than one table.
dbname
Currently connected database name, plus the drive and directory if the database is not on the current directory. It has the form D:\PATHNAME/DBNAME where D: is the optional drive letter, /PATHNAME is the optional directory path, and /DBNAME is the database name.
DISTINCT
Eliminates duplicate rows from the resulting data set.
(expression)
Determines a value using a text or arithmetic formula. The expression can include other columns from the table, constant values, functions, or system variables such as #date, #time, and #pi.
FROM
Lists the tables from which data is to be displayed.
ind_var
Specifies a variable result indicator to be used with an INTO clause in a SELECT command. This variable stores the status of the variable: non-null (0) or null (-1).
INDICATOR
Indicates the following variable is an indicator variable, which is used to indicate if a null value is retrieved.
INTO
Selects information directly from a table and puts it into variables. You must include a WHERE clause so the SELECT command finds only one row.
into_var
Specifies a variable whose value is assigned with an INTO clause in a SELECT command.
ORDER BY clause
Sorts rows of data. See ORDER BY.
=S
Calculates the sum of a column that has CURRENCY, DOUBLE, INTEGER, NUMERIC, or REAL data type values, or the results of an expression using CURRENCY, DOUBLE, INTEGER, NUMERIC, or REAL data type values.
tblview
Specifies a table or view name.
USER
Retrieves the current user as a constant.
=w
Specifies a display width.
WHERE clause
Limits rows of data. See WHERE.
Examples
The following command selects the company name and ID for companies in Washington state:
SELECT custid, company FROM customer +
WHERE custstate = 'WA' ORDER BY company
custid company
122 Data Solutions
119 Datacrafters Infosystems
130 MIS by Design
114 Softech Database Design
The following command selects the first, fourth, and fifth columns from the Employee table:
R>SELECT #1, #4, #5 FROM Employee
EmpID EmpFName EmpLName
---------- ---------- ----------------
101 Arielle Wilson
102 Ernest Hernandez
103 Mary Simpson
104 Peter Coffin
105 John Smith
106 Darnell Williams
107 John Chow
108 Jane Sullivan
109 John Smith
110 Sam Donald
111 Joe Donohoe
115 Craig Alan
