Oracle using MOD command to set Y/N on INSERT

I’m trying to generate some sample data for the following table see below.

I have functions that can generate a random DATE and timestamp within a date range, which is working fine.

I want to use the MOD command to populate the active field to ‘Y’ OR ‘N’. I want to set every row to active=’Y’ except for every 5th row I want active=’N’ but I’m struggling to get the syntax correct and would be grateful for some help.

Below is my test CASE so far. Thanks in advance to all who answer and your expertise.

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

CREATE OR REPLACE FUNCTION random_date(
     p_from IN DATE,
     p_to   IN DATE
   ) RETURN DATE
   IS
   BEGIN
     RETURN p_from + DBMS_RANDOM.VALUE() * (p_to - p_from + 1);
   END random_date;
   /

CREATE OR REPLACE FUNCTION random_timestamp(
     p_from IN TIMESTAMP,
     p_to   IN TIMESTAMP
   ) RETURN TIMESTAMP
   IS
   BEGIN
     RETURN p_from + DBMS_RANDOM.VALUE() * (p_to - p_from + interval '1' day);
   END random_timestamp;
   /

CREATE TABLE t1 (
    seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
      dt   DATE,
      ts TIMESTAMP,
      active VARCHAR2(2)
   );

/*
Want every row active=Y
Except every 5th row=N
*/

INSERT INTO t1 (dt, ts, active)
           SELECT         random_date(DATE '2022-05-01', DATE '2022-05-31'),
               random_timestamp(DATE '2022-05-01', DATE '2022-05-31')
           FROM
               dual CONNECT BY level <= 1000;

Answer

You can use a CASE expression:

INSERT INTO t1 (dt, ts, active)
SELECT random_date(DATE '2022-05-01', DATE '2022-05-31'),
       random_timestamp(DATE '2022-05-01', DATE '2022-05-31'),
       CASE MOD(LEVEL, 5)
       WHEN 0
       THEN 'N'
       ELSE 'Y'
       END
FROM   dual
CONNECT BY level <= 1000;

db<>fiddle here

Advertisement

Answer