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;
CodePudding user response:
Use decode()
(or case
), like this:
decode(mod(rownum,5),0,'N','Y') as active
CodePudding user response:
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