SELECT

SELECT (Short Name: SEL)

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 it's 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 nested SELECT command (a sub-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 1 to 18 character 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
Currenly 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 Clause" later in this entry.

=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 Clause" later in this entry.

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