SELECT

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.
idmscu19
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:
  • Submitted through the Command Facility, the values in the result table are displayed in tabular form
  • Embedded in an application program or SQL routine, the values in the result table are stored in host variables, local variables, or routine parameters
This article describes the following information:
2
2
Authorization
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.
Syntax
  ►►─── 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
 ─┘  
Parameters
  • ALL
    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.
  • 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. If the schema for the entities named in the FROM parameter was defined using the WITH VIRTUAL KEYS option, the result will also include the ROWID and virtual foreign key pseudo-columns. The virtual foreign key columns are sorted in alphabetical order.
  • value-expression
    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.
  • AS
    result-name
    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.
  • table-name.*
    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.
  • alias
    .*
    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.
  • INTO host-variable
  • local-variable
  • routine-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.
  • BULK :
    bulk-buffer
    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.
  • bulk-options
    Refers to optional parameters when BULK is specified. Syntax for
    bulk-options
    immediately follows the syntax for SELECT.
  • FROM table-reference
    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.
  • (query-expression)
    Represents a table to be used in the evaluation of an SQL statement.
    • AS
      alias
      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.
      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.
  • WHERE
    Introduces criteria that a row must meet to be included in the result table.
  • search-condition
    Specifies the set of values against which a row in the base table is tested:
    • When the value of
      search-condition
      is true, the row is included in the result table
    • When the value of
      search-condition
      is false or unknown, the row is not included in the result table
    For expanded
    search-condition
    syntax, see Expansion of Search-condition.
  • extended-search-condition
    Specifies a search condition that includes a set specification. For expanded
    extended-search-condition
    syntax, see Expansion of Extended-search Condition.
  • PRESERVE
    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
  • table-name
    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.
  • alias
    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.
  • GROUP BY
    column-name
    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.
    • table-name
      Specifies the table, view, procedure or table procedure that includes the named column. For expanded
      table-name
      syntax, see Expansion of Table-name.
    • alias
      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.
  • rowid-pseudo-column
    Specifies a ROWID pseudo-column as a grouping column. See Expansion of rowid-pseudo-column for more information.
  • HAVING search-condition
    Specifies criteria a group must meet to be included in the result table:
    • When the value of
      search-condition
      is true, the group is included in the result table
    • When the value of
      search-condition
      is false or unknown, the group is not included in the result table
    For expanded
    search-condition
    syntax, see Expansion of Search-condition.
  • OPTIMIZE FOR literal ROWS
    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.
  • UNION query-expression
    Specifies that:
    • The result table is to include both the rows from the table defined in the FROM parameter of the SELECT statement and the rows from the table defined in
      query-expression
      .
    • Duplicate rows are to be eliminated from the table resulting from the UNION operation, unless the ALL keyword is present.
    You cannot include the UNION operator in a SELECT statement embedded in an application program.
    See Expansion of Query-expression for:
    • Expanded
      query-expression
      syntax
    • A discussion of data type compatibility and the data type that results from the union of columns with compatible data types
  • ALL
    Specifies that all rows resulting from the UNION operation are retained; duplicates are not discarded.
  • ORDER BY
    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.
  • column-name
    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.
    • table-name
      Specifies the table, view, procedure or table procedure that includes the named column. For expanded
      table-name
      syntax, see Expansion of Table-name.
    • alias
      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.
  • column-number
    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.
  • result-name
    Specifies the sort column by the result name specified in the AS parameter of
    query-expression
    .
  • rowid-pseudo-column
    Specifies a sort column as a ROWID pseudo-column. See Expansion of rowid-pseudo-column for more information.
  • ASC
    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.
  • DESC
    Indicates that the values in the specified column are to be sorted in descending order.
Parameters for Expansion of bulk-options
  • START :
    start-variable-name
    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.
  • ROWS :
    row-count-variable-name
    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.
    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.
Usage
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:
  • You specify INTO, the result table must have at most one row
  • You specify BULK, the result table must have no more rows than the number of entries in the bulk buffer (or the value of
    row-count-variable-name
    , if specified)
