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_alias[ ' ]]
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_alias 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.
Note: A table alias cannot be used to qualify a column alias. A column alias can only be used without a qualifier because it is not a part of any table definition.
[table|alias.]column_name , ...]
Specifies a list columns from a particular table or alias.
Example
Both these statements 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. For example:
SELECT Customer.CustNum, Customer.Name, Invoice.*
FROM Customer, Invoice ;
The following example illustrates using the column_alias 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, as shown:
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
When there is a conflict between a SELECT list alias and an actual database column, OpenEdge SQL interprets the reference as the database column. Note the following example:
SELECT substring (state, 1, 2) state, sum (balance)
FROM pub.customer
GROUP BY state;
In the above query, state is ambiguous because it can refer to either database column pub.customer.state or the result of the substring scalar function in the SELECT list. The ANSI standard requires that state refers unambiguously to the database column, therefore, the query groups the result by the database column. The same principle holds true for ambiguous references that appear in WHERE, ON, and HAVING clauses.
