17.00 - SELECT Requests - Parallel Transporter

Teradata® Parallel Transporter Application Programming Interface Programmer Guide

prodname
Parallel Transporter
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-2516-220K

A SELECT request is one or more Teradata SQL SELECT statements that may be optionally preceded by a LOCKING modifier. A SELECT request can have multiple SELECT statements.

When creating SELECT requests for an Export job:

IF your SELECT request... THEN...
Has multiple SELECT statements The database may execute them in parallel, but still returns the response data for the first statement first, then the response data for the second, and so on. If the response rows have a different structure, the Export driver responds with an error message and terminates.

For example, Table1 has columns A, B, and C. Table2 has columns A, B, D, and E. You require data from columns A and B in Table1 and columns A and B in Table2.

The following statement is not a valid multi-SELECT statement for the Export driver.

SELECT A, B from Table1; SELECT a from Table2;

However,

SELECT A, B from Table1; SELECT A, B FROM Table2;

would be valid for the Export driver. The corresponding output schema is required to be defined with columns A and B.

Uses a LOCKING modifier The specified lock remains in effect during the execution of all statements within the request containing the modifier.
The database:
  • Implements all resource locks for the entire request before executing any of the statements in the request.
  • Maintains the locks until all of the response data for the request has been moved to spool tables.
The database removes the resource locks before returning the data to your client system.
  • The following is an example of a valid SELECT request using the LOCKING modifier:
    LOCKING TABLE MYTABLE FOR ACCESS SELECT COL1, COL2 FROM MYTABLE;

    Note that the LOCKING modifier is allowed to precede the SELECT statement.

Uses an ORDER BY clause You must specify one Export instance.

The following is an example of a valid ORDER BY clause:

SELECT COL1, COL2 FROM MYTABLE ORDER BY COL1;

SELECT Request Restrictions

Export SELECT requests cannot:
  • Specify a USING modifier.
  • Access non-data tables, such as SELECT DATE or SELECT USER.
  • Be satisfied by a single AMP, such as SELECT statement with a constraint containing an equality condition on the primary index or unique secondary index columns of a table.
  • Contain character large object (CLOB) or binary large object (BLOB) data type.
  • Use a WITH option to generate total or subtotal response rows. This option is used for report generation, and it produces response rows for the aggregations that are not in the same format as the detail rows.
  • Utilize variable substitution.
  • Contain JSON (JavaScript Object Notation) data type.
  • Contain XML data type.

Other than these restrictions, the database parses and processes SELECT statements from the Export driver as it would from any other data access facility. For a complete description of the SELECT statement, see Teradata SQL reference documentation for your operating system.