If neither of these conditions is met, CA IDMS returns a cardinality violation error.
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.
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:
  • If any column reference in a value expression that identifies a result column is included in the argument of an aggregate function, all column references in all the value expressions must be in aggregate functions
  • The entire table defined by the FROM and WHERE parameters is treated as a single group
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:
  • Column headings when the online command facility displays the result table
  • Column names in the SQL descriptor area when CA IDMS compiles the SELECT statement dynamically
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 ;
Examples
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
Using Virtual Foreign Keys in a Simple SELECT Statement
The following SELECT statement returns the virtual foreign keys referencing the owner DEPARTMENT and owner OFFICE records for employee 7.
SELECT FKEY_DEPT_EMPLOYEE, FKEY_OFFICE_EMPLOYEE FROM EMPLOYEE WHERE EMP_ID_0415 = 7;
Using ROWID and Virtual Foreign Key to join records
The following SELECT statement joins the DEPARTMENT and EMPLOYEE records that are connected through a set.
SELECT D.DEPT_ID_0410,D.DEPT_NAME_0410, E.EMP_ID_0415,E.EMP_LAST_NAME_0415 FROM DEPARTMENT D,EMPLOYEE E WHERE E.FKEY_DEPT_EMPLOYEE = D.ROWID;
Using ROWID and Virtual Foreign Key to obtain owner data
The following SELECT statement retrieves DEPARTMENT information for EMPLOYEE 7.  The DEPARTMENT record does not participate as a member in any sets and therefore has no virtual foreign keys displayed.
SELECT  * FROM DEPARTMENT WHERE ROWID = (SELECT FKEY_DEPT_EMPLOYEE FROM EMPLOYEE WHERE EMP_ID_0415 = 7);DEPT_ID_0410 DEPT_NAME_0410 DEPT_HEAD_ID_0410 ROWID------------------------   -----------------   -----------------------  ----------------------                    4000 PUBLIC RELATIONS  7  X'0125B60100000008'
When using a virtual key schema and selecting all columns, the columns are displayed in the following order for each table in the statement.
  • User columns
  • ROWID
  • Virtual foreign keys in alphabetical order
Using ROWID and Virtual Foreign Key to obtain member data
The following SELECT statement retrieves information for all EMPLOYEEs in DEPARTMENT 4000.  EMPLOYEE participates as a member in the DEPT-EMPLOYEE set and the OFFICE-EMPLOYEE set which is reflected in the projected columns.
SELECT * FROM EMPLOYEE WHERE FKEY_DEPT_EMPLOYEE = (SELECT ROWID FROM DEPARTMENT WHERE EMP_ID_0410 = 4000); EMP_ID_0415 EMP_FIRST_NAME_0415 EMP_LAST_NAME_0415 EMP_STREET_0415 EMP_CITY_0415 EMP_STATE_0415  ------------ -------------------- ------------------- ---------------- -------------- ---------------  7 MONTE BANK 45 EAST GROVE DR HANIBAL MA  127 CAROL MCDOUGALL 19 URITOP DR WELLESLEY MA 476 BETSY ZEDI 34 VALE AVE SOUTHBORO MA  119 CHARLES BOWER 30 RALPH ST WELLESLEY MA  158 JOCK JACKSON 65 BROWN ST WALTHAM MA   120 MICHAEL ANGELO 507 CISTINE DR WELLESLEY MA  149 LAURA PENMAN 45 THRUSH LN WALTHAM MA
             ROWID FKEY_DEPT_EMPLOYEE  FKEY_OFFICE_EMPLOYEE ------------------ ------------------- -------------------- X'0125060100000008' X'0125B60100000008' X'0125970100000008' X'01250A0100000008' X'0125B60100000008' X'0125970100000008' X'0125140500000008' X'0125B60100000008' X'0125A00100000008' X'0125240100000008' X'0125B60100000008' X'0125970100000008' X'01252C0100000008' X'0125B60100000008' X'0125970100000008' X'01254B0100000008' X'0125B60100000008' X'0125970100000008' X'01254B0700000008' X'0125B60100000008' X'0125970100000008'
More Information