Apply SQLEXEC within a TABLE or MAP Statement

When used within a TABLE or MAP statement, SQLEXEC can pass and accept parameters. It can be used for procedures and queries, but not for database commands.

Syntax

This syntax executes a procedure within a TABLE or MAP statement.

SQLEXEC (SPNAME sp_name,
[ID logical_name,]
{PARAMS param_spec | NOPARAMS})

Argument	Description
`SPNAME`	Required keyword that begins a clause to execute a stored procedure.
`sp_name`	Specifies the name of the stored procedure to execute.
`ID logical_name`	Defines a logical name for the procedure. Use this option to execute the procedure multiple times within a `TABLE` or `MAP` statement. Not required when executing a procedure only once.
`PARAMS param_spec \| NOPARAMS`	Specifies whether or not the procedure accepts parameters. One of these options must be used (see Using Input and Output Parameters).

Syntax

This syntax executes a query within a TABLE or MAP statement.

SQLEXEC (ID logical_name, QUERY ' query ',
{PARAMS param_spec | NOPARAMS})

Argument Description

Argument	Description
`ID logical_name`	Defines a logical name for the query. A logical name is required in order to extract values from the query results. `ID` `logical_name` references the column values returned by the query.
`QUERY ' sql_query '`	Specifies the SQL query syntax to execute against the database. It can either return results with a `SELECT` statement or change the database with an `INSERT`, `UPDATE`, or `DELETE` statement. The query must be within single quotes and must be contained all on one line. Specify case-sensitive object names the way they are stored in the database, such as within quotes for Oracle case-sensitive names. `SQLEXEC 'SELECT "col1" from "schema"."table"'`
`PARAMS param_spec \| NOPARAMS`	Defines whether or not the query accepts parameters. One of these options must be used (see Using Input and Output Parameters).

ID logical_name

Defines a logical name for the query. A logical name is required in order to extract values from the query results. ID logical_name references the column values returned by the query.

QUERY ' sql_query '

Specifies the SQL query syntax to execute against the database. It can either return results with a SELECT statement or change the database with an INSERT, UPDATE, or DELETE statement. The query must be within single quotes and must be contained all on one line. Specify case-sensitive object names the way they are stored in the database, such as within quotes for Oracle case-sensitive names.

SQLEXEC 'SELECT "col1" from "schema"."table"'

PARAMS param_spec |
NOPARAMS

Defines whether or not the query accepts parameters. One of these options must be used (see Using Input and Output Parameters).

If you want to execute a query on a table residing on a different database than the current database, then the different database name has to be specified with the table. The delimiter between the database name and the tablename should be a colon (:).

The following are some example use cases:

select col1 from db1:tab1
select col2 from db2:schema2.tab2
select col3 from tab3
select col3 from schema4.tab4