Oracle PL/SQL for DBAs

Generating Random Values with NULLs

Remember from the discussion earlier that the requirement for the column FOLIO_ID is a little different. It needs only 50% of the values populated; the rest should be NULL. How can I achieve that result?

I will use the same probability approach with a twist; I will simply use a determination of “yes” or “no.” Generating a random number between 1 and 100 will ensure 1% probability of each number. Hence, a number below 51 will have exactly 50% probability. I can use this in a CASE statement to get the value:

    SQL> SELECT CASE
      2            WHEN DBMS_RANDOM.VALUE (1, 100) < 51
      3               THEN NULL
      4            ELSE FLOOR (DBMS_RANDOM.VALUE (1, 100))
      5         END
      6* FROM DUAL;

On line 2, I check if the number generated is less than 51. If so, I return NULL. Because the probability of a number under 51 is 50%, I have NULLs occurring 50% of the time, as well. In the other 50%, I have generated a random number between 1 and 100 to be used as a FOLIO_ID.