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

| — | — |

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"'

{::nomarkdown} <pre class="pre codeblock">PARAMS param_spec

NOPARAMS</code></pre> {:/}

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