Use the SELECT statement to compose queries. The SELECT statement
consists of the following components:
Select list
INTO clause
FROM clause
WHERE clause
GROUP BY clause
HAVING clause
ORDER BY clause
The select list and FROM clause are required; all other components
of this statement are optional.
The following example does not contain an INTO clause. Note the reference
numbers identifying the above components:
1
|
____________|________________
| |
| |
SELECT PartNumber, COUNT(VendorNumber)
FROM PurchDB.SupplyPrice ---3
WHERE DeliveryDays < 25 ---4
GROUP BY PartNumber ---5
HAVING COUNT(VendorNumber) > 2 ---6
ORDER BY PartNumber ---7
|
The result is presented in the form of a table, called a
query result. The result table (shown next) for this example has two
columns: part numbers and a count of vendors who supply each part.
The query result has rows only for parts that can be delivered in fewer
than 25 days by more than two suppliers. The rows are
ordered in ascending order by PartNumber.
----------------+-----------
PARTNUMBER |(EXPR)
----------------+-----------
1123-P-01 | 4
1133-P-01 | 3
1243-MU-01 | 3
1323-D-01 | 3
1353-D-01 | 3
1433-M-01 | 3
.
.
.
|
The select list identifies the columns you want in the query
result. In the above example, the (EXPR) column
contains the vendor count specified as COUNT(VendorNumber)
in the select list. Computations of
this kind are called aggregate functions, which are defined
in the "Expressions" chapter. The count function counts rows, in this
case rows that satisfy the conditions set up in the SELECT statement
clauses.
This example contains no INTO clause because host variables
are not being used.
The INTO clause is used in application programs to
identify host variables for storing the query result.
For more information on host variables, refer to the appropriate
ALLBASE/SQL application programming guide.
The FROM clause identifies tables and views from which data
is to be retrieved, in this case, PurchDB.SupplyPrice.
The WHERE clause specifies a search condition for screening
rows. Search conditions are comparisons and other operations
you can have ALLBASE/SQL perform in order to screen
rows for your query result. The "Search Conditions" chapter defines the
ALLBASE/SQL search conditions. In this case, the search
condition states that rows in the query result must contain
information for parts that can be delivered in fewer than 25 days.
The GROUP BY clause tells ALLBASE/SQL how to group rows
before performing an aggregate function in the select list.
The rows that satisfy the WHERE clause are grouped.
In this example, the rows are grouped by PartNumber. Then ALLBASE/SQL
counts the number of vendors that supply each part. The result is
a vendor count for each part number.
The HAVING clause screens the groups. In the above example,
data for only groups having a vendor count greater than two becomes part of
the query result.
The ORDER BY clause sorts the query result rows in order by
specified column, in this case, PartNumber.