The Query

A query is a SELECT command that describes to ALLBASE/SQL the data you want retrieved. You can retrieve all or only certain data from a table. You can have ALLBASE/SQL group or order the rows you retrieve or perform certain calculations or comparisons before presenting data to your program. You can retrieve data from multiple tables. You can also retrieve data using views or combinations of tables and views.

The SELECT Command

The SELECT command identifies the columns and rows you want in your query result as well as the tables and views to use for data access. The columns are identified in the select list. The rows are identified in several clauses (GROUP BY, HAVING, and ORDER BY). The tables and views to access are identified in the FROM clause. Data thus specified is returned into host variables named in the INTO clause, as the following syntax shows:

   EXEC SQL    SELECT SelectList

                 INTO HostVariables

                 FROM TableNames

                WHERE SearchCondition1

             GROUP BY ColumnName

               HAVING SearchCondition2

             ORDER BY ColumnID;

To retrieve all data from a table, the SELECT command need specify only the following:



   EXEC SQL BULK SELECT  *

                   INTO :MyArray

                   FROM  PurchDB.Parts;

Although the shorthand notation * can be used in the select list to indicate you want all columns from one or more tables or views, it is better programming practice to explicitly name columns. Then, if the tables or views referenced are altered, your program will still retrieve only the data its host variables are designed to accommodate:

   EXEC SQL BULK SELECT  PartNumber,

                         PartName,

                         SalesPrice

                   INTO :MyArray

                   FROM  PurchDB.Parts;

The SELECT command has several clauses you can use to format the data retrieved from any table:

The WHERE clause specifies a search condition. A search condition consists of one or more predicates. A predicate is a test each row must pass before it is returned to your program.
The GROUP BY clause and the HAVING clause tell how to group rows retrieved before applying any aggregate function in the select list to each group of rows.
The ORDER BY clause causes ALLBASE/SQL to return rows in ascending or descending order, based on the value in one or more columns.

The following SELECT command contains a WHERE clause that limits rows returned to those not containing a salesprice; the predicate used in the WHERE clause is known as the null predicate:



   EXEC SQL BULK SELECT  PartName,

                         SalesPrice

                   INTO :MyArray

                   FROM PurchDB.Parts

                  WHERE SalesPrice IS NULL;

In the UPDATE and DELETE commands, you may need a WHERE clause to limit the rows ALLBASE/SQL changes or deletes. In the following case, the sales price of parts priced lower than $1000 is increased 10 percent; the WHERE clause in this case illustrates the comparison predicate:



   EXEC SQL UPDATE PurchDB.Parts

               SET SalesPrice = SalesPrice * 1.1

             WHERE SalesPrice < 1000.00;

The ALLBASE/SQL Reference Manual details the syntax and semantics for these and other predicates.

When you use an aggregate function in the select list, you can use the GROUP BY clause to indicate how ALLBASE/SQL should group rows before applying the function. You can also use the HAVING clause to limit the groups to only those satisfying certain criteria. The following SELECT command will produce a query result containing two columns: a sales price and a number indicating how many parts have that price:

   EXEC SQL BULK SELECT  SalesPrice,

                         COUNT(PartNumber)

                   INTO :MyArray

                   FROM  PurchDB.Parts

             GROUP BY  SalesPrice

                 HAVING  AVG(SalesPrice) > 1500.00;

The GROUP BY clause in this example causes ALLBASE/SQL to group all parts with the same sales price together. The HAVING clause causes ALLBASE/SQL to ignore any group having an average sales price less than or equal to $1500.00. Once the groups have been defined, ALLBASE/SQL applies the aggregate function COUNT to each group.

Null values in a GROUP BY column constitute their own group. Therefore, a query result having a null value in the column used to group rows would contain a separate row for each null value.

An aggregate function is one example of an ALLBASE/SQL expression. An expression specifies a value. An expression can be used in several places in the SELECT command as well as in the other data manipulation commands. Refer to the ALLBASE/SQL Reference Manual for the syntax and semantics of expressions, as well as the effect of null values on them.

