Generated Schemas Based on SQL SELECT Statements
Teradata PT will also generate a DEFINE SCHEMA statement based on the result columns of an SQL SELECT statement:
DEFINE SCHEMA PROD_EXT FROM SELECT 'Select a,b,c,sum(d) from Products;';
DEFINE SCHEMA TRANS FROM SELECT OF OPERATOR EXPORT2;
DEFINE SCHEMA Invoice_Counts FROM SELECT DELIMITED 'Select a, b
from Invoice;' ;
DEFINE SCHEMA PROD FROM SELECT DELIMITED OF OPERATOR EXPORT2;
The first DEFINE SCHEMA statement requests Teradata PT to pass the specified SQL SELECT statement to the Teradata Database and generate a fully-specified DEFINE SCHEMA statement from the definitions of the result columns of the SQL SELECT statement, as determined by the Teradata Database.
The second DEFINE SCHEMA statement requests Teradata PT to obtain the SQL SELECT statement that is the specified value of the SelectStmt attribute of the script-defined operator named EXPORT2, to pass it to the Teradata Database, and then to generate a fully-specified DEFINE SCHEMA statement from the result columns of that SQL SELECT statement.
The third and fourth DEFINE SCHEMA statements use the DELIMITED keyword for generating delimited schema.The DELIMITED keyword can be specified after the SELECT keyword if the Teradata PT is to generate a delimited file format version of the Teradata PT schema. In this case, all the columns in the Teradata PT-generated schema will be of type VARCHAR regardless of the table specified in the Select statement or SelectStmt attribute of operator.
The capability to generate schemas from SQL SELECT statements can be quite useful for complex data extractions, for example SELECT statements involving joins, and for tables with column-by-column character encodings, for which the Teradata Database can be relied on to calculate the correct column length values.
There is no functional difference between a schema defined via an SQL SELECT statement and a schema defined by a fully-coded DEFINE SCHEMA statement.