Progress
SQL-92
Guide and 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 taht 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.

[ 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.customer_id 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 © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095