6.3.1 SELECT Command

The SELECT command allows you to extract rows from a table, according to optional selection rules.

Syntax:

SELECT [DISTINCT] <field-list-1>
 FROM <table-list>
 [WHERE <condition>]
 [GROUP BY <field-list-2>]
 [HAVING <condition-2>]
 [ORDER BY <field-list-3>]
 [FOR UPDATE <field-list-4>]

where

<field-list-1>

is the list of fields that must be selected. Every field must be written according to the following syntax:

[<table-name>.]<field-name>
      [,[<table-name>.]<field-name>][, ...]]

All the fields present in this list must belong to one of the tables indicated in <table-list>. You must specify <table-name> only when two or more <field-name>s have been declared with the same name in two different tables.

If desired, you may replace <field-list-1> with the character "*" (star), which means "all the fields of every table present in <table-list>."

<table-list>

is a list of tables containing fields specified in <field-list-1> and/or in <condition>.

The format is:

<table-name>
      [,<table-name> [, ...]]

If you omit the WHERE clause, the output of a SELECT statement is a new table containing all of the rows of the tables selected. The columns of this new table are the fields specified in <field-list-1>, while the number of rows are equal to the product of the rows of each table present in <table-list>.

In other words, all the rows of a table are associated with each row of the other tables present in <table-list>. So if you have two tables, the first containing 100 rows and the second one with ten, the result of a SELECT statement, without the WHERE clause, is a table containing 1000 rows (100 * 10).

If you had three tables, the first with 100 rows, the second with 1000, and the third with 10, the result would be a table with 1,000,000 rows (100 * 1000 * 10).

AcuODBC also supports a variety of join statements, including inner joins, left outer joins, right outer joins, and self-joins.

An inner join is a SELECT statement that joins two or more tables together. The format for <table-list> when creating inner joins is:

<table-name-1> INNER JOIN <table-name-2>
  ON <field-name-1>=<field-name-2>

For example, to join the tables "customer" and "orders":

SELECT *
 FROM customer INNER JOIN orders
  ON customer.cs_code = orders.or_customer_code;

A left outer join is a SELECT statement that retains unmatched rows from Table 1 when joining two tables, but discards unmatched rows from Table 2. The format for <table-list> when you are creating left outer joins is:

<table-name-1> LEFT OUTER JOIN <table-name-2>
 ON <field-name-1>=<field-name-2>

Note that the "OUTER" clause is required when making outer joins with AcuODBC. For example:

SELECT *
 FROM customer LEFT OUTER JOIN orders
 ON customer.cs_code = orders.or_code

A right outer join is the opposite of a left outer join; namely, it is a SELECT statement that retains unmatched rows from Table 2 when joining two tables, but discards unmatched rows from Table 1.

The format is the same for right outer joins as for left outer joins:

<table-name-1> RIGHT OUTER JOIN <table-name-2>
 ON <field-name-1>=<field-name-2>

Finally, a self-join is a SELECT statement that joins a table to itself. It is useful for comparing values within a single column. To create a self-join, list the table twice in the FROM clause, assigning it a different alias each time. Use the aliases to refer to the table in the SELECT and WHERE clauses as if it were two tables.

The format for <table-list> when you are creating self-joins is:

<table-name>[,<table-name> AS <table-alias>]
   [,<table-name>[,<table-name> AS <table-alias>][, ...]]

For example:

SELECT DISTINCT orders1.or_date, orders1.or_quantity
 FROM orders AS orders1, orders AS orders2
  WHERE orders1.or_date=990409 and orders1.or_code=orders2.or_code

AcuODBC does not support the use of full outer joins.

<condition>

consists of a logical expression aimed at identifying a useful set of lines among all the lines generated by the product of the tables. It can be expressed in the following way:

[NOT] <field>|<literal> <operator> <field>|<literal>
[AND|OR <condition>]
[NOT EXISTS <condition>]
[EXISTS <condition>]

Example:

SELECT *
FROM ORDERS
WHERE EXISTS
     ( SELECT OR_VALUE
       FROM ORDERS
       WHERE OR_VALUE > 900000)

<field>

is a field present in one of the tables listed in <table-list>.

<literal>

