Using the SELECT Statement

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.

Using the SELECT Statement

Technical documentation

» Table of Contents

» Index