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 .. 83 84 85 86 87 88 < 89 > 90 91 92 93 94 95 .. 241 >> Next

CASE  WHEN  search_condition1  THEN  resultl  WHEN (A)

(A^search_condition2THEN  result2 ELSEresult_n  END ^>|

HH01A053

The result of the expression is result1 if search_condition1 is met, result2 if search_condition2 is met, ... and result_n if none of the conditions are met.

The following statement is equivalent to the Valued form given in the example above.

SELECT SUM(

CASE WHEN part='1'

THEN cost ELSE 0

END)/SUM(cost)

FROM t;

Example 2

CASE expressions may be used in place of any value-expressions.

SELECT * FROM t WHERE x = CASE WHEN y=2 THEN 1

WHEN (z=3 AND y=5) THEN 2

END;

Teradata RDBMS for UNIX SQL Reference

6-91
SQL Expressions

Searched CASE Expression

Example 3

WHEN Search Conditions

Data Type of CASE Expression

The following example uses an ELSE clause.

The system does not support embedded SELECTs in a WHEN condition.

Again, the ELSE clause is optional. If it is left out and none of the WHEN conditions are TRUE, the default value of NULL is returned.

SELECT * FROM t WHERE x = CASE WHEN y=2 THEN 1 ELSE 2 END;

WHEN search conditions have the properties listed in the following list

 Can take the form of any of the comparison operators, such as LIKE, =, <>, and so forth.

 May be a quantified predicate, such as ALL, IN ANY, and so forth.

 May also contain joins of 2 tables; for example:

SELECT CASE

WHEN t1.x=t2.x THEN t1.y ELSE t2.y END FROM t1,t2;

 May not contain any SELECT statements.

Since the values in the THEN/ELSE clauses may belong to different data types, the following rules apply to the data type of the result of

the CASE expression.
IF . . . THEN . . .
any of the THEN/ELSE expressions is of character type the CASE expression is character type, with the length equal to the maximum of all of the formats of the different data types of the THEN/ELSE expressions.
the byte, graphic, date, or time data type is found in a THEN/ELSE expression all other expressions can only have the same data type.
THEN/ELSE dataset is composed of decimals and integers all are converted to real.
no character string is found and if any of the THEN/ELSE expressions is of approximate numeric (real) the CASE expression returns a result that is of approximate numeric data type.

6-92

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

Searched CASE Expression

Error Conditions

The WHEN clause of a CASE expression is considered illegal when:

 An invalid WHEN expression is supplied in a simple CASE expression.

For example:

SELECT CASE a=1 WHEN 1 THEN 1 ELSE 0 END FROM t;

 An invalid WHEN condition is supplied in a searched CASE expression.

For example:

SELECT CASE

WHEN a THEN 1 ELSE 0 END FROM t;

 A SELECT statement (subquery) is detected in a WHEN condition of a searched CASE expression.

For example:

SELECT CASE

WHEN t.a IN (SEL u.a FROM u) THEN 1 ELSE 0 END FROM t;

Teradata RDBMS for UNIX SQL Reference

6-93
SQL Expressions

CASE Abbreviation Expressions

Introduction

CASE Abbreviation Expressions

There are two CASE abbreviation expressions:

 NULLIF

 COALESCE

6-94

Teradata RDBMS for UNIX SQL Reference
SQL Expressions NULLIF

NULLIF

Introduction

NULLIF is a CASE abbreviation expression. The expression

 NULLIF (expressionl, expression2)

HH01A094

is the equivalent of:

CASE WHEN expression! = expression2 THEN NULL ELSE expression! END

Example 1: NULLIF

Field Age IS SMALLINT.

NULLIF (Age,0)

This is the ANSI compliant form of the Teradata function NULLIFZERO(Age), and is more versatile.

Example 2: NULLIF

Field Code IS CHAR(4) and blanks indicate no value. Use

NULLIF (Code, ' ')

Teradata RDBMS for UNIX SQL Reference

6-95
SQL Expressions COALESCE

COALESCE

COALESCE is a CASE abbreviation expression

Introduction

The general form of COALESCE is the following, where m>=2. fOITO COALESCE, then, returns the first NON NULL value in the

expression list.COALESCE, then, returns the first NON NULL value in the expression list.

I---©----------1

COALESCE  (-L expression_m L)------------

HH01A056

For a simple application of COALESCE, using only two expressions, the form is the following.

---COALESCE  ( expression1, expression2 )----

HH01A054

The following example returns the named individuals home phone Example number, if present, or office phone if HomePhone is null, or

MessageService if present and both home and office phone values are null. Returns null if all three values are null.

SELECT Name,

COALESCE (HomePhone, OfficePhone, MessageService)

FROM PhoneDir;

6-96

Teradata RDBMS for UNIX SQL Reference
SQL Expressions Built-In Values

Introduction

Built-In Values

Built-in values are predefined values. The built-in values include the following:

 DATE

 TIME

 USER

 NULL

The built-in values, except for NULL, can be used anywhere that a constant can appear.
Previous << 1 .. 83 84 85 86 87 88 < 89 > 90 91 92 93 94 95 .. 241 >> Next