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 .. 182 183 184 185 186 187 < 188 > 189 190 191 192 193 194 .. 241 >> Next


The SQL flagger can be enabled/disabled by carrying out the following:

FOR this software . . . USE these commands or options . . . TO turn the SQL Flager . . .
BTEQ .[SET] SESSION SQLFLAG ENTRY to entry-level ANSI
.[SET] SESSION SQLFLAG NONE off
Refer to the Teradata BTEQ Reference for more detail on using BTEQ commands.

Enabling and Disabling the SQL Flagger

Teradata RDBMS for UNIX SQL Reference

9-7
Advanced SQL

SQL Flagger

What Is Flagged and Where Is It Done?

FOR this software . . . USE these commands or options . . . TO turn the SQL Flager . . .
Preprocessor2 SQLFLAGGER(ENTRY) to entry-level ANSI
SQLFLAGGER(NONE) off
Refer to Teradata Application Programming With Embedded SQL for C, COBOL, and PL/I for more detail on setting Preprocessor options.
CLI set lang_conformance = ‘2’ set lang_conformance to ‘2’ to entry-level ANSI
set lang_conformance = ‘N’ off
Refer to Teradata Call-Level Interface Version2 for more detail on setting the conformance field.

Determination as to whether an SQL statement is entry-level ANSI or not, is made by the Teradata RDBMS. Use of Teradata SQL extensions is communicated back to client software as a Warning message.

The SQL flagger satisfies entry level ANSI requirements, which require flagging syntax deviations. Also flagged are some instances of non-ANSI behavior, detectable only after dictionary table lookup (qualifying the data being processed), particularly the fact that the Teradata RDBMS allows assignment and comparison of disparate data types (where ANSI would require the use of CAST to convert data explicitly).

Most flagging is done in the Teradata Syntaxer.

The Lexer is the next largest locus of flagging. It includes code to flag non-entry level ANSI names, non-entry level ANSI synonyms for keywords, non-entry level ANSI string literals, etc.

There are instances of flagging beyond the syntaxing phase. The most significant of these is a semantic check, catching instances in which data types are compared or converted and assigned implicitly and ANSI would give an error. The main semantic deviation of the Teradata RDBMS from ANSI is the ‘user friendly’ implicit transformation of character values to numeric or numeric values to character strings.

Throughout this SQL Reference manual, note is made of whether a command or function will be flagged as non-entry level ANSI or non-ANSI, when the SQL flagger is enabled.

9-8

Teradata RDBMS for UNIX SQL Reference
Advanced SQL

Correlated Subqueries

Introduction

Rules

Converting Existing Subquery Code to Current Subquery Code

Correlated Subqueries

A subquery is correlated when it references columns of outer tables in the enclosing (outer) query. The columns of outer tables are called outer references:

• For each row of the outer query, the values of the outer references in that row are used to evaluate the result of the subquery.

• The subquery’s result is then used to join with the associated row of the outer query based on the join constraint of the subquery.

The following is a list of rules for using correlated subqueries:

• Correlated subqueries can be used to specify combinations of equality and inequality between an outer query’s tables and a subquery’s tables.

SELECT * FROM T1 WHERE X < ALL (SELECT Y FROM T2 WHERE

T2.N=T1.N);

• Correlated subqueries can be nested:

SELECT * FROM T1 WHERE X =

(SELECT Y FROM T2 WHERE T2.N=T1.N AND T2.M = (SELECT Y FROM T3

WHERE T3.N=T2.M AND T1.L=T3.L));

• A correlated subquery can specify the same table in its FROM clause as a table specified in the FROM clause of some outer query. To differentiate between the references to the inner and outer tables, one table has to be renamed with an alias.

SELECT * FROM T1 A WHERE X < (SELECT AVG(T1.X) FROM T1 WHERE

T1.N=A.N);

SELECT * FROM T1 WHERE X < (SELECT AVG(A.X) FROM T1 A

WHERE T1.N=A.N);

• Because existing applications based on prior releases, are using subqueries and associated rules, the restriction requiring that all tables referenced in the main query, or in correlated subqueries as an outer table defined in the main query, have to defined in the FROM clause of the main query, has been lifted. The FROM clause requirement is still required for all other queries.

Take note of the following discussion for converting existing subquery code to current subquery code:

Since some queries are run in prior releases and the current release, but return different results in the current release, subqueries coded in existing applications should be revised to follow the semantics

Teradata RDBMS for UNIX SQL Reference

9-9
Advanced SQL

Correlated Subqueries

Combining Equality and Inequality Constraints

Impact on SELECT, UPDATE, DELETE, and ABORT Statements

described in the section “Correlated Subqueries” on page 9-9 so that they will still return the desired results in all releases.

The following are guidelines on how this can be done:
Previous << 1 .. 182 183 184 185 186 187 < 188 > 189 190 191 192 193 194 .. 241 >> Next