Expansion of Table-procedure-reference

The expanded parameters of table-procedure-reference represent qualified or unqualified table procedure identifiers together with an optional set of parameter values.
The expanded parameters of table-procedure-reference represent qualified or unqualified table procedure identifiers together with an optional set of parameter values.
This article describes the following information:
Expansion of table-procedure-reference
 ─────────────────────────►       └─ 
. ─┘ ►►─┬────────────────────────────────────────────────────────────────┬─────────►◄    │     ┌───────────── , ─────────────┐                            │    └─ ( ─▼── 
 ──┴─ ) ────────────────────────┘
Expansion of parameter-specification
 ────────────────────────────►◄       └─ 
 ── = ─┘
  • schema-name
    Specifies the schema with which the table procedure identified by
    is associated.
For more information about using a schema name to qualify a table procedure, see Identifying Entities in Schemas.
  • table-procedure-identifier
    Identifies a table procedure defined in the dictionary.
  • parameter-specification
    Assigns a value to a parameter in a table procedure reference. You can use both the positional (with NO
    parameter name
    ) and the non-positional (with
    parameter name
    ) forms of parameter specification in a single table procedure reference. If you use a non-positional parameter specification, all remaining parameter specifications in the parameter list MUST be non-positional. Positional parameter specifications are assumed to correspond to the declared parameters of a table procedure, in the sequence of their declaration.
  • parameter-name
    Specifies the name of a parameter associated with the table procedure.
  • value-expression
    Specifies the value to assign to the parameter. See Expansion of Value-expression for more information.
Referencing Table Procedures
You can code references to SQL table procedures in SQL SELECT, INSERT, UPDATE, and DELETE statements in the same manner as references to SQL tables and views. The parameters of such table procedures act as columns in an SQL table or view. You can reference them in SELECT list expressions, WHERE clauses, UPDATE statement SET clauses, and the column list of the INSERT statement. You can also reference table procedures in the SQL CALL statement. The output parameter values return as a result set.
During SQL DML processing, CA IDMS issues calls to the corresponding external routines at the same time at which it would perform database access to satisfy standard table references. This permits the simulation of SQL DML activity on external data storage structures (for example, non-SQL-defined CA IDMS databases or VSAM file systems) managed by the table procedures.
Assigning Parameter Values with the WHERE Clause
An alternative method for assigning values to parameters of table procedures is through the WHERE clause. An expression of the form
parameter name
value specification
coded in the WHERE clause is considered to be equivalent to a parameter assignment using table procedure reference syntax. This allows table procedure references to be coded without a parenthesized parameter list, just like standard table or view references.
This method is useful particularly if you are coding SQL statements in generic SQL environments, such as CA Visual Express, which do not support CA IDMS SQL extensions, such as table procedures.
When you use the WHERE clause to assign parameter values, the following conditions must be met for the parameter to be assigned a value:
  • It must appear in an "=" comparison, not, for example, with >, <, >=. or <=.
  • The "=" comparison in which the parameter appears can be combined only with other factors in the WHERE clause using an AND operator. Use of an OR operator or preceding the "=" comparison with the NOT keyword means that no value is assigned to the parameter.
For more information about assignment of values to table procedure parameters, see Table Procedure Parameters.
Qualified Table Procedure Reference
In the following SELECT statement, the table procedure reference is qualified:
select * from emp.org;
Table Procedure Reference with Keyword Parameter Values
In the following SELECT statement, values are supplied for the EMP_ID and MGR_ID parameters using keyword notation:
select * from org (emp_id=127, mgr_id=7);
Table Procedure Reference with Positional Parameter Values
In the following SELECT statement, a value is supplied for the first parameter associated with the ORG table procedure:
select * from org (7);
Table Procedure Reference with Parameter Values Specified in the WHERE Clause
In the following SELECT statement, parameter values are supplied through the WHERE clause. This example is identical to the example above that uses keyword notation.
select * from org .    where emp_id=127 and mgr_id=7;
For more information about defining table procedures, see CREATE TABLE PROCEDURE and Defining and Using Table Procedures.