15.00 - RANDOM - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

RANDOM

Purpose  

Returns a random integer number for each row of the results table.

Syntax  

where:

 

Syntax element …

Specifies …

lower_bound

an integer literal to define the lower bound on the closed interval over which a random number is to be selected.

The limits for lower_bound range from -2147483648 to 2147483647, inclusive.

lower_bound must be less than or equal to upper_bound.

upper_bound

an integer literal to define the upper bound on the closed interval over which a random number is to be selected.

The limits for upper_bound range from -2147483648 to 2147483647, inclusive.

upper_bound must be greater than or equal to lower_bound.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Result Type and Attributes

The data type, format, and title for RANDOM(x,y) are as follows.

 

Data Type

Format

Title

INTEGER

Default format for INTEGER

Random(x,y)

For information on default data type formats, see SQL Data Types and Literals.

Computation

RANDOM uses the linear congruential algorithm and 48-bit integer arithmetic.

The algorithm works by generating a sequence of 48-bit integer values, Xi, using the following equation:

where:

 

This variable …

Represents …

X

a random number over a defined closed interval

n

an integer >= 0

a

0x5DEECE66D

c

0xB

%

the modulo operator

m

248

Multiple RANDOM Calls Within a SELECT List

You can call RANDOM any number of times in the SELECT list, for example:

   SELECT RANDOM(1,100), RANDOM(1,100);

Each call defines a new random value.

Restrictions

The following rules and restrictions apply to the use of the RANDOM function.

  • RANDOM can only be called in one of the following SELECT query clauses:
  • WHERE
  • GROUP BY
  • ORDER BY
  • HAVING/QUALIFY
  • RANDOM cannot be referenced by position in a GROUP BY or ORDER BY clause.
  • RANDOM cannot be nested inside aggregate or ordered analytical functions.
  • RANDOM cannot be used in the expression list of an INSERT statement to create a primary index or partitioning column value.
  • For example:

       INSERT t1 (RANDOM(1,10),...) 
     

    RANDOM causes an error to be reported in this case if the first column in the table is a primary index or partitioning column.

    Using RANDOM as a Condition on an Index

    Because the RANDOM function is evaluated for each selected row, a condition on an index column that includes the RANDOM function results in an all-AMP operation.

    For example, consider the following table definition:

       CREATE TABLE t1
          (c1 INTEGER
          ,c2 VARCHAR(9))
       PRIMARY INDEX ( c1 );

    The following SELECT statement results in an all-AMP operation:

       SELECT * 
       FROM t1 
       WHERE c1 = RANDOM(1,12);

    Example  

    Suppose you have a table named sales_table with the following subset of columns.

     

    Store_ID

    Product_ID

    Sales

    1003

    C

    20000

    1002

    C

    35000

    1001

    C

    60000

    1002

    D

    50000

    1003

    D

    50000

    1001

    D

    35000

    1001

    A

    100000

    1002

    A

    40000

    1001

    E

    30000

    The following SELECT statement returns a random number between 1 and 3, inclusive, for each row in the results table.

       SELECT store_id, product_id, sales, RANDOM(1,3)
       FROM sales_table;

    The results table might look like this.

     

    Store_ID

    Product_ID

    Sales

    RANDOM(1,3)

    1003

    C

    20000

    1

    1002

    C

    35000

    2

    1001

    C

    60000

    2

    1002

    D

    50000

    3

    1003

    D

    50000

    2

    1001

    D

    35000

    3

    1001

    A

    100000

    2

    1002

    A

    40000

    1

    1001

    E

    30000

    2