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 .. 13 14 15 16 17 18 < 19 > 20 21 22 23 24 25 .. 241 >> Next


An activity count is meaningful for a SELECT, INSERT, UPDATE, or DELETE statement. For other SQL statements, activity count is meaningless.

The following SELECT statement

sel avg(f1) from Inventory;

in BTEQ, returns the successful response message:

*** Query completed. One row found. One column returned.

*** Total elapsed time was 1 second.

Average(f1)

14

Teradata RDBMS for UNIX SQL Reference

2-25
Data Handling Fundamentals

Warning Response

Warning Response

Introduction

An SQL warning response typically occurs with a successful request response. It indicates that either an anomaly has occurred or informs the user about the anomaly, as it may be important to the interpretation of the results returned.

Example

If nulls are included in column fl’s data, then

sel avg(f1) from Inventory;

in BTEQ, returns the successful response message with a warning about the nulls:

*** Query completed. One row found. One column returned.

*** Warning: 2892 Null value eliminated in set function *** Total elapsed time was 1 second.

Average(f1)

14

This warning response is not generated if the session is running in Teradata mode.

2-26

Teradata RDBMS for UNIX SQL Reference
Data Handling Fundamentals

Failure Response

Introduction

Example

Failure Response

A failure response includes a statement number, an error code, and an associated text string describing the cause of the failure. If one statement in a macro fails, a single failure response is returned to the client, and the results of any previous statements in the transaction are backed out.

The following SELECT statement

sel * from Inventory:;

in BTEQ, returns the failure response message:

*** Failure 3709 Syntax error, replace the ':' that follows the name with a ';'.

Statement# 1, Info =20

*** Total elapsed time was 1 second.

Teradata RDBMS for UNIX SQL Reference

2-27
Data Handling Fundamentals

Error Response (ANSI Mode Only)

Error Response (ANSI Mode Only)

Introduction

An error response occurs when the anomaly was bad enough to prevent the correct processing of the request.

In ANSI mode, an error for a request causes the request to rollback, and not the transaction.

Example 1

The following command

.set session trans ANSI;

returns the error message:

*** Error: You must not be logged on .logoff to change the SQLFLAG or TRANSACTION settings.

Example 2

Assume that the user has logged on in ANSI mode.

show table inv;

CREATE MULTISET TABLE MAIN.inv, FALLBACK,

NO BEFORE JOURNAL,

NO AFTER JOURNAL (

item INTEGER CHECK ((item >=10) AND (item <= 20) ))

PRIMARY INDEX (item);

INSERT INTO inv (12);

The following response is returned:

*** Insert completed. One row added........

INSERT INTO inv (9);

The following error response is returned:

***Error 5317 Check constraint violation: Check error in field inv.item

commit;

The following response is returned:

*** COMMIT done. ...

SELECT * FROM inv;

The following response is returned:

*** Query completed. One row found. Cone column returned. item 12

2-28

Teradata RDBMS for UNIX SQL Reference
Data Handling Fundamentals

Session Parameters

Session Parameters

Introduction

The following session parameters can be controlled:

SQL flagger: on or off

Transaction mode: Teradata (BTET) or ANSI (COMMIT)

Session collation: ASCII, EBCDIC, MULTINATIONAL, or HOST Character set: options vary with each user Express logon: enable or disable

SQL Flagger

When enabled, the SQL Flagger assists SQL programmers by notifying them of the use of non-ANSI and non-entry level ANSI SQL syntax. Syntax which is intermediate level ANSI SQL will also be flagged as “non-entry level” ANSI.

Enabling the SQL Flagger can be done regardless of whether the user is in ANSI or Teradata mode.

To set the SQL Flagger on or off, use the .SET SESSION command in

BTEQ.
To set this level of flagging . . . Set the flag variable to this value . . .
None SQLFLAG none
Entry level sqlflag entry
Intermediate level SQLFLAG INTERMEDIATE

See also Chapter 9, “Advanced SQL,” for more detail on the use of the SQL Flagger, and Teradata BTEQ Reference for details on using BTEQ.

If using SQL in other application programs, refer to that application’s reference manual for instructions on enabling the SQL Flagger.

Users can run transactions in either Teradata or ANSI mode and Transaction Mode 2 these modes can be set or changed.

To set the transaction mode, use the .SET SESSION command in BTEQ.

To set this type of transaction mode . . . Set the variable to this value . . .
Teradata TRANSACTION BTET
ANSI TRANSACTION ANSI

Teradata RDBMS for UNIX SQL Reference

2-29
Data Handling Fundamentals

Session Parameters

Collation Sequence

See also Chapter 9, “Advanced SQL,” for more detail on the use of the Transaction Semantics, and Teradata BTEQ Reference for details on using BTEQ.
Previous << 1 .. 13 14 15 16 17 18 < 19 > 20 21 22 23 24 25 .. 241 >> Next