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 .. 59 60 61 62 63 64 < 65 > 66 67 68 69 70 71 .. 241 >> Next


HASHBUCKET----(expression)---------

HH01A026

where expr, is a byte (4) data type expression.

You can call the HASHBUCKET function without any argument, for Usage Notes instance, HASHBUCKET( ). In this case it will return with the

maximum hash bucket, which is 65,536. Hash buckets range from 0 to 65,536.

If you call a HASBUCKET function with a NULL argument, for example, HASHBUCKET (NULL), the function returns the NULL value. HASHBUCKET (HASHROW (NULL)) returns the 0 hash bucket.

The following example returns the number of rows in each hash Example I bucket where C and C2 are to be the primary index of T:

SELECT HASHBUCKET (HASHROW (C1,C2)), COUNT (*) FROM T GROUP BY 1 ORDER BY 1;

The results of the following example can be treated as a histogram Example 2 of the table distribution among the AMPs:

SELECT HASHAMP (HASHBUCKET (HASHROW (C1, C2))), HASHBUCKET (HASHROW (C1,C2))

FROM T

GROUP BY 1,2 ORDER BY 1,2 ;

Teradata RDBMS for UNIX SQL Reference

5-113
Data Definition

HASHAMP Function

HASHAMP Function

The HASHAMP function returns the identification number of the IntrOducUon primary AMP corresponding to a hash bucket.

The HASHAMP function has the following form, where expr is any Form expression that can appear in the expression list of the select clause

of the SELECT statement, and is an integer:

-HASHAMP----------(expression)-

HH01A027

If you specify a type other than INTEGER for an argument of the Usage Notes HASHAMP function, you get the following run-time error:

Invalid type of argument for HASHAMP function.

If the data does not evaluate to an integer value between zero and 65535, you get the following error:

Invalid value used as an argument for HASHAMP function.

In case of error, check the data against the function being used.

You can call the HASHAMP function without any argument, for instance, HASHAMP( ). Without an argument, the function returns one less than the maximum number of AMPs on the system.

If you call a HASHAMP function with a NULL argument, for example, HASHAMP(NULL), the function returns NULL.

The following example assumes table T with one integer column B Example 1 populated with integer numbers from zero to the maximum number

of hash buckets on the system:

SELECT B, HASHAMP (B) FROM T ORDER BY 1;

This query returns the distribution of the primary buckets among the AMPs.

Another example returns the number of rows on each AMP where Example 2 C1 and C2 are to be the primary index of T:

SELECT HASHAMP (HASHBUCKET (HASHROW (C-^C2))), COUNT (*)

FROM T GROUP BY 1 ORDER BY 1;

5-114

Teradata RDBMS for UNIX SQL Reference
Data Definition

HASHBAKAMP Function

HASHBAKAMP Function

The HASHBAKAMP function returns the identification number of Introduction the fallback AMP corresponding to a hash bucket.

The HASHBAKAMP function has the following form, where FOTm expression is any expression that can appear in the expression list of

the select clause of the SELECT statement:

----HASHBAKAMP------(expression)--------

HH01A028

If you specify a type other than INTEGER for an argument of the Usage Notes HASHBAKAMP function, you get the following run-time error:

Invalid type of argument for HASHBUCKET function.

You can call the HASHBAKAMP function without any argument, for instance, HASHBAKAMP( ). Without an argument, the function returns one less than the maximum number of fallback AMPs on the system.

If you call a HASHBAKAMP function with a NULL argument, for example, HASHBAKAMP(NULL), the function returns NULL.

The following example assumes table T with one integer column B Example populated with integer numbers from zero to the maximum number

of hash buckets on the system:

SELECT B, HASHBAKAMP (B) FROM T ORDER BY 1;

This query returns the distribution of the fallback buckets among the AMPs.

Teradata RDBMS for UNIX SQL Reference

5-115
Data Definition

HASHBAKAMP Function

5-116

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

Chapter 6

SQL Expressions

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

About This Chapter

About This Chapter

This chapter describes the following types of expressions:

Arithmetic operators

Arithmetic functions

Date and time expressions (EXTRACT function, ADD_MONTHS function)

Aggregate operators

Character string expressions Concatenation operator String functions SUBSTRING and SUBSTR SUBSTR with KanjiEUC and KanjiEBCDIC MSUBSTR INDEX MINDEX TRIM

VARGRAPHIC CHAR2HEXINT UPPER Logical Expressions

Comparison operators IN and NOT IN IS NULL IS NOT NULL LIKE operator

Japanese character site LIKE operator EXISTS

Conditional

CASE

Built-in values

Indicator values in Arithmetic Expressions

Teradata RDBMS for UNIX SQL Reference

6-1
SQL Expressions

About This Chapter

Expressions allow you to perform arithmetic and logical operations, and to generate new values or Boolean results from constants and stored values.
Previous << 1 .. 59 60 61 62 63 64 < 65 > 66 67 68 69 70 71 .. 241 >> Next