The SELECT data manipulation statement retrieves values from one or more tables, views, procedures and table procedures. CA IDMS returns the values in the form of a result table.
When the SELECT statement is:
To issue a SELECT statement, you must own or have the SELECT privilege on each table, view, function, procedure and table procedure explicitly named in the statement.
Additional authorization requirements apply to each view explicitly named in the SELECT statement, to each view explicitly named in the definition of such a view, to each view explicitly named in the definition of those views, and so forth.
For any such view, the owner of the view must own or have the grantable SELECT privilege on each table, view, procedure and table procedure explicitly named in the view definition.
►►─── SELECT ─┬────────────┬──────────────────────────────────────────────────► ├─ ALL ◄─────┤ └─ DISTINCT ─┘ ►─┬─ * ──────────────────────────────────────────────────────┬───────────────► │ ┌─────────────────────── , ───────────────────────────┐ │ └─▼─┬── value-expression ──┬──────────────────────────┬─┴──┤ │ └─┬──────┬───result-name ──┘ │ │ └─ AS ─┘ │ ├─ table-name.* ───────────────────────────────────────┤ └─ alias.* ────────────────────────────────────────────┘ ►─┬───────────────────────────────────────────┬──────────────────────────────► │ ┌─────── , ───────────┐ │ ├─ INTO ─▼┬─ host-variable ───┬┴────────────┤ │ ├ local-variable ───┤ │ │ └ routine-parameter ┘ │ └─ BULK :bulk-buffer ──┬────────────────┬───┘ └─ bulk-options ─┘ ┌──────────── , ────────────────────────────────────┐ ►─── FROM ─▼─┬─ table-reference ────┬─┬────────────────────┬───┴─────────────► └─ (query-expression) ─┘ └─┬──────┬─── alias ─┘ └─ AS ─┘ ►─┬───────────────────────────────────────────────┬──────────────────────────► └─ WHERE ──┬─ search-condition ──────────┬──────┘ └─ extended-search-condition ─┘ ►─┬─────────────────────────────┬────────────────────────────────────────────► └─ PRESERVE ─┬─ table-name ─┬─┘ └─ alias ──────┘ ►─┬─────────────────────────────────────────────────┬────────────────────────► │ ┌─────────────── , ────────────────┐ │ └─ GROUP BY ─▼─┬───────────────┬─ column-name ─┴─┤ ├─ table-name. ─┤ │ ├─ alias. ──────┘ │ └─ rowid-pseudo-column ────────────┘ ►─┬───────────────────────────┬──────────────────────────────────────────────► └─ HAVING search-condition ─┘ ►─┬─────────────────────────────┬────────────────────────────────────────────► └─ OPTIMIZE FOR literal ROWS ─┘ ►─┬───────────────────────────────────────────┬──────────────────────────────► │ ┌───────────────────────────────────────┐ │ └─▼─ UNION ─┬────────┬─ query-expression ─┴─┘ └─ ALL ──┘ ┌─────────────────────── , ───────────────────────────┐ ►── ORDER BY ─▼─┬─┬───────────────┬─ column-name ─┬───┬──────────┬──┴────────►◄ │ ├─ table-name. ─┤ │ ├─ ASC ◄───┤ │ └─ alias. ──────┘ │ └─ DESC ───┘ ├─ column-number ─────────────────┤ ├─ result-name ───────────────────┤ └─ rowid-pseudo-column ───────────┘
Expansion of bulk-options
►►──┬──────────────────────────────┬──────────────────────────────────────────► └─ START :start-variable-name ─┘ ►──┬─────────────────────────────────┬───────────────────────────────────────►◄ └─ ROWS :row-count-variable-name ─┘
Directs CA IDMS to return all the rows, including duplicates, in the requested result table. The default value is ALL when you specify neither ALL nor DISTINCT.
Directs CA IDMS to eliminate duplicate rows from the result table returned by the SELECT statement.
Specifies that the result table is to include all columns in the tables, views, procedures and table procedures named in the FROM parameter of the SELECT statement. The columns in the tables, views, procedures and table procedures are concatenated in the order in which the tables, views, procedures and table procedures are specified in the FROM parameter.
Identifies the values to be included in a result column. Typically, value-expression is a column reference, an arithmetic operation that includes a column reference, or an aggregate function that includes a column reference.
Each column reference in value-expression must identify a column in the table defined by the FROM parameter of the SELECT statement.
You can specify from 1 through 1,024 value expressions. Multiple value expressions must be separated by commas.
The number of columns in a result table is the same as the number of value expressions in the SELECT statement defining the result table. For expanded value-expression syntax, see Expansion of Value-expression.
Specifies a name for the result column identified by value-expression. When displaying the result table, the Command Facility uses the result name as the column header.
Result-name must be a 1- through 32-character name that follows the conventions for SQL identifiers.
Specifies that the result table is to include all columns in the table identified by table-name.
Table-name must match an occurrence of table-name in the FROM parameter.
Specifies that the result table is to include all columns in the table identified by alias.
Alias must match an occurrence of alias in the FROM parameter.
Identifies the variables to which CA IDMS is to assign the values in the result table. CA IDMS assigns the value in the first result column to the first variable, the value in the second result column to the second variable, and so on. You use the INTO parameter when the result table will contain at most one row.
Host-variable must be a host variable previously declared in the application program.
Local-variable and routine-parameter must be defined previously in the SQL routine.
You must specify the same number of variables in the INTO parameter as the number of columns in the result table. Multiple variables must be separated by commas.
You can specify the INTO parameter only when you embed the SELECT statement in an application program or SQL routine. You must specify INTO or BULK when you embed a SELECT statement in a host program or SQL routine.
Identifies a variable defined as an array to which CA IDMS is to assign the values in the result table. The BULK parameter is a CA IDMS extension of the SQL standard. You use the BULK parameter when the result table may contain more than one row.
You can specify the BULK parameter only when you embed the SELECT statement in an application program. You must specify BULK or INTO when you embed a SELECT statement in a host program.
Bulk-buffer must be a variable previously declared in the host-language application program or SQL routine. Bulk-buffer must have a subordinate structure that occurs multiple times and has the same number of sub-elements as the number of columns in the result table.
Refers to optional parameters when BULK is specified. Syntax for bulk-options immediately follows the syntax for SELECT.
Identifies one or more tables, views, procedures and table procedures from which the result table is to be derived. For expanded table-reference syntax, see Expansion of Table-reference.
Represents a table to be used in the evaluation of an SQL statement.
Defines a new name to be used to identify the table, view, procedure, table procedure or query-expression within the SELECT statement. Alias must be a 1- through 18-character name that follows the conventions for SQL identifiers.
Note: CA IDMS supports keywords as identifiers as an extension of the SQL standard. However, if you use a keyword as an alias but do not code the optional parameter AS, you must delimit the keyword with double quotation marks or a syntax error will occur.
Introduces criteria that a row must meet to be included in the result table.
Specifies the set of values against which a row in the base table is tested:
For expanded search-condition syntax, see Expansion of Search-condition.
Specifies a search condition that includes a set specification. For expanded extended-search-condition syntax, see Expansion of Extended-search Condition.
Requests an outer join on the specified table, view, procedure, or table procedure. The PRESERVE parameter is a CA IDMS extension of the SQL standard.
To specify a more powerful outer join that is compatible with the SQL standard, use the joined-table construct as table-reference
Specifies by table name the table, view, procedure or table procedure to be preserved in an outer join. For expanded table-name syntax, see Expansion of Table-name.
Specifies the table, view, procedure or table procedure to be preserved in an outer join by the alias defined for the table or view in the FROM parameter of the SELECT statement.
Groups the rows in the table defined by the FROM parameter by the values in the specified columns. Rows with the same value in each grouping column are grouped together.
Column-name must identify a column in a table, view, procedure or table procedure named in the FROM parameter of the SELECT statement.
Specifies the table, view, procedure or table procedure that includes the named column. For expanded table-name syntax, see Expansion of Table-name.
Specifies the alias associated with the table, view, procedure or table procedure that includes the named column. Alias must be defined in the FROM parameter of the SELECT statement.
Specifies a ROWID pseudo-column as a grouping column. See Expansion of rowid-pseudo-column for more information.
Specifies criteria a group must meet to be included in the result table:
For expanded search-condition syntax, see Expansion of Search-condition.
Specifies the expected number of output rows from this query-specification. It is used by the optimizer to generate the best possible access strategy for satisfying query-expression. "Literal" is an integer constant.
Specifies that:
You cannot include the UNION operator in a SELECT statement embedded in an application program.
See Expansion of Query-expression for:
Specifies that all rows resulting from the UNION operation are retained; duplicates are not discarded.
Sorts the rows in the table defined by the FROM parameter in ascending or descending order by the values in the specified columns. Rows are ordered first by the first column specified, then by the second column specified within the ordering established by the first column, then by the third column specified, and so on.
Specifies a sort column by the column name. Column-name must identify a column in a table, view, procedure or table procedure named in the FROM parameter of the SELECT statement and must be included in the result table.
Specifies the table, view, procedure or table procedure that includes the named column. For expanded table-name syntax, see Expansion of Table-name.
Specifies the alias associated with the table, view, procedure or table procedure that includes the named column. Alias must be defined in the FROM parameter of the SELECT statement.
Specifies a sort column by the position of the column in the result table. The first result column is in position 1.
Column-number must be an integer in the range 1 through the number of columns in the result table.
Specifies the sort column by the result name specified in the AS parameter of query-expression.
Specifies a sort column as a ROWID pseudo-column. See Expansion of rowid-pseudo-column for more information.
Indicates that the values in the specified column are to be sorted in ascending order. ASC is the default when you specify neither ASC nor DESC.
Indicates that the values in the specified column are to be sorted in descending order.
Parameters for Expansion of bulk-options
Identifies a variable containing the relative position within the bulk buffer to which CA IDMS is to assign the values in the first row of the result table. Values in subsequent rows of the result table are assigned sequentially to subsequent positions in the bulk buffer.
Start-variable-name must be a variable previously declared in the host-language application program or SQL routine. The value in the variable must be an integer in the natural range of subscripts for arrays in the language in which the application program is written.
If you do not specify the START parameter, CA IDMS assigns the values in the first row of the result table to the beginning of the bulk buffer.
Identifies a variable that specifies the maximum number of rows in the result table CA IDMS is to assign to the bulk buffer.
Row-count-variable-name must be a variable previously declared in the host-language application program or SQL routine. The value in the host variable must be an integer in the range 1 through the number of rows that will fit in the bulk buffer.
If you do not specify the ROWS parameter, CA IDMS assigns the rows in the result table to the bulk buffer sequentially until no more rows exist in the result table or the buffer is full.
Note: The bulk-buffer, start-variable-name, and row-count-variable-name variables can be host variables, or when the statement is used in an SQL routine, local variables or routine parameters. In this case, their names must not be preceded with a colon.
Value Expressions without Column References
If the value expression that identifies a result column does not include any column references, the result column contains the same value in each row. This value is derived directly from the value expression without reference to the table defined by the FROM parameter of the SELECT statement.
Use of BULK and INTO
You must specify the BULK parameter or the INTO parameter when you embed the SELECT statement in an application program, except when the statement is to be compiled dynamically.
You cannot specify either of these parameters when you submit the SELECT statement through the command facility or for dynamic compilation in an application program.
When you embed the SELECT statement in an application program and:
If neither of these conditions is met, CA IDMS returns a cardinality violation error.
Note: To select an undetermined number of rows, the SELECT statement must be associated with a cursor. You can fetch rows individually from the cursor.
Compatible Data Types
The data types of the result columns and their corresponding host variables in the BULK or INTO parameter must be compatible for assignment.
Uniqueness of Table Names
Each alias and each table name without an associated alias must be unique within the FROM parameter of a SELECT statement.
Column References in the WHERE Parameter
Each column reference directly included in the search condition in the WHERE parameter of a SELECT statement must identify a column in a table, view, procedure or table procedure specified in the FROM parameter of the SELECT statement, or be an outer reference.
Note: For more information about outer references, see Subqueries.
Aggregate Functions in the WHERE Parameter
The search condition in the WHERE parameter of a SELECT statement cannot directly include an aggregate function. However, you can use aggregate functions in subqueries within the search condition.
GROUP BY Parameter Requirements
When a SELECT statement includes the GROUP BY parameter, each column reference in the value expressions that identify the result columns must identify a column specified in the GROUP BY parameter or occur only in the argument of an aggregate function. If the result columns are identified by an asterisk (*), the GROUP BY parameter must include all the columns in the tables, views, procedures and table procedures specified in the FROM parameter.
SELECT Statements without the GROUP BY Parameter
If a SELECT statement does not include the GROUP BY parameter:
Column References in the HAVING Parameter
Each column reference included in the search condition in the HAVING parameter of a SELECT statement must identify a column specified in the GROUP BY parameter of the SELECT statement or occur in the argument of an aggregate function.
When to Use OPTIMIZE FOR Literal ROWS
Under some circumstances, the SQL optimizer may choose a less than optimal access strategy to satisfy a query expression. This typically happens with host program embedded SQL statements which contain WHERE clauses with host variable references, rather than explicit constants. For example, a BETWEEN clause involving host variables may induce the optimizer to assume many rows will be retrieved, causing it to choose an area sweep to satisfy the request. Without knowing the underlying values of the host variables, the optimizer cannot know if the BETWEEN will always qualify a small number of rows, thus possibly making an index retrieval much more efficient. The OPTIMIZE FOR literal ROWS clause is used to override the number of expected rows deduced by the optimizer. This allows it to generate better access strategies.
Result Column Names with the UNION Operator
When a SELECT statement includes the UNION operator, the names of the columns in the result table are the names established by the last UNION operand. These names are used as:
Outer Join Using PRESERVE
Within a SELECT statement, PRESERVE can be used to request an outer join on one of the tables or views named in the FROM parameter. If PRESERVE is specified, the result table includes rows of the preserved table for which no matching row exists in the other tables used in the join operation.
If no matching row exists, the corresponding columns in the result table are set to null. Predicates in the WHERE clause other than those used to perform the outer join are evaluated before determining whether a matching row exists.
The following statement returns the names of all active employees. The name of the employee's spouse is also returned if found. The logic of the statement is that the result table will include the name of each active employee, whether the employee has a spouse:
select e.first_name, e.last_name,
s.first_name, s.last_name
from employee e, relation s
where e.empid=s.empid
and e.status='A' -- active employee
and s.relationship='S' -- employee's spouse
preserve e ;
Selecting a Single Row
The following SELECT statement retrieves information about a specific project from the PROJECT and EMPLOYEE tables. The value in each selected column is assigned to the corresponding host variable. The SELECT statement includes indicator variables for the ACT_START_DATE, ACT_END_DATE, EST_START_DATE, and EST_END_DATE columns.
EXEC SQL
SELECT PROJ_ID, EMP_FNAME, EMP_LNAME, DEPT_ID, PROJ_DESC,
ACT_START_DATE, ACT_END_DATE, EST_START_DATE, EST_END_DATE
INTO :PROJ-ID, :EMP-FNAME, :EMP-LNAME, :DEPT-ID, :PROJ-DESC,
:ACT-START-DATE :ACT-START-DATE-IND,
:ACT-END-DATE :ACT-END-DATE-IND,
:EST-START-DATE :EST-START-DATE-IND,
:EST-END-DATE :EST-END-DATE-IND
FROM PROJECT, EMPLOYEE
WHERE PROJ_LEADER_ID = EMP_ID
AND PROJ_ID = :PROJECT_NUMBER
END-EXEC
Retrieving Values through the Command Facility
The following SELECT statement retrieves project information for each employee and consultant in department 1100.
select e.proj_id, emp_lname, emp_fname, est_start_date, act_start_date
from employee e, project p
where e.proj_id = p.proj_id
and dept_id = 1100
union select c.proj_id as "Project ID",
con_lname as "Last Name",
con_fname as "First Name",
est_start_date as "Estimated Start Date",
act_start_date as "Actual Start Date"
from consultant c, project p
where c.proj_id = p.proj_id
and dept_id = 1100
order by 1, 2, 3;
Selecting Multiple Rows into a Buffer
The following SELECT statement returns information on the cost of insurance plans in Massachusetts into an array identified by the host variable :INS-COST-BUFFER:
EXEC SQL
SELECT PLAN_CODE, COMP_NAME, MAX_LIFE_COST, FAMILY_COST, DEP_COST
BULK :INS-COST-BUFFER
FROM INSURANCE_PLAN
WHERE STATE = 'MA'
END-EXEC
|
Copyright © 2014 CA.
All rights reserved.
|
|