8.4.7 RANDOMROW

RANDOMROW returns values in accordance with the following rules:

This function returns pseudorandom numbers that follow a uniform distribution and are greater than or equal to the value specified for the minimum value and less than the value specified for the maximum value.
If a query specification contains multiple RANDOMROW functions for which the same identification number is specified, those functions return the same values for each result row of the query specification.

There are some scalar functions, including RANDOMROW, that return pseudorandom numbers. Check the differences in the specifications among those scalar functions that return pseudorandom numbers, and then use the scalar function that is most suitable for your purpose. For details about the differences in the specifications among the scalar functions that return pseudorandom numbers, see (6) List of scalar functions that return pseudorandom numbers in 8.4.5 RANDOM.

Organization of this subsection

(1) Specification format
(2) Explanation of specification format
(3) Rules
(4) Notes
(5) Example

(1) Specification format

scalar-function-RANDOMROW::=RANDOMROW(identification-number[,minimum-value,maximum-value])
 
  identification-number::=unsigned-integer-literal
  minimum-value::=value-expression
  maximum-value::=value-expression

To Page Top

(2) Explanation of specification format

identification-number:

Specifies an integer in the range from 1 to 1000. If a query specification contains multiple RANDOMROW functions for which the same identification number is specified, those functions return the same values for each result row of the query specification.

minimum-value:

Specifies a minimum value in the range for generating a random number. (The minimum value is included in the range.) If both minimum-value and maximum-value are 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 both minimum-value and maximum-value are 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

This scalar function can be specified in the following locations:
- Selection expression in a query specification
- ORDER BY clause (except the ORDER BY clause in a WITHIN group specification or a window order clause)
RANDOMROW cannot be specified in a value expression in RANDOMROW.

However, RANDOMROW can be specified in a subquery specified in RANDOMROW.

Example of an SQL statement that generates an error:
```
RANDOMROW(1,0,RANDOMROW(1))
```
Example of a correct SQL statement:
```
RANDOMROW(1,0,(SELECT RANDOMROW(1) FROM "T1"))
```
If you specify multiple RANDOMROW functions with the same identification number in one query specification, comply with either of the following rules:
- Specify the minimum value and maximum value in only one RANDOMROW function, and omit them in all other RANDOMROW functions.
  
  Example of a correct SQL statement:
```
SELECT "C1"+RANDOMROW(1,10,20),"C2"+RANDOMROW(1),"C3"+RANDOMROW(1) FROM "T1"
```
  Example of an SQL statement that generates an error:
```
SELECT "C1"+RANDOMROW(1,10,20),"C2"+RANDOMROW(1,10,20),"C3"+RANDOMROW(1) FROM "T1"
```
- Omit the minimum value and maximum value in all RANDOMROW functions.
  
  Example of a correct SQL statement:
```
SELECT "C1"+RANDOMROW(1),"C2"+RANDOMROW(1),"C3"+RANDOMROW(1) FROM "T1"
```
RANDOMROW generates a pseudorandom number for each result row of the query specification. Therefore, the result changes for each result row of the query specification.
All RANDOMROW functions for which the same identification number is specified return the same values for each result row of the query specification.

Example:
```
SELECT
  "C1"+ RANDOMROW(1,10,20),    ...[a]
  "C2"+ RANDOMROW(1),          ...[a]
  "C3"+ RANDOMROW(2,20,30),    ...[b]
  "C4"+ RANDOMROW(2)           ...[b]
FROM "T1"
UNION ALL
SELECT
  "C1"+ RANDOMROW(1,10,20),    ...[c]
  "C2"+ RANDOMROW(1),          ...[c]
  "C3"+ RANDOMROW(2),          ...[d]
  "C4"+ RANDOMROW(2)           ...[d]
FROM "T2"
```
Explanation:
1. These are RANDOMROW functions for which 1 is specified as the identification number. Each function returns the same value (a value greater than or equal to 10 and less than 20) for each result row of the query specification.
2. These are RANDOMROW functions for which 2 is specified as the identification number. Each function returns the same value (a value greater than or equal to 20 and less than 30) for each result row of the query specification.
3. These are RANDOMROW functions for which 1 is specified as the identification number. Each function returns the same value (a value greater than or equal to 10 and less than 20) for each result row of the query specification.
4. These are RANDOMROW functions for which 2 is specified as the identification number. Each function returns the same value (a value greater than or equal to 0 and less than 1) for each result row of the query specification.
When the internal derived table shown later is expanded, HADB automatically re-assigns the identification number of the RANDOMROW in the derived query for the internal derived table. This prevents that identification number from coinciding with the identification number of the RANDOMROW in the query specification in which the internal derived table is specified.
- Internal derived table for which RANDOMROW is specified in the derived query
Example:

SQL statement in which a derived table is specified:
```
SELECT "DC1"+RANDOMROW(1,10,20),"DC2"+RANDOMROW(1)
    FROM (SELECT "C1"+RANDOMROW(1,20,30),"C2"+RANDOMROW(1)
              FROM "T1") "DT"("DC1","DC2")
```
SQL statement in which a derived table is expanded:
```
SELECT "C1"+RANDOMROW(2,20,30)+RANDOMROW(1,10,20),
       "C2"+RANDOMROW(2)+RANDOMROW(1)
    FROM "T1"
```
The identification numbers of the RANDOMROW functions in the derived table DT are re-assigned as follows:
- RANDOMROW(1,20,30) → RANDOMROW(2,20,30)
- RANDOMROW(1) → RANDOMROW(2)
Multiple RANDOMROW functions with the same identification number can be specified in different query specifications. Note, however, that the identification number of each RANDOMROW function is treated as a different entity.
The maximum number of entities of identification numbers that can be specified in one SQL statement is 1,000. However, if a viewed table is specified in an SQL statement, equivalent exchange is performed to convert the viewed table into a derived table, and then the number of entities of identification numbers is checked.
The same identification number can be specified for RANDOMCURSOR and RANDOMROW. In this case, each scalar function separately generates and returns a pseudorandom number.

Example:
```
SELECT RANDOMCURSOR(1,10,20) AS "C1",
       RANDOMROW(1,10,20) AS "C2"
    FROM "T1"
```
Example of execution results
The data type of the execution result is the DOUBLE PRECISION type.
The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).
If minimum-value or maximum-value is a null value, the execution result will also be a null value.
Both minimum-value and maximum-value are converted to the DOUBLE PRECISION type, and then the execution result is calculated.
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.
If you specify the same value for minimum-value and maximum-value, the execution result is the value specified for minimum-value.
If the execution result cannot be expressed as the data type specified for the execution result, an overflow error occurs.
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

Modify the admission date and discharge date so that the following conditions are met:

The admission and discharge dates are modified so that the hospitalization period does not change. The same number of days is added to both the current admission and discharge dates to produce the new admission and discharge dates.
A maximum of 6 days is added equally to both the current admission and discharge dates to produce new admission and discharge dates. The number of days to be added differs for each patient.
The retrieval results are sorted by the new admission date.

Example:

SELECT "PATIENT-ID","ADMISSION-DATE","DISCHARGE-DATE",
       "ADMISSION-DATE"+CAST(RANDOMROW(1,0,7) AS INTEGER) DAY AS "NEW-ADMISSION-DATE",
       "DISCHARGE-DATE"+CAST(RANDOMROW(1) AS INTEGER) DAY AS "NEW-DISCHARGE-DATE"
    FROM "HOSPITALITY-HISTORY"
      ORDER BY "ADMISSION-DATE"+CAST(RANDOMROW(1) AS INTEGER) DAY

[Figure]

To Page Top