Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 179 180 181 182 183 184 < 185 > 186 187 188 189 190 191 .. 241 >> Next


---IISING-------J-

USING--------(-- name data type declaration -

data type attribute -

- )---------------------request _ >!

FF07R081

where:

Syntax Element... Specifies . . .
name the name of a client system variable that is to be referenced as a parameter in the SQL request associated with the modifier. Each name must be unique. In the request, the variable name is prefixed by the colon (:) character. During processing, :name is replaced by a constant value read from the client system data record.
data type declaration the data type of the constant value and may be used to set a default value. See Chapter 5, Table 5-1 for a list of data types.

8-260

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

USING Modifier

Syntax Element... Specifies . . .
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: CASESPECIFIC NOT CASESPECIFIC UPPERCASE Type attributes are a Teradata RDBMS extension to ANSI SQL. When the SQL flagger is enabled, they are flagged. See for a list of the data attributes, and options that can be used to define the constant value.
request the SQL request with which the USING modifier is associated. This may be a multi-statement request, any single-statement request except a DDL statement, or an explicit transaction (see the Usage Notes following).

Use the first (upper) form of the syntax diagram for all new scripts. In a USING clause, each variable name must be unique.

The variable construct (:name) is allowed anywhere a character, numeric, or byte constant is allowed. The constant values in the client system data record must match, item for item, the USING description of those values. Also, the constants in the data record must appear in the order in which they are defined in the USING clause.

One USING clause can only modify one request. If several requests are to use client system variables, each request must be associated with its own USING clause (see Examples).

A USING modifier can be associated with one of the following types of queries:

Any single-statement request except a DDL statement

A multi-statement request

An explicit transaction. If the modifier is associated with an explicit transaction, the USING keyword must appear as the first keyword in that transaction.

If the first statement or request references a USING variable, the USING clause should immediately precede the BEGIN TRANSACTION statement (see Examples).

Variables

One Using Call Per Request

Teradata RDBMS for UNIX SQL Reference

8-261
Teradata SQL Syntax Guide

USING Modifier

The following examples illustrate the use of the USING modifier:

Examples

Here, the USING modifier defines the client system variables Example 1 :emp_name and :emp_number as, respectively, a character constant

and a numeric constant. The variables are replaced by values from a client system data record when the accompanying INSERT statement is processed.

USING (emp_name CHAR(40), emp_number INTEGER)

INSERT INTO Employee (Name, EmpNo)

VALUES (:emp_name, :emp_number) ;

The INSERT statement (in record mode on an IBM Series/370) would be transmitted to the Teradata RDBMS with an appended 44-byte data record consisting of a 40-byte EBCDIC character string, followed by a 32-bit integer.

Here, the USING modifier establishes three variables whose Example 2 constant values are used both for data input and as WHERE

conditionals in a multi-statement request:

USING (var1 CHAR, var2 CHAR, var3 CHAR)

INSERT INTO TestTabU (C1) VALUES (:var1)

; INSERT INTO TestTabU (C1) VALUES (:var2)

; INSERT INTO TestTabU (C1) VALUES (:var3)

; UPDATE TestTabU SET C2 = C1 + 1 WHERE C1 = :var1

; UPDATE TestTabU SET C2 = C1 + 1 WHERE C1 = :var2

; UPDATE TestTabU SET C2 = C1 + 1 WHERE C1 = :var3 ;

In this example, the USING modifier defines a variable for use with

Example 3 an explicit transaction that reads character strings from a disk file

and inserts them in signed zoned decimal format.

The USING modifier precedes the BEGIN TRANSACTION statement, while the BEGIN TRANSACTION statement and the statement associated with the USING clause are entered as one multi-statement request.

USING (zonedec CHAR(4))

BEGIN TRANSACTION ; INSERT INTO Dectest

(Colz = :zonedec (DECIMAL(4), FORMAT '9999S')) ;

USING (zonedec CHAR(4))

INSERT INTO Dectest

(Colz = :zonedec (DECIMAL(4), FORMAT '9999S')) ;

USING (zonedec CHAR(4))

INSERT INTO Dectest

(Colz = :zonedec (DECIMAL(4), FORMAT '9999S')) ;

ET;

In BTEQ applications, USING can be used with the .REPEAT command to perform multiple insertions automatically.

8-262

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

WHERE Clause

WHERE Clause

The WHERE clause of the SELECT statement selects rows that satisfy a conditional expression.
Previous << 1 .. 179 180 181 182 183 184 < 185 > 186 187 188 189 190 191 .. 241 >> Next