USING Syntax | SQL Statements | Teradata Vantage - 17.05 - USING Request Modifier Syntax - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)
USING ( using_spec [,...] ) SQL_request

Syntax Elements

using_spec
using_variable_name data_type [ data_type_attribute [...] ]
  [ AS { DEFERRED [BY NAME] | LOCATOR } ]
SQL_request
The SQL request with which the USING request modifier is associated.
This can be a multistatement request, any non-DDL single-statement request, or an explicit transaction.
You can pass the value for n in a TOP n operator in an SQL request by specifying a USING-defined parameter. See Example: USING Request Modifier with TOP Operator.
All USING variable names in the SQL request must be preceded by a COLON character.
using_variable_name
Name of a USING variable that is to be referenced as a parameter in the SQL request associated with the modifier.
Each name specified must be unique.
During processing, each using_variable_name in the SQL request is replaced by a constant value read from the client application.
You can also use dot notation to access DATASET and JSON data. See Teradata Vantage™ - DATASET Data Type, B035-1198 and Teradata Vantage™ - JSON Data Type, B035-1150.
data_type
Data type of the constant value substituted for using_variable_name.
UDT types are not supported directly. See USING Support for UDTs for details and information about conversion workarounds.
This can be used to set a default value.
See Teradata Vantage™ - Data Types and Literals, B035-1143 for a list of data types.
data_type_attribute
One of the data type attributes listed below.
The only data type attributes that affect the handling of values imported by USING are the following:
  • CASESPECIFIC
  • NOT CASESPECIFIC
  • UPPERCASE
The server character set attribute (for example CHARACTER SET LATIN) cannot be part of data_type_attribute.
Type attributes are Teradata extensions to the ANSI SQL:2011 standard.
DEFERRED
LOB data is sent to Vantage from the client application when requested, and separately from other row data.
This option is only valid for LOB data.
See USING Support for Large Objects for more information.
BY NAME
Interpret the data in the USING row as a client file name. When the system detects the BY NAME option following AS DEFERRED, the following events occur in the order indicated:
The database sends the file name back to the client.
The client opens the file.
The client sends the data in the file to the database.
LOCATOR
The client application passes a locator for the LOB, generated from a previous SELECT request for the session, to the database.
This option is only valid for LOB data.
See USING Support for Large Objects for more information.