The SELECT statement in OLQ access mode retrieves data for display. A single retrieval request can combine data from multiple ASF tables, database records, logical records, subschemas, and sequential files. Sequential files are used for batch retrieval only.
Access mode:The syntax below is invalid when the access switch is set to IDMS.
Syntax:
►►─── SELect ─┬────────────┬──────────────────────────────────────────────────► └─ DIStinct ─┘ ►─┬─ * ────────────────────────────────────────────────────────────────────┬─► │ ┌───────────────────────────────── , ────────────────────────────────┐ │ └─▼─┬─ qualifying-name.* ─────────────────────────────────────────────┬┴─┘ └┬─ column-expression ─────────────────┬─┬───────────────────────┬┘ └┬──────────────────────┬ column-name ┘ └ AS alt-column-heading ┘ │ ┌──────────────────┐ │ └─▼ qualifying-name. ┴─┘ ┌─────────────────────────── , ──────────────────────────┐ ►─── FROm -▼─┬─ table-name ─────────────────┬─┬───────────────────┬─┴────────► ├─ view-name ──────────────────┤ └─ alt-source-name ─┘ └─┬────────────┬─ record-name ─┘ └─ view-id. ─┘ ►─┬──────────────────┬───────────────────────────────────────────────────────► └─ WHEre criteria ─┘ ►─┬────────────────────────────────────────────────────────────┬─────────────► │ ┌────────── , ──────────┐ │ └─ GROup by -▼- group-by-expression ─┴─┬───────────────────┬─┘ └─ HAVIng criteria ─┘ ►─┬──────────────────────────────────────┬───────────────────────────────────► └─ UNIon ─┬───────┬─ select-statement ─┘ └─ ALL ─┘ ►─┬────────────────────────────────────────────────────────┬─────────────────► │ ┌─────────────────── , ───────────────────┐ │ └─ ORDer by -▼─┬─ column-name ─────┬─┬───────────────┬─┴─┘ └─ column-position ─┘ ├─ ASCending ◄ ─┤ └─ DEScending ──┘ ►─┬──────────────────────────────┬───────────────────────────────────────────►◄ └─ OUTput ─┬─────┬─ file-name ─┘ └─ = ─┘
Parameters:
Eliminates all but one duplicate row occurrence from the report, based on specified columns.
The qualifying name can modify an asterisk (*), requesting all columns of the named table. Qualifying-name can be one of the following:
You can specify more than one qualifying name for a column.
Fixed value expressions containing blanks or special characters must be enclosed in quotation marks.
Specifies an alternative column heading. You can use this to assign a column heading for any column reference, including computed fields or fields modified by a built-in or aggregate function.
Commas separating column specifications are optional, but recommended.
Represents the table from which data is retrieved. Commas separating multiple table and record entries are required.
Specifies the name of the table derived from one or more stored data tables or records.
Specifies a user-supplied name identifying a particular subschema. View-id is defined by the VIEW operand of the SIGNON statement. This is used to qualify record names.
Specifies the name of a record.
Specifies an alternative name for the table or record.
Represents criteria used by SELECT to retrieve records.
Note: Syntax for criteria used in the WHERE clause are expanded in SELECT Selection Criteria.
Groups rows into sets that contain like column values. Group-by-expression represents a column value. In each GROUP BY set, all rows contain the same value of the group-by-expression.
The GROUP BY group-by-expression must be the same value as the column expression that is the object of the SELECT statement and can include aggregate and/or built-in functions.
Applies selection criteria to the result of the GROUP BY expression.
Note: Syntax and Purpose for criteria used in the HAVING clause are expanded in SELECT Selection Criteria.
Concatenates two or more SELECT statements containing like columns. Columns correspond positionally. Corresponding columns must have the same data type, picture, and decimal representation. UNION eliminates duplicate rows from the report.
Sorts the rows of the report by the value of the column you specify. You can sort by:
Specifies the order in which to sort the columns.
Specifies that the report is saved as a sequential file. This option is only valid when running CA OLQ batch.
Examples:
Select all columns
Instead of specifying all the columns of the table, or fields of the record that you want to retrieve, you can specify an asterisk (*) and receive all columns or fields:
select * from employee
The result report displays the first column of the EMPLOYEE table first, the second column of the EMPLOYEE table second, and so on.
Select column expression
You can specify the names of the columns or fields you want to retrieve explicitly:
select emp-name, dept-name from employee, department
The result report displays two columns, the employee name from the EMPLOYEE table, and the department name from the DEPARTMENT table.
Select Distinct
Distinct eliminates all but one duplicate row occurrence from the report, based on specific columns.
Example:
If there are two occurrences of the last name field BURR, you can eliminate the row containing the duplicate value:
select DISTINCT emp-last-name from employee ! display EMP LAST NAME ------------- BURR GOLD ILTIS LIGARE WAKEFIELD WONES
Select qualifying name
Qualifying-name is a prefix denoting the table or record from which the column is being retrieved. Qualifying-name must be separated from its object by a period (.). This is useful when two tables contain columns with the same name.
Example:
If both the EMPLOYEE and the DEPARTMENT tables contain DEPT-ID fields, you can qualify the column names:
select EMPLOYEE.dept-id, DEPARTMENT.dept-id from employee, department where employee.dept-id = department.dept-id
The qualifying name can also modify an asterisk (*).
You can also specify a group-level qualification as a qualifying name:
select EMP-NAME-0415.EMP-LAST-NAME-0415 from employee
Select two references
You can use the AS parameter to distinguish one of two references to the same column name:
select manage.emp-id-0415 AS MANAGER, works-for.emp-id-0415 AS EMPLOYEE from employee manages, employee works-for
Select and sort
A simple SELECT command retrieves the EMPLOYEE record specified with a column list and sorts the fields by last name:
select emp-id-0415,emp-name-0415
from employee
order by emp-last-name-0415 descending ! display
EMP-ID-0415 EMP-FIRST-NAME-0415 EMP-LAST-NAME-0415
0124 SUSAN SPELLMAN
0528 EDWARD MCCARTHY
0512 CHERYL MAYOR
1042 SHARON CIVITTOLO
0954 ANGELA BELVAL
0320 JOSEPH ANTHONY
Joining tables based on equal values
To retrieve hiring information on all employees in a department:
This example lists employees, the year they started, and their department name:
select employee.emp-last-name, employee.start-year, department.dept-name from employee, department where employee.dept-id = department.dept-id ! display EMP-LAST-NAME START-YEAR DEPT-NAME --------------- ---------- --------------------------------- WONES 79 DEVELOPMENT WAKEFIELD 83 PERSONNEL BURR 80 PERSONNEL LIGARE 85 DEVELOPMENT BURR 84 MARKETING SCHLEY 80 PLANNING ILTIS 81 PERSONNEL GOLD 80 MARKETING
Joining two tables
Using a single SELECT statement, you can produce a report containing data from more than one table. The selection criteria in the WHERE clause provide column join and key information. The WHERE clause can contain other comparison expressions.
Example:
The following SELECT statement joins the EMPLOYEE and DEPARTMENT tables on like DEPT-ID values, and lists only those employees who started working before 1980:
select employee.*, department.* from employee, department where employee.dept-id = department.dept-id and employee.start-year lt '80'
You do not have to display the fields on which you are joining.
Example:
The following SELECT statement joins the EMPLOYEE and DEPARTMENT stored tables, but lists only the employee ID numbers and the name:
select distinct employee.emp-id, department.dept-name from employee, department where employee.dept-id = department.dept-id
Reflexive joins
Reflexive joins combine two different rows of the same table. When you are joining a table with itself, it is useful to supply alternative table names to distinguish the two references to the column name.
This example lists employees and their managers. EMPLOYEE MANAGE and EMPLOYEE WORKS-FOR are alternative names for the same record:
select works-for.emp-last-name as 'worker', manage.emp-last-name as 'manager' from employee works-for, employee manage where works-for.manager-id = manage.emp-id ! display WORKER MANAGER ------------- -------------- WONES WONES WAKEFIELD WAKEFIELD BURR WAKEFIELD LIGARE WONES BURR BURR SCHLEY WONES ILTIS WAKEFIELD GOLD BURR
Joining tables and records residing in multiple subschemas
This example uses a SELECT statement to create a report containing data from an ASF-generated table and a network table:
In this example, the EMPLOYEE table resides in the EMPSS01 subschema. The DEPARTMENT table is an ASF-generated table:
signon ss empss01 dictname testdict id=emp
signon table department dictname asfdict id=dept
select * from emp.employee a, dept.department b where a.dept-id = b.dept-id
Nesting SELECT statements
You can issue multiple SELECT statements in a single retrieval request. By using more than one SELECT statement, you can can apply a more specific search condition than is possible in a single WHERE clause. You can combine SELECT statements in a retrieval request in either of the following two ways:
Example:
To list the departments containing more than two employees:
select * from department where 2<(select count(*) from employee where employee.dept-id = department.dept-id) ! display DEPARTMENT NAME DEPT ID DEPT HEAD ID -------------------- ------- ------------ DEVELOPMENT 20 1127 PERSONNEL 30 4430
Nested SELECT statements are enclosed in parentheses. There is no limit to the number of nested SELECT statements, but bear in mind that the statement becomes hard to understand after three or four nesting levels.
The column specification of the higher level SELECT statement must be an asterisk (*), indicating all columns.
Using existential quantifiers
This example lists which department employee Schley works in:
select * from department where exists
(select * from employee
where employee.dept-id = department.dept-id
and emp-last-name = 'schley') ! display
DEPARTMENT NAME DEPT ID DEPT HEAD ID
-------------------------------------- ------- ------------
DEVELOPMENT 20 1127
Stringing together SELECT statements (UNION)
You can concatenate two or more tables containing like columns, using the UNION statement. The result table contains data found in one or both source tables. The UNION option eliminates duplicate rows from the report. To display duplicate rows, specify UNION ALL.
The two SELECT statements must have the same number of columns. Corresponding columns must have the same:
The DISTINCT operand cannot be specified when using the UNION option.
This example lists employee information for all employees in the Development departments of the Massachusetts and New York EMPLOYEE tables. Because ALL is specified, duplicate rows are displayed:
select * from mass.employee union all select * from ny.employee
|
Copyright © 2013 CA.
All rights reserved.
|
|