8.4.6 RANDOMCURSOR

(1) Specification format

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

To Page Top

(2) Explanation of specification format

identification-number:

Specifies an integer in the range from 1 to 1000. If an SQL statement contains multiple RANDOMCURSOR functions for which the same identification number is specified, those functions always return the same values.

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 specification. For details about value specifications, see 7.21 Value specification.

• 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 a 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 specification. For details about value specifications, see 7.21 Value specification.

• 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 a dynamic parameter for maximum-value, the DOUBLE PRECISION type is assumed.

To Page Top

(3) Rules

1. 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)

2. If you specify multiple instances of RANDOMCURSOR with the same identification number in one SQL statement, comply with either of the following rules:

   • Specify the minimum value and maximum value in only one instance of RANDOMCURSOR, and omit them in all other instances of RANDOMCURSOR.

     Example of a correct SQL statement:

     SELECT "C1"+RANDOMCURSOR(1,10,20),"C2"+RANDOMCURSOR(1) FROM "T1"
     UNION ALL
     SELECT "C3"+RANDOMCURSOR(1),"C4"+RANDOMCURSOR(1) FROM "T2"

     Example of an SQL statement that generates an error:

     SELECT "C1"+RANDOMCURSOR(1,10,20),"C2"+RANDOMCURSOR(1) FROM "T1"
     UNION ALL
     SELECT "C3"+RANDOMCURSOR(1,10,20),"C4"+RANDOMCURSOR(1) FROM "T2"

   • Omit the minimum value and maximum value in all instances of RANDOMCURSOR.

     Example of a correct SQL statement:

     SELECT "C1"+RANDOMCURSOR(1),"C2"+RANDOMCURSOR(1) FROM "T1"
     UNION ALL
     SELECT "C3"+RANDOMCURSOR(1),"C4"+RANDOMCURSOR(1) FROM "T2"

3. If an SQL statement contains multiple RANDOMCURSOR functions for which the same identification number is specified, those functions always return the same values.

   Example:

   SELECT
     "C1"+ RANDOMCURSOR(1,10,20),    ...[a]
     "C2"+ RANDOMCURSOR(1),          ...[a]
     "C3"+ RANDOMCURSOR(2),          ...[b]
     "C4"+ RANDOMCURSOR(2)           ...[b]
   FROM "T1"
   UNION ALL
   SELECT
     "C1"+ RANDOMCURSOR(1),          ...[a]
     "C2"+ RANDOMCURSOR(1),          ...[a]
     "C3"+ RANDOMCURSOR(2,20,30),    ...[b]
     "C4"+ RANDOMCURSOR(2)           ...[b]
   FROM "T2"
   

   Explanation:

   1. These are instances of RANDOMCURSOR for which 1 is specified as the identification number. Each instance always returns the same value (a value greater than or equal to 10 and less than 20).

   2. These are instances of RANDOMCURSOR for which 2 is specified as the identification number. Each instance always returns the same value (a value greater than or equal to 20 and less than 30).

4. If the following identification numbers are the same, HADB automatically re-assigns them. Therefore, the SQL statement does not result in an error.

   • Identification number specified when a viewed table is defined (by using the CREATE VIEW statement)

   • Identification number specified in an SQL statement in which the viewed table is specified

   Example:

   Definition of viewed table V1:

   CREATE VIEW "V1"("VC1","VC2") AS 
     SELECT "C1"+RANDOMCURSOR(1,10,20),"C2"+RANDOMCURSOR(1) FROM "T1"

   SQL statement for searching viewed table V1:

   SELECT "VC1"+RANDOMCURSOR(1,10,20),"VC2"+RANDOMCURSOR(1) FROM "V1"

   For the preceding SELECT statements, HADB performs equivalent exchange as follows:

   SELECT "VC1"+RANDOMCURSOR(1,10,20),"VC2"+RANDOMCURSOR(1)
     FROM (SELECT "C1"+RANDOMCURSOR(2,10,20),"C2"+RANDOMCURSOR(2)
               FROM "T1") "V1"("VC1","VC2")

   The underlined identification number, 2, is the one that HADB automatically changed from 1. Therefore, this SELECT statement does not result in an error. As shown in the preceding example, HADB automatically changes the identification number that was specified when the viewed table was defined.

5. In one SQL statement, a maximum of 1,000 entities of RANDOMCURSOR identification numbers can be specified. If a viewed table is specified in an SQL statement, the total number of entities of RANDOMCURSOR identification numbers appearing in the SQL statement and the relevant CREATE VIEW statement must not exceed 1,000.

6. 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

   

7. RANDOMCURSOR generates a pseudorandom number when the cursor opens. Therefore, the result changes each time the cursor opens.

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

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

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

11. Both minimum-value and maximum-value are converted to the DOUBLE PRECISION type, and then the execution result is calculated.

12. 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.

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

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

15. 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 retrieval results are sorted by the new admission date.

Example:

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

Example of execution results

To Page Top