|
|
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.
|