Progress
SQL-89
Guide and Reference


Selecting Single Rows

The SELECT INTO statement retrieves only one row of the table. This is also called a singleton SELECT. You can use the SELECT INTO statement instead of declaring a cursor and fetching one row through it. The SELECT INTO statement is commonly used for aggregate functions.

Here is the general syntax for the SELECT INTO statement.

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 ] 

The SELECT INTO statement assumes that the WHERE clause evaluates to only one row. If you know that multiple rows exist with the same values, specify DISTINCT to prevent an error. ALL is the default. The variable-list is a list of procedure variables separated by commas. There must be one variable for each column or aggregate function in the column-list. The data types of the variables and columns must be compatible. You can use the same name to specify procedure variables and SQL columns. All other elements of the syntax are the same as for the SELECT statement.

In the following example, the SELECT INTO statement retrieves the name and state for customer number 10.

DEFINE VARIABLE namevar LIKE Customer.Name.
DEFINE VARIABLE statevar LIKE Customer.State.

SELECT Name, State INTO namevar, statevar
  FROM Customer
  WHERE Cust-Num = 10. 

You can use a SELECT INTO statement instead of a cursor to retrieve aggregate values that are not grouped, because such queries return only a single row.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095