Example : USING Request Modifier
In this example, the USING request modifier establishes three variable parameters whose constant values are used both for data input and as WHERE clause predicates in a multistatement request:
.SET RECORDMODE ON
.IMPORT DATA FILE = r13sales.dat;
USING (var_1 CHARACTER, var_2 CHARACTER, var_3 CHARACTER)
INSERT INTO testtabu (c1) VALUES (:var_1)
;INSERT INTO testtabu (c1) VALUES (:var_2)
;INSERT INTO testtabu (c1) VALUES (:var_3)
;UPDATE testtabu
SET c2 = c1 + 1
WHERE c1 = :var_1
;UPDATE testtabu
SET c2 = c1 + 1
WHERE c1 = :var_2
;UPDATE testtabu
SET c2 = c1 + 1
WHERE c1 = :var_3;
Example : USING Request Modifier with Variables
In this example the USING request modifier defines the variables :emp_name and :emp_number as, a CHARACTER constant and an INTEGER numeric constant, respectively. The USING variables are replaced by values from a client system data record when the system processes the accompanying INSERT request.
.SET RECORDMODE ON
.IMPORT DATA FILE = r13sales.dat;
USING (emp_name CHARACTER(40),
emp_number INTEGER)
INSERT INTO employee (name, empno)
VALUES (:emp_name, :emp_number);
The INSERT request (in Record Mode on an IBM mainframe) is transmitted to Teradata Database with an appended 44‑byte data record consisting of a 40‑byte EBCDIC character string followed by a 32‑bit integer.
Example : USING Request Modifier Reads Character Strings
In this example, the USING request modifier defines a variable parameter for use with an explicit transaction that reads character strings from a disk file and inserts them in signed zoned decimal format.
The USING request modifier precedes the BEGIN TRANSACTION statement, while the BEGIN TRANSACTION statement and the request associated with the USING clause are entered as one multistatement request.
.SET RECORDMODE ON
.IMPORT DATA FILE = r13sales.dat;
USING (zonedec CHARACTER(4))
BEGIN TRANSACTION
;INSERT INTO dectest (colz = :zonedec (DECIMAL(4),FORMAT '9999S')) ;
USING (zonedec CHARACTER(4))
INSERT INTO Dectest
(colz = :zonedec (DECIMAL(4), FORMAT '9999S')) ;
USING (zonedec CHARACTER(4))
INSERT INTO Dectest
(colz = :zonedec (DECIMAL(4), FORMAT '9999S')) ;
END TRANSACTION;
In BTEQ applications, you can combine USING request modifiers with the .REPEAT command to perform multiple insertions automatically.
Example : Inline Mode Processing of a Large Object
The following example passes the BLOB values for column b inline:
.SET INDICDATA ON
.IMPORT DATA FILE=mar08sales.dat
USING (a INTEGER,
b BLOB(60000))
INSERT INTO mytable VALUES (:a, :b);
Example : Deferred Mode Processing of a Large Object
The following example passes the CLOB values for column b in deferred chunks:
.SET INDICDATA ON
.IMPORT DATA FILE=mar08sales.dat
USING (a INTEGER,
b CLOB AS DEFERRED)
INSERT INTO mytable VALUES (:a, :b);
Example : Deferred Mode Processing of CLOBs Using the DEFERRED BY NAME Phrase
The following example shows the use of the BY NAME phrase for deferred mode processing of large objects using BTEQ and CLOBs.
.SET INDICDATA ON
CREATE TABLE tabf (
i1 INTEGER,
v1 VARCHAR(256));
INSERT INTO tabf (1, 'c:\temp\vconfig.txt');
.EXPORT INDICDATA FILE=scg0720.dat
SELECT *
FROM tabf;
.EXPORT reset
CREATE TABLE tabc (
i1 INTEGER,
c1 CLOB);
.IMPORT INDICDATA FILE=scg0720.dat
USING (a INTEGER,
b CLOB AS DEFERRED BY NAME)
INSERT INTO tabc (:a, :b);
Example : DEFERRED MODE Processing of BLOBs Using the DEFERRED BY NAME Phrase
The following example shows the use of the BY NAME phrase for deferred mode processing of large objects using BTEQ and BLOBs.
CREATE TABLE tabf2 (
i1 INTEGER,
v1 VARCHAR(256));
INSERT INTO tabf2 (1, 'c:\temp\data.dat');
.SET INDICDATA ON
.EXPORT INDICDATA FILE=scg0720.dat
SELECT *
FROM tabf;
.EXPORT RESET
CREATE TABLE tabb (
i1 INTEGER,
c1 BLOB);
.IMPORT INDICDATA FILE=scg0720.dat
USING (a INTEGER,
b BLOB AS DEFERRED BY NAME)
INSERT INTO tabb (:a, :b);
Example : Locator Mode Processing of a Large Object
The first example shows how locator b is used to copy an existing, Teradata platform‑resident BLOB from its current base table location into a base table named mytable without any data transferred to the client.
USING (a INTEGER,
b BLOB AS LOCATOR)
INSERT INTO mytable VALUES (:a, :b);
The second example shows the BLOB data identified by locator b being returned to the client.
.SET INDICDATA ON
.EXPORT INDICDATA FILE=udbsales.dat
USING (b BLOB AS LOCATOR)
SELECT :b;
.EXPORT RESET
Example : Using a Locator Multiple Times Within a Session
This example shows the same locator being used in more than one request within a session:
USING (a CLOB AS LOCATOR)
SELECT :a;
USING (a INTEGER,
b CLOB AS LOCATOR)
INSERT INTO tab2 (:a, :b);
Example : Iterated Requests
The following example shows one way of performing an iterated request in BTEQ:
.IMPORT DATA FILE = r13sales.dat;
.REPEAT RECS 200 PACK 100
USING (pid INTEGER, pname CHAR(12))
INSERT INTO ptable VALUES(:pid, :pname);
The .REPEAT command specifies that BTEQ should read up to 200 data records and pack a maximum of 100 data records with each request.
Example USING Request Modifier with TOP Operator
This example passes the value for n in the TOP n operator in its SELECT request using the INTEGER parameter a as defined in the USING request modifier.
.SET RECORDMODE ON
.IMPORT DATA FILE=employee.dat
USING (a INTEGER)
SELECT TOP :a *
FROM employee;
Example : Non-Support for Iterated Requests With TOP n
The following request indicates the lack of support for specifying a TOP n value as a parameterized variable in a USING request modifier for iterated arrays.
.REPEAT 1 PACK 5
BTEQ -- Enter your DBC/SQL request or BTEQ command:
USING (a INTEGER, b INTEGER, c INTEGER)
SELECT TOP :a *
FROM t1;
*** Starting Row 0 at Tue Aug 05 11:46:07 2008
*** Failure 6906Iterated request:Disallowed statement type (TOP N).
Statement# 1, Info =0
*** Total elapsed time was 1 second.
Example : Dynamic UDT Expressions
The following example shows one way to use a dynamic UDT expression in a table UDF with a USING request modifier:
.IMPORT INDICDATA FILE lobudt003.data
USING(p1 INTEGER, p2 INTEGER, p3 BLOB)
SELECT *
FROM TABLE(dyntbf003(:p2,
NEW VARIANT_TYPE(:p2 AS a,:p3 AS b))) AS t1
ORDER BY 1;
*** Query completed. 2 rows found. 3 columns returned.
*** Total elapsed time was 1 second.
R1 R3 R2
--------- ------- ----------------------------------------
10 0 1111111111111111111111111111111111111110
11 1 1111111111111111111111111111111111111110
Example : USING and SQL UDF Invocation
This example invokes the SQL UDF value_expression in a USING clause‑based SELECT request.
USING (a INTEGER, b INTEGER)
SELECT test.value_expression(:a, :b) AS cve
FROM t1
WHERE t1.a1 = :a
AND t1.b1 = :b;
This example invokes the SQL UDF value_expression in a USING clause‑based DELETE request.
USING (a INTEGER, b INTEGER)
DELETE FROM t1
WHERE test.value_expression(:a, :b) > t1.a1;
This example, which invokes the SQL UDF value_expression in a USING clause‑based SELECT request with mismatches of the data types of the arguments, aborts and returns an error.
USING (a CHARACTER(10), b CHARACTER(10))
SELECT test.value_expression(:a, :b) AS cve
FROM t1
WHERE t1.a1 = :a
AND t1.b1 = :b;