Release 10.1A: OpenEdge Data Management:
SQL Reference

> ## COLUMN_LIST clause

> Specifies which columns to retrieve by the `SELECT` statement.

> #### Syntax

>     [ ALL | DISTINCT ]
>       { * | { table_name | alias.}* [ , { table_name.| alias.} * ] ...
>          | expr [ [ AS ] [ ' ] column_title [ ' ] ]
>             [, expr [ [ AS ] [ ' ] column_title [ ' ] ] ] ...
>          |  [ table| alias.]column_name , ... ]
>     }

> `[ ALL | DISTINCT ]`

> Indicates whether a result table omits duplicate rows. ALL is the default and specifies that the result table includes all rows. `DISTINCT` specifies that a table omits duplicate rows.

> `* | { table_name. | alias. } *`

> Specifies that the result table includes all columns from all tables named in the FROM clause.

> `* expr [ [ AS ] [ ' ] column_title [ ' ] ]`

> Specifies a list of expressions, called a select list, whose results will form columns of the result table. Typically, the expression is a column name from a table named in the `FROM` clause. The expression can also be any supported mathematical expression, scalar function, or aggregate function that returns a value.

> The optional column_title argument specifies a new heading for the associated column in the result table. You can also use the column_title in an `ORDER` `BY` clause. Enclose the new title in single or double quotation marks if it contains spaces or other special characters, including hyphens.

> `[ table | alias.]column_name , ... ]`

> Specifies a list columns from a particular table or alias.

> Examples

> Both these statement return all the columns in the customer table to the select list:

>     SELECT * FROM Customer;

>     SELECT Customer.* FROM Customer;

> The table_name.* syntax is useful when the select list refers to columns in multiple tables and you want to specify all the columns in one of those tables:

>     SELECT Customer.CustNum, Customer.Name, Invoice.*
>          FROM Customer, Invoice ;

> The following example illustrates using the column_title option to change the name of the column:

>     -- Illustrate optional 'column_title' syntax
>     SELECT
>             FirstName AS 'First Name',
>             LastName AS 'Last Name',
>             state AS 'New England State'
>     FROM Employee
>             WHERE state = 'NH' OR state = 'ME' OR state = 'MA'
>     OR state = 'VT' OR state = 'CT' OR state = 'RI';

>     First Name       Last Name        New England State
>     ---------------- ---------------- ------------------
>     Justine          Smith            MA
>     Andy             Davis            MA
>     Marcy            Adams            MA
>     Larry            Dawsen           MA
>     John             Burton           NH
>     Mark             Hall             NH
>     Stacey           Smith            MA
>     Scott            Abbott           MA
>     Meredith         White            NH
>     Heather          White            NH

> You must qualify a column name if it occurs in more than one table specified in the `FROM` clause:

>     SELECT Customer.CustNum FROM Customer;

>     -- Table name qualifier required
>     -- Customer table has city and state columns
>     -- Billto table has city and state columns

>     SELECT
>             Customer.CustNum,
>             Customer.City AS 'Customer City',
>             Customer.State AS 'Customer State',
>             Billto.City AS 'Bill City',
>             Billto.State AS 'Bill State'
>     FROM Customer, Billto
>             WHERE Customer.City = 'Clinton';

>      CustNum Customer City    Customer State   Bill City        Bill State
>     -------- ---------------- ---------------- -------------------------------
>       1272     Clinton          MS               Montgomery         AL
>       1272     Clinton          MS               Atlanta            GA
>       1421     Clinton          SC               Montgomery         AL
>       1421     Clinton          SC               Atlanta            GA
>       1489     Clinton          OK               Montgomery         AL
>       1489     Clinton          OK               Atlanta            GA

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