I have this table X with only one column, A. How can I generate a random number in each row, within specific values? Let's say I want random numbers to be either 1, 2 or 999 (3 numbers), I would have a column B looking something like this:
A | B
-----
12| 1
33| 999
67| 1
20| 2
...
I've tried the dbms_random package but only to generate between 2 numbers, like this:
select X.*, round(dbms_random.value(1,2)) from X;
Any suggestions?
Here is one method using case:
select x.a,
(case when rand < 2 then 1 when rand < 3 then 2 else 999 end) as b
from (select x.*,
dbms_random.value(1, 4) as rand
from x
) x;
You could store the values you want (1, 2, 999, bla, bla, bla) in a table, and join into it in a random order, like:
create table x (a int);
insert into x values (12);
insert into x values (33);
insert into x values (67);
insert into x values (20);
create table y (z int);
insert into y values (1);
insert into y values (2);
insert into y values (999);
create table new_x as
select a,
z
from (select a,
z,
row_number() over(partition by a order by dbms_random.value) as rn
from x
cross join y)
where rn = 1;
drop table x;
alter table new_x rename to x;
Oracle 11g R2 Schema Setup:
CREATE TABLE X ( A ) AS
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 20;
Query 1:
SELECT A,
CASE FLOOR(DBMS_RANDOM.VALUE(1,4))
WHEN 1 THEN 1
WHEN 2 THEN 2
ELSE 999 END AS B
FROM X
| A | B |
|----|-----|
| 1 | 1 |
| 2 | 2 |
| 3 | 999 |
| 4 | 999 |
| 5 | 2 |
| 6 | 1 |
| 7 | 999 |
| 8 | 999 |
| 9 | 999 |
| 10 | 999 |
| 11 | 1 |
| 12 | 1 |
| 13 | 999 |
| 14 | 2 |
| 15 | 1 |
| 16 | 2 |
| 17 | 2 |
| 18 | 999 |
| 19 | 1 |
| 20 | 999 |
