RANDOM in Teradata

  1. Forget Code
  2. Teradata
  3. RANDOM
RANDOM returns a random integer between the given bounds.
It is a Teradata extension to the ANSI SQL-99 standard.

Syntax:
SELECT RANDOM ( lower_bound, upper_bound )

Bounds are integer constants. They lie between -2147483648 and 2147483647.

What it is doing actually ?

RANDOM follows congruential algorithm by following the below equation
Xn+1 = (aXn + c) % m

where,
X - a random number over a defined closed interval
n - an integer >= 0
a - 0x5DEECE66D (Pre-determined)
c - 0xB (Pre-determined)
%- the modulo operator
m - 2^48

Special feature:

You can call RANDOM any number of times in the SELECT list, for example:
SELECT RANDOM(1,50), RANDOM(1,100);

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 used in the expression list of an INSERT statement.
• RANDOM cannot be nested inside aggregate functions.

ForgetCode Examples:

Employee table is now having the following data,

 SELECT EmpID,EmpName FROM EMPLOYEE;


EmpID    EmpName
1    Rachel
2    Rider
3    Joe



 SELECT EmpID,EmpName,RANDOM(1,3) FROM EMPLOYEE;


EmpID    EmpName        RANDOM
1    Rachel        3
2    Rider        1
3    Joe        2


..