8.4.5 RANDOM

(1) Specification format

scalar-function-RANDOM ::= RANDOM([minimum-value,maximum-value])
 
  minimum-value ::= value-expression
  maximum-value ::= value-expression

To Page Top

(2) Explanation of specification format

minimum-value:

Specifies a minimum value in the range for generating a random number. (The minimum value is included in the range.) If this argument is omitted, minimum-value is assumed to be 0.

The following rules apply:

• Specify minimum-value in the form of a value expression. For details about value expressions, see 7.20 Value expression.

• Specify numeric data for minimum-value. For details about numeric data, see (1) Numeric data in 6.2.1 List of data types.

• If you specify only a single dynamic parameter for minimum-value, the DOUBLE PRECISION type is assumed.

maximum-value:

Specifies a maximum value in the range for generating a random number. (The maximum value is not included in the range.) If this argument is omitted, maximum-value is assumed to be 1:

The following rules apply:

• Specify maximum-value in the form of a value expression. For details about value expressions, see 7.20 Value expression.

• Specify numeric data for maximum-value. For details about numeric data, see (1) Numeric data in 6.2.1 List of data types.

• If you specify only a single dynamic parameter for maximum-value, the DOUBLE PRECISION type is assumed.

To Page Top

(3) Rules

1. The data type of the execution result is the DOUBLE PRECISION type.

2. The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).

3. If minimum-value or maximum-value is a null value, the execution result will also be a null value.

4. After converting both minimum-value and maximum-value to the DOUBLE PRECISION type, calculate the execution result.

5. If the relationship between value A specified for minimum-value and value B specified for maximum-value satisfies "A > B", minimum-value and maximum-value are automatically switched. Then, pseudorandom numbers that follow a uniform distribution in a range of values "greater than or equal to B and less than A" are returned.

6. If you specify the same value for minimum-value and maximum-value, the execution result is the value specified for minimum-value.

7. If the execution result cannot be expressed as the data type specified for the execution result, an overflow error occurs.

8. If you specify 0 for maximum-value, +0 might be returned as the execution result.

To Page Top

(4) Notes

This scalar function is not suitable for use in encryption.

To Page Top

(5) Example

Example

For table T1, determine DOUBLE PRECISION-type values that follow a uniform distribution, in the range of 1 or more and less than 10.

Note that every time you execute the SELECT statement, the values of the execution results change.

SELECT RANDOM(1,10) FROM "T1"

To Page Top

(6) List of scalar functions that return pseudorandom numbers

In addition to RANDOM, the following three scalar functions return pseudorandom numbers:

• RANDOMCURSOR

• RANDOMROW

• RANDOM_NORMAL

The following table describes the differences in the specifications among the preceding scalar functions. Use the scalar function that is most suitable for your purpose.

Table 8‒5: List of scalar functions that return pseudorandom numbers

No.	Item	Scalar function RANDOM	Scalar function RANDOMCURSOR	Scalar function RANDOMROW	Scalar function RANDOM_NORMAL
1	Format	RANDOM([minimum-value,maximum-value]) minimum-value::=value-expression maximum-value::=value-expression	RANDOMCURSOR(identification-number[,minimum-value,maximum-value]) identification-number::=unsigned-integer-literal minimum-value::=value-specification maximum-value::=value-specification	RANDOMROW(identification-number[,minimum-value,maximum-value]) identification-number::=unsigned-integer-literal minimum-value::=value-expression maximum-value::=value-expression	RANDOM_NORMAL([average-μ,standard-deviation-σ]) average-μ::=value-expression standard-deviation-σ::=value-expression
2	Distribution of pseudorandom numbers	Uniform distribution	Uniform distribution	Uniform distribution	Normal distribution
3	Range of pseudorandom numbers	Value greater than or equal to the specified minimum value and less than the specified maximum value	Value greater than or equal to the specified minimum value and less than the specified maximum value	Value greater than or equal to the specified minimum value and less than the specified maximum value	Value that follows average μ and standard deviation σ
4	Do scalar functions having the same identification number in an SQL statement always return the same value?	--	Y	N	--
5	Do scalar functions having the same identification number in a query specification return the same value for each row?	--	Y	Y	--
6	Possible specification location	value-expression	value-expression in a selection expression ORDER BY clause#	value-expression in a selection expression ORDER BY clause#	value-expression

Legend:

Y: True

N: False

--: Not applicable. No identification number can be specified.

#

The function cannot be specified for the ORDER BY clause in a WITHIN group specification or a window order clause.

To Page Top