is a numeric or alphanumeric constant. If it is alphanumeric it must be enclosed between quotes (' or ").

<operator>

is a logic operator included in the following list:

Logic Operator	Meaning
=	Equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<> or !=	Not equal to
LIKE	Contains
BETWEEN	Included between two values
IN	Included in a list of values
NOT IN	Not included in a list of values

<field-list-2>

Using the GROUP BY clause, you can group records that have identical values in columns. The list of fields specified after the GROUP BY clause specifies which columns have to be considered in order to form groups.

<condition-2>

is used to qualify groups the way <condition-1> works to qualify records. Syntax and operators are the same specified for <condition-1>.

<field-list-3>

is a list of fields belonging to tables present in <table-list>. These fields must be written according to the following syntax:

[<table-name>.]<field-name> [DESC]
   [[<table-name>.]<field-name> [DESC][, ...]]

This option allows you to sort tables according to selected fields. By default, fields are ordered from lowest to highest value, but if the data file has an embedded collating sequence, AcuODBC honors that sequence instead.

The option DESC specifies that fields must be ordered in reverse ordering.

If desired, you can use standard aggregate functions in the field-list of an ORDER BY clause.

<field-list-4>

is a list of fields you want selected for update. These fields must belong to tables present in <table-list>, and they must be written according to the following syntax:

[<table-name>.]<field-name> [DESC]
   [[<table-name>.]<field-name> [DESC][, ...]]

To select as many records as you want for update, you can use the SELECT FOR UPDATE statement. For example:

SELECT * from ARTICLES
WHERE AR_CODE = 4
FOR UPDATE OF AR_DESCRIPTION

Standard Aggregate Functions

AcuODBC supports a series of standard aggregate functions that are specified inside a SELECT command on selected fields. The field-names to which these functions apply must follow the name of the function and be enclosed in parentheses.

The result of an aggregate function is unique for each selected group. If a group is not selected (i.e., the GROUP BY clause is not used), the result is computed on the full table.

The standard aggregate functions are:

Function	Description
COUNT	Counts the number of values present in a column.
COUNT(*)	Returns the number of rows of a table.
COUNT(field-name)	Returns the number of not null values stored in the column "field-name".
SUM	Computes the sum of values present in a column.
AVG	Computes the arithmetic average of the values present in a column.
MAX	Returns the highest value present in a column.
MIN	Returns the lowest value present in a column.
UPPER	Returns column values in upper case (for conditions only).
LOWER	Returns column values in lower case (for conditions only).

AcuODBC supports the following string functions inside a SELECT command:

Function	Description
ASCII	Changes a character string to its corresponding ASCII value.
CHAR	Changes a numeric string to an character string.
CONCAT	Concatinates two strings together.
LCASE	Changes a character string to lower case.
LENGTH	Returns the length of the string.
LOCATE	Locates a character in a string.
LTRIM	Removes leading spaces from a string.
RTRIM	Removes trailing spaces from a string.
SUBSTRING	Locates a string within a string.
TRUNCATE	Truncates a string wherever you specify.
UCASE	Changes a character string to upper case.

SELECT ORDERS.OR_ARTICLE_CODE, string (ORDERS.OR_QUANTITY) FROM ORDERS

Arithmetic Functions

AcuODBC supports the use of arithmetic functions inside a SELECT command, including the following:

Function	Example
Expressions in the SELECT list	SELECT col1 + col2 FROM table1
Expressions in functions	SELECT max(col1 + col2) FROM table1
Expressions as part of a BETWEEN predicate	SELECT col1, col2 FROM table1 WHERE col1 BETWEEN col2 + 5 and col2 + 10
Expressions as part of comparisons	SELECT col1, col2 FROM table1 WHERE col1 + col2 > 10
Expressions as the test in an IN clause	SELECT col1 FROM table1 WHERE col1 + col2 IN (10, 20, 30, 40)
Expressions in a LIKE predicate	SELECT col1, col2 FROM table1 WHERE col1 + 10 LIKE 100%

For detailed instructions on the SELECT command and its various clauses, refer to an ODBC or SQL reference manual, like the "ODBC Programmer's Reference."

Examples:

The following examples are designed to illustrate SQL concepts. They are not made from the sample data that is shipped with AcuODBC.

Input table number 1 -> CUSTOMERS

id_cust	Company	Address	City
1	Acucorp Inc	8515 Miralani Drive	San Diego
2	Acucorp Germany	Otto-Hahn-Strasse 9	Friedrichsdorf Koeppern
3	Acucorp Italia	Via I Maggio 3	Piacenza
4	Acucorp Scandinavia	Rasundavagen 101	Solna

id_order	id_ordcust	Date	Quantity
1	2	15/01/99	15
2	4	16/01/99	23
3	2	19/01/99	12
4	4	19/01/99	33
7	4	27/01/99	14
8	2	27/01/99	25

Example 1. If you execute the following SQL command:

SELECT company, id_order, date, quantity
 FROM customers, orders
 WHERE id_cust = id_ordcust

the following table results:

Company	id_order	Date	Quantity
Acucorp Germany	1	15/01/99	15
Acucorp Germany	3	19/01/99	12
Acucorp Germany	8	27/01/99	25
Acucorp Italia	5	22/01/99	16
Acucorp Italia	6	25/01/99	21
Acucorp Scandinavia	2	16/01/99	23
Acucorp Scandinavia	4	19/01/99	33
Acucorp Scandinavia	7	27/01/99	14

Example 2. If you execute the following SQL command:

SELECT company, id_order, date, quantity
 FROM customers, orders

the following table results:

Company	id_order	Date	Quantity
Acucorp Inc	1	15/01/99	15
Acucorp Inc	2	16/01/99	23
Acucorp Inc	3	19/01/99	12
Acucorp Inc	4	19/01/99	33
Acucorp Inc	5	22/01/99	16
Acucorp Inc	6	25/01/99	21
Acucorp Inc	7	27/01/99	14
Acucorp Inc	8	27/01/99	25
Acucorp Germany	1	15/01/99	15
Acucorp Germany	2	16/01/99	23
Acucorp Germany	3	19/01/99	12
Acucorp Germany	4	19/01/99	33
Acucorp Germany	5	22/01/99	16
Acucorp Germany	6	25/01/99	21
Acucorp Germany	7	27/01/99	14
Acucorp Germany	8	27/01/99	25
Acucorp Italia	1	15/01/99	15
Acucorp Italia	2	16/01/99	23
Acucorp Italia	3	19/01/99	12
Acucorp Italia	4	19/01/99	33
Acucorp Italia	5	22/01/99	16
Acucorp Italia	6	25/01/99	21
Acucorp Italia	7	27/01/99	14
Acucorp Italia	8	27/01/99	25
Acucorp Scandinavia	1	15/01/99	15
Acucorp Scandinavia	2	16/01/99	23
Acucorp Scandinavia	3	19/01/99	12
Acucorp Scandinavia	4	19/01/99	33
Acucorp Scandinavia	5	22/01/99	16
Acucorp Scandinavia	6	25/01/99	21
Acucorp Scandinavia	7	27/01/99	14
Acucorp Scandinavia	8	27/01/99	25

Example 3. If you execute the following SQL command:

SELECT company, id_order, date, quantity
 FROM customers, orders
 WHERE id_cust = id_ordcust AND id_cust = 4
 ORDER BY quantity

the following table results:

Company	id_order	Date	Quantity
Acucorp Scandinavia	7	27/01/99	14
Acucorp Scandinavia	2	16/01/99	23
Acucorp Scandinavia	4	19/01/99	33

SELECT company, Sum(quantity)
 FROM  customers, orders
 WHERE id_cust = id_ordcust
 GROUP BY company

Company
Acucorp Germany	52
Acucorp Italia	37
Acucorp Scandinavia	70

SELECT company, Count(quantity) AS '#Ord', Sum(quantity) AS 'Tot_Ord',
Min(quantity) AS 'Min', Max(quantity) AS 'Max'
 FROM customers, orders
 WHERE id_cust = id_ordcust
 GROUP BY company

Company	# Ord	Tot_Ord	Min	Max
Acucorp Germany	3	52	12	25
Acucorp Italia	2	37	16	21
Acucorp Scandinavia	3	70	14	33

SELECT company, Avg(quantity) AS Avrg', Count(quantity) AS 'Count'
FROM customers, orders
WHERE id_cust = id_ordcust
GROUP BY company
HAVING (Count(*)>2)

Company	Avrg	Count
Acucorp Germany	17.33333	3
Acucorp Scandinavia	23.33333	3