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

NULL = NULL Unknown
NULL <> NULL Unknown

Note that if the argument of the NOT operator is unknown, the result is unknown. This translates to false as a final boolean result.

Considered a CASE abbreviation expression. Similar in function to NULLIF 2 NULLIFZERO but more versatile. See Chapter 6, “SQL

Expressions” under the topic “CASE Expression”.

COALESE is also considered a CASE abbreviation expression. It COALESCE 2 returns the first NON NULL in an expression list. See Chapter 6,

“SQL Expressions” under the topic “CASE Expression”.

When records are sorted using an ORDER BY clause, null is sorted Null is the LOwest Value as the lowest value.

Teradata RDBMS for UNIX SQL Reference

2-21
Data Handling Fundamentals

Operations on Null

Searching for Nulls

Searching for Non Nulls

Searching for Nulls and Non Nulls Together

Teradata RDBMS Fills in Values in NULL columns

If any row has a null in the column being grouped, then all rows with a null are placed into one group.

The IS NULL operator tests row data for the presence of nulls. For example, to search for the names of all employees who have a null in the deptno column, you could enter the statement:

SELECT name FROM employee WHERE deptno IS NULL ;

This query produces the names of all employees with a null in the deptno field. Because all employees contained in the employee table have been assigned to a department, no rows are returned.

To exclude nulls from the results of a query, use the IS NOT NULL operator.

For example, to search for the names of all employees with a value other than null in the jobtitle column, enter the statement:

SELECT name FROM employee

WHERE jobtitle IS NOT NULL ;

The result of this query is the names of all employees with a value other than null in the jobtitle column. When you enter this statement, the names of all employees are returned because every employee has been given a job title.

To search for nulls and non-nulls in the same statement, the search condition for nulls must be included separately from any other search conditions. For example, to select the names of all employees with the job title of vice pres, manager, or null, enter either of the following statements:

SELECT name, jobtitle FROM employee

WHERE jobtitle IN ('Manager' or 'Vice Pres')

OR (jobtitle IS NULL) ;

or

SELECT name, jobtitle FROM employee WHERE jobtitle IS NULL

OR jobtitle IN ('Manager' or 'Vice Pres') ;

When the Teradata RDBMS returns information to the client system in record mode, it is necessary to fill null items with some value for the underlying column. This is required because client system languages do not recognize null as a value. Table 2-1 shows the values returned for declared column types.

2-22

Teradata RDBMS for UNIX SQL Reference
Data Handling Fundamentals

Operations on Null

Table 2-1 Returned Nulls

Column Type Value Returned
INTEGER 0
FLOAT 0
DECIMAL 0
REAL 0
DOUBLE PRECISION 0
NUMERIC 0
CHAR[(n)] Blank if n omitted, or n blanks
BYTE[(n)] 0 byte if n omitted, or n binary zero bytes
VARBYTE(n) Zero-length byte string
VARCHAR(n) Zero-length character string
DATE 0
GRAPHIC[(n)] If n is omitted, one double-byte zero; otherwise, n double-byte zeros
VARGRAPHIC(n) Zero-length graphic string

Note: The values returned for NULLS are not distinguishable from legal NON NULLs. It is normal to access the data in IndicData mode, in which additional information is returned to the Host, flagging NULLs. BTEQ, for example, displays NULLs in reports as ‘?’, based on knowing whether values are NULL.

Teradata RDBMS for UNIX SQL Reference

2-23
Data Handling Fundamentals

Statement Response

Introduction

Statement Response

The Teradata RDBMS responds to an SQL statement with one of the following responses:

• Success response, with optional Warning response

• Failure response

• Error response (ANSI mode only)

Depending on the type of statement, the RDBMS also responds with one or more rows of data.

A response to a request that contains more than one statement, such as a macro, is not returned to the client until all statements in the request are successfully executed.

The manner in which the response is returned depends on the interface that is being used. For example, if an application is using a language preprocessor, the activity count, warning code, error code, and fields from a selected row are returned directly to the program’s variables. If a user is working through BTEQ, a success, error, or failure response is displayed automatically.

2-24

Teradata RDBMS for UNIX SQL Reference
Data Handling Fundamentals

Success Response

Introduction

Example

Success Response

A success response contains an activity count that indicates the total number of rows involved in the result.

For example, the activity count for a SELECT statement is the total number of rows selected for the response. For a SELECT, COMMENT, or ECHO statement, the activity count is followed by the data that completes the response.
Previous << 1 .. 12 13 14 15 16 17 < 18 > 19 20 21 22 23 24 .. 241 >> Next