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 .. 168 169 170 171 172 173 < 174 > 175 176 177 178 179 180 .. 241 >> Next


• To rename a macro, table, or view that is not contained in the same database as the user’s current default database setting, the old and new name must be qualified with the name of the database in which the object exists.

Example 1

The following statement renames the Employee table:

RENAME TABLE Employee TO Emp;

newname

The following statement renames the Employee table if the user’s Example 2 current default database setting is other than Personnel:

RENAME TABLE Personnel.Employee TO Personnel.Emp;

8-222

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

REPLACE MACRO

REPLACE MACRO

Function

The REPLACE MACRO statement redefines an existing macro, or, if the specified macro does not exist, creates a new macro.

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

Syntax

REPLACE MACRO —macroname

U

( -Lparam_name — data_attribute — )

-AS @

¦ ( —L statement ;-L) -

where:

FF07A035

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.
data_attribute a data description 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. Possible data types and attributes are listed in Chapter 5 and in this chapter.
AS an introduction to a statement or statements that comprise the macro body.
statement; a SQL statement. Each 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 previously defined macros.

To replace an existing macro, you must have the DROP MACRO

Usage Notes privilege.

You must have the CREATE MACRO privilege if the specified macro does not exist.

Teradata RDBMS for UNIX SQL Reference

8-223
Teradata SQL Syntax Guide

REPLACE MACRO

Limit On Request Size

Example

In effect, the REPLACE MACRO statement operates as a DROP followed by a CREATE, except for the handling of access rights. All access rights that were granted directly on the original view or macro are retained.

If the specified macro does not exist, the REPLACE MACRO statement creates the view or macro. In this case, the REPLACE statement has the same effect as a CREATE MACRO statement.

Unqualified object names in macros that contain DML statements are forever bound to the creating user’s default database at the time of creation.

See also “CREATE MACRO”.

Because expansions of source text in macro definitions are made to 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.

The following example illustrates the use of REPLACE MACRO.

The following statement may be used to replace macro NewEmp (refer to the “CREATE MACRO” and “SHOW MACRO SHOW TABLE SHOW VIEW” statements):

REPLACE MACRO NewEmp(name VARCHAR(12) NOT NULL, street CHAR(30), city CHAR(20), number INTEGER NOT NULL, dept SMALLINT DEFAULT 999)

AS (INSERT INTO Employee

(Name, Street, City, EmpNo, DeptNo)

VALUES (:name, :street, :city, :number, :dept);

UPDATE Department SET EmpCount = EmpCount + 1 WHERE DeptNo = :dept ; ) ;

8-224

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

REPLACE VIEW

REPLACE VIEW

The REPLACE VIEW statement redefines an existing view, or, if the specified view does not exist, creates a new view.

REPLACE VIEW is flagged as non-ANSI when the SQL flagger is enabled.

The form of REPLACE VIEW is identical to that of the CREATE Syntax VIEW statement, except that the keyword REPLACE is substituted

for the keyword CREATE.

Teradata RDBMS for UNIX SQL Reference

8-225
Teradata SQL Syntax Guide

REPLACE VIEW

REPLACE VIEW— viewname -

- (¦i-col_name -- ) —

AS

?

A

LOCKING

LOCK

GJ

!-DATABASE-1

dbname-

I—TABLE—I

tname

Ñ Zr

VIEW

B

ROW

SEL

FOR

IN

-ACCESS -¦ EXCLUSIVE ¦

---EXCL-----

— SHARE — READ

WRITE

I-mode-H-nowait-1

ECT

¦ DISTINCT -ALL

expr-



™ I
AS tname. *



©-FROM-

AS

-joined_table ¦

tname

INNER -LEFT-RIGHT-'-FULL

OUTER

JOIN-joined_table-ON-search_cond -

OUTER CROSS JOIN—single_table AS

-( subquery) -³-----ã derived_tname

lAS-I

Single

Tables

>

Joined

Tables

—( -¦- co_name —) -

D

WHERE search_cond

lGROUP BY-

E

-co_name¦ — col_pos -expr

I- HAVING— cond -I

} Derived Tables

<D

Previous << 1 .. 168 169 170 171 172 173 < 174 > 175 176 177 178 179 180 .. 241 >> Next