The rows in the query result obtained with the preceding query could be returned in a specific order by using the ORDER BY clause. In the following case, the rows are returned in descending sales price order:

   EXEC SQL BULK SELECT  SalesPrice,

                         COUNT(PartNumber)

                   INTO :MyArray

                   FROM  PurchDB.Parts

               GROUP BY  SalesPrice

                 HAVING  AVG(SalesPrice) > 1500.00

               ORDER BY  SalesPrice DESC;

The examples shown so far have all included the BULK option and a host variable array, because the query results would most likely contain more than one row. Besides the BULK table processing technique, the sequential table processing technique could also be used to handle multiple-row query results. Later in this chapter you'll find examples of both these techniques, as well as examples illustrating simple data manipulation, in which only one row query results are expected.

Selecting from Multiple Tables

To retrieve data from more than one table or view, the query describes to ALLBASE/SQL how to join the tables before deriving the query result in the following places:

In the FROM clause, you identify the tables and views to be joined.
In the WHERE clause, you specify a join condition. A join condition defines the condition(s) under which rows should be joined.

To obtain a query result consisting of the name of each part and its quantity-on-hand, you need data from two tables in the sample database: PurchDB.Parts and PurchDB.Inventory. The join condition in this case is that you want ALLBASE/SQL to join rows in these tables that have the same part number:

   EXEC SQL BULK SELECT  PartName,

                         QtyOnHand

                   INTO :MyArray

                   FROM  PurchDB.Parts,

                         PurchDB.Inventory

                  WHERE  PurchDB.Parts.PartNumber =

                         PurchDB.Inventory.PartNumber;

Whenever two or more columns in a query have the same name but belong to different tables, you avoid ambiguity by qualifying the column names with table names. Because the columns specified in the join condition shown above have the same name (PartNumber) in both tables, they are fully qualified with table names (PurchDB.Parts and PurchDB.Inventory). If one of the columns named PartNumber were named PartNum, the WHERE clause could be written as follows:

   WHERE PartNumber = PartNum

ALLBASE/SQL creates a row for the query result whenever the PartNumber value in one table matches that in the second table. As illustrated in Figure 6-1 “Sample Query Joining Multiple Tables”, any row containing a null PartNumber is excluded from the join, as are rows that have a PartNumber value in one table, but not the other.

Figure 6-1 Sample Query Joining Multiple Tables

You can also join a table to itself. This type of join is useful when you want to identify values within one table that have certain relationships.

The PurchDB.SupplyPrice table contains the unit price, delivery time, and other data for every vendor that supplies any part. Most parts are supplied by more than one vendor, and prices vary with vendor. You can join the PurchDB.SupplyPrice table to itself in order to identify for which parts the difference among vendor prices is greater than $50. The query and its result would appear as follows:

        EXEC SQL BULK SELECT  X.PartNumber,

                              X.VendorNumber,

                              X.UnitPrice,

                              Y.VendorNumber,

                              Y.UnitPrice

                        INTO :MyArray

                        FROM  PurchDB.SupplyPrice X,

                              PurchDB.SupplyPrice Y

                       WHERE  X.PartNumber = Y.PartNumber AND

                              X.UnitPrice  > (Y.UnitPrice + 50.00);



   ----------------+------------+--------------+------------+--------------

   PARTNUMBER      |VENDORNUMBER|UNITPRICE     |VENDORNUMBER|UNITPRICE

   ----------------+------------+--------------+------------+--------------

   1123-P-01       |        9007|        550.00|        9002|        450.00

   1123-P-01       |        9012|        525.00|        9002|        450.00

   1123-P-01       |        9007|        550.00|        9008|        475.00

   1123-P-01       |        9007|        550.00|        9003|        475.00

   1433-M-01       |        9007|        700.00|        9003|        645.00

   1623-TD-01      |        9011|       1800.00|        9015|       1650.00



                    |_________________________|

                                  |

                   These vendors charge at least $50

                   more for a part than the vendors

                   identified in the next two columns.

