SELECT

SELECT   [ DISTINCT ]  variable_list
FROM     [ tabfile.  ] table_name,     |
         [ tabfile.  ] view_name ,     |
         [ database.] record_name,     |
         [ database.] CIR ,            |
         path_name ,                   |
         [AS] alias_name,

Output

If variables are selected from multiple records or rows, one output row is written for each individual data record or row which exists and which meets the selection criteria. For example the following results in one output row per combination of employee and review:

SELECT NAME POSITION RATING -
FROM EMPLOYEE REVIEW -
WHERE EMPLOYEE.ID = REVIEW.ID

SELECT ....  FROM A B OUTER B
SELECT ....  FROM A B OUTER A
SELECT ....  FROM A B OUTER A B

DISTINCT

SELECT DISTINCT SALARY FROM EMPLOYEE

Aggregation

SELECT AVG(SALARY) FROM EMPLOYEE

The following functions are aggregation functions and alter the number of output rows produced:

SELECT .....  FROM EMPLOYEE GROUP BY CURRPOS

SELECT CURRPOS COUNT(SALARY) SUM(SALARY) FROM EMPLOYEE -
GROUP BY CURRPOS

Variable List

Computing New Values

SELECT ID NAME SALARY*12/52 FROM EMPLOYEE

Numeric expressions may use numeric constants, numeric variables, the arithmetic operators ( + , - , / , * , ** ), SQL functions, and parentheses to denote the order of operations.

Character expressions may use quoted strings, variables which are strings, the "+" character to join strings, SQL string functions, and parentheses to denote the order of operation. Enclose strings in single quotes.

Functions

FROM

There must be common values in some corresponding columns between the data sources in the FROM clause. For example, to create a table of employee name and position level for each position ever held by the employee:

SELECT NAME POSITION FROM EMPLOYEE OCCUP -
WHERE  EMPLOYEE.ID = OCCUP.ID

PATHS

...  WHERE OCCUP.POSITION = REVIEW.POSITION

If CASE mode is set, records are joined when the case identifier variable in one record is equal to the case identifier in another. This is equivalent to specifying a WHERE clause such as:

...  WHERE RECONE.CASEID = RECTWO.CASEID

For example, the default path joining the OCCUP and REVIEW records in the sample database is equivalent to the WHERE clause:

...  WHERE OCCUP.ID EQ REVIEW.ID AND OCCUP.POSITION EQ REVIEW.POSITION

If PATHS are explicitly referenced in the FROM clause, the setting of CASE and PATH have no effect, the PATH is always applied as defined.

Joins

A join condition can specify other relationships between columns, such as "greater than" (GT or >), "less than" (LT or <), etc. These are referred to as Non-Equi-Joins. For example, suppose a table is created of minimum and maximum starting salaries by division:

SELECT DIVISION MIN(STARTSAL) MAX(STARTSAL) -
FROM OCCUP -
ON DIVSAL -
GROUP BY DIVISION -
FORMAT COL 2 NAME MINSAL -
FORMAT COL 3 NAME MAXSAL

SELECT ID NAME SALARY FROM EMPLOYEE REVIEW DIVSAL -
WHERE SALARY GT MAXSAL OR SALARY LT MINSAL

Non-equi-joins on large tables can produce a tremendous number of rows and the WHERE conditions should be carefully examined to limit the output to the required combinations.

Alias

The alias is defined in the record or table specification in the FROM clause and is used wherever the table or record name would be used in other parts of the SELECT statement.

When an alias is defined, it follows the record name or the optional keyword AS and must be followed by a comma (,) to separate it from any other record names.

For example, suppose a genealogical database where everybody is in a PERSON record, and each person has an ID. In each person's record are the IDs of their father and mother. Because this involves joining a record type to itself, use an alias:

SELECT CHILD.ID FATHER.ID MOTHER.ID -
FROM   PERSON AS CHILD,  PERSON AS FATHER, PERSON AS MOTHER -
WHERE  CHILD.FATHERID EQ FATHER.ID AND -
       CHILD.MOTHERID EQ MOTHER.ID

Keywords

FORMAT

FORMAT COLUMN column_list  column_format

Column References

Specify a list or range of consecutive columns by specifying the start and end columns separated by a colon (:).

Examples of column specifications might be as follows:

FORMAT COLUMN SALARY option
FORMAT COLUMN 1 option
FORMAT COLUMN 3:7 option
FORMAT COLUMN 1 4 8 option
FORMAT COLUMN SALARY option
FORMAT COLUMN BIRTHDAY option

Column Formats

The column formats are specified as a keyword and a setting as follows:

         [  DATE 'date_map' ]
         [  DPLACES n ]
         [  EXPONENT n ]
         [  LABEL ON | OFF ]
         [  MISSCHAR 'c' ]
         [  NAME column_name ]
         [  NULL 'string']
         [  SEPARATOR 'string' | n BLANKS ]
         [  TIME'time_map' ]
         [  VALLAB ON | OFF ]
         [  WIDTH n ]
         [  ZEROS ON | OFF | 'string']

GROUP BY

SELECT VALLAB(GENDER) AVG(SALARY) FROM EMPLOYEE -
GROUP BY GENDER

VALLAB(GENDER)  AVG(SALARY)
Male              2745.83
Female            2831.25

SELECT VALLAB(GENDER) VALLAB(EDUC) AVG(SALARY) -
FROM EMPLOYEE GROUP BY GENDER EDUC

