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 .. 121 122 123 124 125 126 < 127 > 128 129 130 131 132 133 .. 241 >> Next


This example demonstrates a named index.

CREATE UNIQUE INDEX Ident (Name, SocSecNo) ON Employee ;

This, in turn, allows the named index to be dropped using:

DROP INDEX Ident ON Employee;

This example creates a named unique secondary index index_1. Index index_1 must not be an existing name in table table_1.

CREATE UNIQUE INDEX index_1 (field_1, field_2 ON table_1;

This example creates a named non-unique secondary index index_2. Index index_2 must not be an existing name in table table_2.

CREATE INDEX index_2 (field_1)

ON table_2;

This example creates an unnamed unique secondary index.

CREATE UNIQUE INDEX (field_1, field_2)

ON table_3;

This example creates an unnamed non-unique secondary index.

CREATE INDEX (field_1)

ON table_4;

This example demonstrates how not to use CREATE INDEX.

CREATE UNIQUE INDEX ThisIsAnExtremelyLongNamedIndex (field_1, field_2)

ON table_7;

The name for the index exceeds the maximum of 30 characters.

The system returns the message:

Name is longer than 30 characters.

Teradata RDBMS for UNIX SQL Reference

8-61
Teradata SQL Syntax Guide

CREATE MACRO

Function

Syntax

CREATE MACRO

The CREATE MACRO statement defines a set of statements that are frequently used, or that perform a complex operation. The statements in the macro body are submitted when the macro is invoked by a subsequent EXECUTE statement.

The user who executes a macro need not be aware of the tables affected by macro execution

CREATE MACRO is flagged as non ANSI when the SQL flagger is enabled.

-ã- CREATE MACRO -i—macroname-

'------CM----------1

A

(-L param_name—type declaration

-L type attribute -L

Liising modifier.\i I

- statement )

;)

USING modifier-1 ^LOCKING modifier-

LJ

-H

where:

FF07A051

Syntax Element . . . Specifies . . .
macroname the name of the new macro. If a fully qualified name is not specified, the default database is used.
param_name the name of a parameter that is replaced with a value during macro execution. When referenced in the macro body, a parameter name is prefixed by the : (colon) character. A parameter may be defined as a constant or a constant expression.
type declaration a data definition or default definition for a parameter. If a default value is not assigned, the user must specify a value for the parameter at EXECUTE time. For a list of data types see Table 5-1.
type attribute the data type attributes, such as NOT NULL, UPPERCASE, TITLE (quotestring), etc. For a list of data type attributes, see Table 5-2.

8-62

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE MACRO

Syntax Element . . . Specifies . . .
USING modifier one or more variable parameter names. A value is substituted for each parameter name when the request is processed. See also “USING Modifier”. This is an optional phrase. USING in a macro does not work when accessed via BTEQ. When running BTEQ, put USING in the EXECUTE statement. To use USING in a macro may require special programming. Refer to your NCR Field support engineer or the NCR Support Center for help.
LOCKING modifier a lock on a database, table, view or row. It overrides the default usage lock placed in response to a request. See also “LOCKING Modifier”. This is an optional phrase.
statement Specifies an SQL statement. Every statement in the macro body must be terminated by a semicolon. Parameter names referenced in the macro body are prefaced by the : (colon) character. The macro body may include EXECUTE statements to invoke other macros.

Executing a macro frees a user from having to enter the entire set of Why Use Macrc>s? statements in the macro body each time the operation defined by

these statements is needed. Macro execution simplifies an operation that is complex or must be performed frequently.

Because expansions of source text in macro definitions are made to Limit On R6quest Size fully qualify object names and to normalize expressions, it is

possible for a macro to be defined but to be unusable because of stack overflows in the Syntaxer at execution. In this case, the system returns a 3741 error.

In designing a macro, employ the USING modifier to retrieve data

Employ USING Phrase to from an external source such as a data file.

Retrieve Data

Use the : (colon) character to identify the fields whose values are to Use Colon tO Specify Value be inserted at execution time. For example, if the CREATE MACRO

Insertion Variables statement contained the following VALUES clause:

VALUES (:name, :city, :empno)

then the EXECUTE statement might contain a clause such as:

(name='Kimble G', city='Altadena', empno=8273)

Teradata RDBMS for UNIX SQL Reference

8-63
Teradata SQL Syntax Guide

CREATE MACRO

To create a macro, a user must have the CREATE MACRO privilege. Privileges The creator of a macro is automatically granted the DROP MACRO

and EXECUTE privileges WITH GRANT OPTION on the macro.
Previous << 1 .. 121 122 123 124 125 126 < 127 > 128 129 130 131 132 133 .. 241 >> Next