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."

SYNTAX
SELECT
  [ ALL | DISTINCT ]
  { * | column-list }
  [ INTO variable-list ]
  FROM {   table-name [ correlation-name ]
         | implicit-join
         | explicit-join
       }
  [ WHERE search-condition ]
  [ GROUP BY column [ , column ] ... ]
  [ HAVING search-condition ]
  [ ORDER BY sort-criteria ]
  [ WITH [ frame-options ] [ STREAM stream ] [ EXPORT ] ] 

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.

SYNTAX
    column [ format-phrase ] 
      [ , column [ format-phrase ] ] ... 

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.

Table A–1: SQL Aggregate Functions 
Function
Description
AVG( [ DISTINCT ] expression)
Calculates an average value for all rows in the result list. The expression can refer to a column or a calculation.
COUNT(*)
Counts the number of rows in the result list. This count includes duplicate rows.
COUNT(DISTINCT expression)
Counts the number of rows with different values for expression.
MAX( [ DISTINCT ] expression)
Returns the maximum value of expression for all rows (or for all distinct rows that you specify).
MIN( [ DISTINCT ] expression)
Returns the minimum value of expression for all rows (or for all distinct rows that you specify).
SUM( [ DISTINCT ] expression)
Calculates the sum of expression for all rows (or for all distinct rows that you specify). The expression must evaluate to a numeric value.

INTO variable-list

Lists 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.

SYNTAX
variable [ [ expression [ FOR n ] ] ]
  [ [ INDICATOR ] indicator-variable
      [ [ expression [ FOR n ] ] ] ] 

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.

SYNTAX
    table-name [ correlation-name ] 
      [ , table-name [ correlation-name ] ] ... 

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.

SYNTAX
FROM table-name [ correlation-name ] 
  [ INNER | LEFT [ OUTER ] ] 
  JOIN table-name [ correlation-name ] ON search-condition
  [ [ INNER | LEFT [ OUTER ] ]
    JOIN table-name [ correlation-name ] ON search-condition
  ] ... 

  table-name [ correlation name ] RIGHT [ OUTER ]
    JOIN table-name [ correlation-name ]
      ON search-condition ... 

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

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.

SYNTAX
{ n | expression } [ ASC | DESC ]
[ , n | , expression [ ASC | DESC ] ] ... 

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.

EXAMPLES

This example retrieves each distinct (unique) country from the customer table.

SELECT DISTINCT Country FROM Customer. 

This example retrieves certain fields from the order table if the ship-date field has a null value.

SELECT Cust-Num, Order-Num, Order-Date
  FROM Order
  WHERE Ship-Date IS NULL. 

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.

SELECT Cust-Num, Name, Sales-Rep, Balance
  FROM Customer WHERE Balance > All
    (SELECT Balance FROM Customer WHERE Sales-Rep = "JAL")
  ORDER BY Balance DESC. 

NOTES

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