ALLBASE/SQL Reference Manual (36216-90216)

Chapter 3 111

SQL Queries

Using the SELECT Statement

The select list identiﬁes the columns you want in the query result. In the above example,

the (EXPR) column contains the vendor count speciﬁed as COUNT(VendorNumber) in the

select list. Computations of this kind are called aggregate functions, which are deﬁned

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 identiﬁes tables and views from which data is to be retrieved, in this case,

PurchDB.SupplyPrice.

The WHERE clause speciﬁes 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 deﬁnes 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 speciﬁed column, in this case,

PartNumber.