17.10 - SELECT Requests - Parallel Transporter

Teradata® Parallel Transporter Application Programming Interface Programmer Guide

Product
Parallel Transporter
Release Number
17.10
Release Date
June 2021
Content Type
Programming Reference
Publication ID
B035-2516-061K
Language
English (United States)

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.