VALLAB(GENDER)        VALLAB(EDUC)              AVG(SALARY)
Male                  Elementary                  2533.33
Male                  High School                 2500.00
Male                  Some University             2550.00
Male                  B.Sc.  or B.A.              3050.00
Male                  M.S.                        2625.00
Male                  Ph.D.                       3350.00
Female                High School                 2600.00
Female                Some University             2700.00
Female                B.Sc.  or B.A.              3533.33
Female                M.S.                        1650.00
Female                Ph.D.                       2400.00

SELECT VALLAB(EDUC) VALLAB(GENDER) COUNT(SALARY) AVG(SALARY) -
FROM EMPLOYEE GROUP BY EDUC GENDER HAVING AVG(SALARY) > 2500

Case Aggregation

SELECT ID AVG(STARTSAL) FROM OCCUP

CLEAR CASE
SELECT AVG(STARTSAL) FROM OCCUP

Case mode is equivalent to a GROUP BY clause on the case id for the aggregation functions.

For example, the following query computes the mean salary and the number of all male employees.

SELECT AVG(SALARY) COUNT(SALARY) FROM EMPLOYEE -
WHERE GENDER = 1

ON

If an output table is not specified with the ON clause, a table called PREVIOUS_SELECT is used.

All tables are on tabfiles. If a tabfile is not specified, the default is used. If a default tabfile has not been explicitly SET, a tabfile called $SYSTEM is used.

If all defaults are used, the results of a SELECT are stored on the table $SYSTEM.PREVIOUS_SELECT. This table is overwritten as necessary without prompting for confirmation. If you specify a table as output which already exists, you are prompted for confirmation that you want it overwritten before proceeding with the SELECT.

You cannot select ON to a table you are selecting FROM.

ORDER BY

The sort keys may contain variables and expressions. The sort key variables do not have to be in the variable list of the SELECT.

DISPLAY does not resequence rows and ORDER BY must be specified on the SELECT to create the table in a particular sequence if this differs from the source data. For example:

SELECT ID NAME SALARY FROM EMPLOYEE  -
ORDER BY SALARY NAME

OUTER

Normally, a join operation creates a row for the resultant table if there exists a record for every record name in the FROM clause. The OUTER option allows the retrieval of some data even when some records may not exist. This operation is called an outer join. The keyword OUTER is specified after all records in the FROM clause and specifies all record names that the OUTER applies to.

SELECT NAME REVDATE FROM EMPLOYEE REVIEW  OUTER REVIEW 

UNION

The main SELECT command defines the table that is produced when using the UNION statement. For example, assume two tables, one for current employees and one for ex-employees. A single output table for all employees could be produced with the UNION clause:

SELECT NAME SALARY FROM EMPLOYEE ON ALLEMPLOYEES -
       UNION  SELECT NAME SALARY FROM EXEMPLOYEE

As many SELECT clauses as required may be UNIONed together as long as the rules on number and type of variables are followed.

If sequence of the output table is important, use the ORDER BY clause to specify it. Any such ORDER BY should follow the last clause of the last SELECT in the command.

WHERE

Relational operators

EQ or = or IS

Equal to

NE or ><

Not equal to

LT or <

Less than

LE or <=

Less than or equal to

GT or >

Greater than

GE or >=

Greater than or equal to

BETWEEN expr AND expr

Between or equal to the values of two expressions

IN (expr,......)

Equal to one of the values in a list

LIKE

Matches a specified character pattern

EQ NULL

Is missing

NE NULL

Is not missing

The logical operator NOT can be used to test for the opposite of any condition.

SELECT ...  FROM EMPLOYEE WHERE ID IN (1,5,7)

SELECT .... WHERE  NOT ( ID IN (1, 5, 7))

LIKE Pattern Matching

Subqueries

SELECT NAME GENDER EDUC FROM EMPLOYEE -
WHERE EDUC = -
(SELECT EDUC FROM EMPLOYEE WHERE NAME = 'Mary Black')

The data comes from multiple cases and case structure must be off (CLEAR CASE) for this query to operate as required. With a case structured database and subqueries which retrieve data from one case which is used to SELECT other cases, CLEAR CASE mode.

Subqueries can be used wherever a WHERE can be specified.

The previous example shows the most basic use of a subquery - one that returns a single value. If a subquery can return more than one value, specify how the returned values are treated in the WHERE clause with the IN, ANY and ALL functions. ANY tests against any returned value; ALL tests against every returned value. These can be used with the relational operators (EQ, NE, LT, GT, LE, GE). IN tests a value to be equal to a value in the list of returned values and is equivalent to EQ ANY.

Where a subquery can return more than one value, this is equivalent to a list. For example, to select people whose salary is greater than anyone whose current position is in division 1:

SELECT ID NAME SALARY FROM EMPLOYEE -
WHERE SALARY GT ALL  -
(SELECT SALARY FROM EMPLOYEE OCCUP -
WHERE CURRPOS EQ POSITION AND DIVISION EQ 1)

A WHERE clause can contain a combination of conditions and subqueries. Any subquery either returns a single value or a list and can be treated as equivalent to a value or list specified by expressions which are not subqueries. The logical operators (AND, OR, XOR, NOT) are used to connect separate clauses in the WHERE expression.

The logical function EXISTS tests that the subquery returns at least one row. EXISTS returns "True" if at least one row exists, "False" if not. The test can be reversed with the NOT logical operator. For example:

SELECT ID NAME SALARY FROM 1 WHERE -
EXISTS (SELECT * FROM REVIEW WHERE RATING = 5)

Note: This query could have been performed more easily and more efficiently as a simple join although this would return multiple rows for people who had received multiple ratings of 5:

SELECT ID NAME SALARY FROM EMPLOYEE REVIEW -
          WHERE RATING EQ 5