Progress
SQL-89
Guide and Reference
SELECT Statement
Retrieves and displays data from a table or join. For more information on the SELECT statement, see Data Manipulation Language."
ALL
Retrieves all values in the specified columns. ALL is the default.
DISTINCT
Retrieves only unique sets of values (tuples) in the specified columns.
*
Indicates all columns of the specified table(s).
column-list
Specifies the names of the columns to retrieve. The syntax of the column-list is as follows.
For the syntax of format-phrase, see the Format Phrase reference entry in the Progress Language Reference.
Each column can also be an aggregate function. Table A–1 lists the aggregate functions Progress/SQL supports.
INTO variable-listLists the local program variables to receive the column values (singleton SELECT).
The variable-list consists of a comma-separated list of variable references. Each variable reference has the following syntax.
Each indicator-variable must be an integer. If a NULL value is fetched for a field, the indicator variable is set to -1. If a character field must be truncated to fit in the variable, the indicator variable is set to the original (untruncated) length of the value. Otherwise, if the selection succeeds, it is set to 0. You cannot use indicator variables for array fields.
Each expression is an integer array subscript. To specify a subrange of an array you can specify FOR n, where n is an integer constant.
You cannot use the GROUP BY or HAVING clauses with SELECT INTO.
FROM
Specifies the table or join from which to select data.
NOTE: Progress/SQL does not support 4GL buffer names in SQL FROM phrases.table-name
Specifies the name of a table or view from which to select data.
correlation-name
Specifies an alias for a table name, useful for specifying joins between a table and itself.
implicit-join
Specifies multiple tables from which to select data using an implied inner join. This is the syntax for an implicit-join.
Any SELECT statement can specify an implicit-join, including those used in subqueries. You can specify both table join conditions and selection criteria for each table in the join using the SELECT statement WHERE option. A table-name in an implicit-join can specify either a table or a view.
For more information on implicit joins, see Data Manipulation Language."
explicit-join
Specifies multiple tables from which to select data using a specific type of join. This is the syntax for an explicit-join.
An explicit-join can contain either a combination of inner and left outer joins of multiple tables or a single right outer join between two tables. You can specify an explicit-join only in a direct SELECT statement or in a cursor SELECT specified in a DECLARE CURSOR statement. Thus, you cannot specify an explicit-join in a subquery (SELECT in a WHERE clause), CREATE VIEW statement, or INSERT INTO statement.
NOTE: A table-name in an explicit-join cannot specify a view.JOIN by default or with the INNER option specifies an inner join. JOIN with the LEFT [OUTER] option specifies a left outer join, and JOIN with the RIGHT [OUTER] option specifies a right outer join.
The ON clause specifies both table join conditions and any additional selection criteria for each table in the join. The search-condition is one or more relational or logical expressions connected by a logical operator (AND, OR, or NOT). For an explicit-join, the WHERE clause of the SELECT statement specifies additional selection criteria on the complete join result and plays no part in building the join itself.
NOTE
- Unlike the WHERE clause, the search-condition for the ON clause cannot include a subquery.
For more information on explicit joins, see Data Manipulation Language."
WHERE search-condition
Specifies the conditions for selecting data from tables and joins. For an implicit-join, these conditions can specify table join criteria and the selection criteria for each table in the join. For an explicit-join, these conditions specify selection criteria on the join result and play no part in building the join itself.
The search-condition is one or more relational or logical expressions connected by a logical operator (AND, OR, or NOT). Each expression can include a subquery (nested SELECT). For information on subqueries, see Data Manipulation Language."
GROUP BY
column[ , column ]
Groups rows that have the same value for the specified column or columns. Each column can be a reference to a column in the table or an expression. The GROUP BY clause produces a single row for each group of rows sharing the same column values.
HAVING search-condition
Specifies one or more qualifying conditions, normally for the GROUP BY clause. The HAVING clause allows you to exclude groups from the query results.
ORDER BY sort-criteria
Sorts the query results by the values in one or more columns. The syntax of sort-criteria is as follows.
In this syntax, n represents an integer that specifies the position of a column or expression in the select list. Use this option to sort on arithmetic expressions included in the select list. Each expression represents a column name or other value by which to sort the results. This might be a calculated expression involving one or more table columns.
NOTE
ASC
Sorts the query results in ascending order. Ascending order is the default.
DESC
Sorts the query results in descending order.
frame-options
These options specify the overall layout and processing properties of a frame. See the Frame Phrase reference entry in the Progress Language Reference for information about the options.
You cannot use frame options in a SELECT statement within a UNION statement.
STREAM stream
Specifies the name of a stream. If you do not name a stream, Progress uses the unnamed stream. See the DEFINE STREAM Statement reference entry in the Progress Language Reference and the Progress Programming Handbook for more information about streams.
You cannot use STREAM in a SELECT statement within a UNION statement.
EXPORT
Converts data to a standard character format and displays it to the current output destination (except when the current output destination is the screen) or to a named output stream.
You cannot use EXPORT in a SELECT statement within a UNION statement.
EXAMPLESThis example retrieves each distinct (unique) country from the customer table.
This example retrieves certain fields from the order table if the ship-date field has a null value.
This example retrieves information about customers whose balance is greater than that of each customer for sales rep JAL. The procedure lists the customers retrieved in descending order of balance.
NOTES
- You can use the SELECT statement in interactive SQL and ESQL.
- In ESQL only, you must use a cursor with the SELECT statement unless you specify the INTO option to perform a singleton SELECT.
- The SELECT statement can also have one or more subqueries.
- For each row selected, the server returns the subset of the available columns specified by column-list plus any additional columns that the client requires to complete the query. For more information on how Progress might determine the additional columns in this subset, see the section on field lists in the Progress Programming Handbook and the Progress DataServer Guide for any DataServers you use.
SEE ALSO
CREATE VIEW Statement, DECLARE CURSOR Statement, INSERT INTO Statement, UNION Statement, UPDATE Statement
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |