The OLQ SELECT Statement

Introducing the OLQ SELECT statement › The OLQ SELECT Statement

The OLQ SELECT Statement

When to use the SELECT statement

CA OLQ uses the OLQ SELECT statement to access information from ASF tables, logical records, and database records.

Generate queries

By using the SELECT statement, you can formulate both simple and complex queries for information. CA OLQ interprets the SELECT statement and produces a report.

SELECT statement syntax

Depending on your request, use some or all of these clauses in the sequence listed:

Clause	Information you supply
SELECT	Columns or fields to display.
FROM	The source of information (that is, table or record names).
WHERE	Selection criteria applied to rows or record occurrences.
GROUP BY	Column or field names for grouping information with the same value (for example, all rows with the same department ID).
HAVING	Selection criteria applied to grouped information (for example, a summary row where the average salary is less than $35,000).
UNION	Multiple SELECT statements. UNION combines the rows retrieved from each SELECT statement.
ORDER BY	Column or field names on which to sort retrieved information.

Reading a SELECT statement

Read a SELECT statement like an English sentence. The order of the clauses falls into place. For example, to get all employee IDs less than 100 displayed in ascending order, you would:

SELECT the employee ID
FROM the EMP table
WHERE the employee ID is less than 100
ORDER the display BY employee ID

Here is the complete SELECT statement:

select empid from emp where empid < 100 order by empid

Some examples

These examples show how the SELECT statement is used to retrieve information from a database:

List all information about corporate departments:
```
select * from dept
```
List the names and phone numbers of employees in department 4000 sorted by the employee's last name:
```
select lastname, firstname, phone
  from emp
  where deptid = 4000
  order by lastname
```

Display the date Themis Papazeus was hired:

select startdate
  from emp
  where firstname=&xq.themis' and lastname=&xq.papazeus'

List the number of employees in department 4000:

select count(*)
  from emp
  where deptid = 4000

How many employees in each department were hired before January 1, 1975?

select deptid, count(*)
  from emp
  where startdate < 750101
  group by deptid