To obtain such a query result, ALLBASE/SQL joins one copy of the table with another copy of the table, using the join condition specified in the WHERE clause:

You name each copy of the table in the FROM clause by using a correlation name. In this example, the correlations names are X and Y. Then you use the correlation name to qualify column names in the select list and other clauses in the query.
The join condition in this example specifies that for each part number, the query result should contain a row only when the price of the part from vendor to vendor differs by more than $50.

Join variables can be used in any query as a shorthand way of referring to a table, but they must be used in queries that join a table to itself so that ALLBASE/SQL can distinguish between the two copies of the table.

Selecting Using Views

Views are used to restrict data visibility as well as to simplify data access:

Data visibility can be limited using views by defining them such that only certain columns and/or rows are accessible through them.
Data access can be simplified using views by creating views based on joins or containing columns that are derived from expressions or aggregate functions.

The sample database has a view called PurchDB.VendorStatistics, defined as follows:

   CREATE VIEW  PurchDB.VendorStatistics

               (VendorNumber,

                VendorName,

                OrderDate,

                OrderQuantity,

                TotalPrice)

            AS

        SELECT  PurchDB.Vendors.VendorNumber,

                PurchDB.Vendors.VendorName,

                OrderDate,

                OrderQty,

                OrderQty * PurchasePrice

          FROM  PurchDB.Vendors,

                PurchDB.Orders,

                PurchDB.OrderItems

         WHERE  PurchDB.Vendors.VendorNumber   =

                PurchDB.Orders.VendorNumber   

           AND  PurchDB.OrderItems.OrderNumber =

                PurchDB.OrderItems.OrderNumber

This view combines information from three base tables to provide a summary of data on existing orders with each vendor. One of the columns in the view consists of a computed expression: the total cost of an item on order with the vendor.

Note that the select list of the SELECT command defining this view contains some qualified and some unqualified column names. Columns OrderDate, OrderQty, and PurchasePrice need not be qualified, because these names are unique among the column names in the three tables joined in this view. In the WHERE clause, however, both join conditions must contain fully qualified column names, since the columns are named the same in each of the joined tables.

You can use a view in a query without restriction. In the FROM clause, you identify the view as you would identify a table. When you reference columns belonging to the view, you use the column names used in the view definition. In the view above, for example, the column containing quantity-on-order is called OrderQuantity, not OrderQty as it is in the base table (PurchDB.OrderItems).

The VendorStatistics view can be used to quickly determine the total dollar amount of orders existing for each vendor. Because the view definition contains all the details for deriving this information, the query based on this view is quite simple:

   EXEC SQL BULK SELECT  VendorNumber,

                         SUM(TotalPrice)

                   INTO :MyArray

                   FROM  PurchDB.VendorStatistics

               GROUP BY  VendorNumber;

The query result appears as follows:

   ------------+----------------------

   VENDORNUMBER|(EXPR)

   ------------+----------------------

           9001|              31300.00

           9002|               6555.00

           9003|               6325.00

           9004|               2850.00

           9006|               2010.00

           9008|              12460.00

           9009|               7750.00

           9010|               9180.00

           9012|              12280.00

           9013|               8270.00

           9014|               2000.00

           9015|              17550.00

Although you can use views in queries without restriction, you can use only some views to insert, update, or delete rows:

You cannot INSERT, UPDATE, or DELETE using a view if the view definition contains one of the following:
- Join operation.
- Aggregate function.
- DISTINCT option.
- GROUP BY clause.
- ORDER BY clause.
- UNION.
You cannot INSERT using a view if any column of the view is computed in an arithmetic expression.

The PurchDB.VendorStatistics view cannot be used for any INSERT, UPDATE, or DELETE operation because it is based on a three table join and contains a column (TotalPrice) derived from a multiplication operation.

The Query

Technical documentation

» Table of Contents

» Index

The SELECT Command

Selecting from Multiple Tables

Selecting Using Views