1

I have two tables, one is Clients where i have primary key: ClientID (integer type). The other table is Orders where I have foreign key: idClient. I want to generate random data into Orders but also idClient should be ClientID numbers from Clients.

Here's what I wrote:

INSERT INTO Orders
SELECT level AS OrderID,
   (SELECT * FROM (SELECT ClientID FROM Clients ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM = 1) AS idClient,
   DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(2,50))) AS ProductName,
   TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)) AS Date,
   TRUNC(DBMS_RANDOM.value(15,2000)) AS Price
FROM   dual
CONNECT BY level <= 200;
COMMIT;

The problem is that when I look at generated records in Orders, I have the same number in idClient in every row for example 24 in each row. I want there various numbers selected from ClientID.

How to fix it??

Thank you in advance.

Improve this question
0

1 Answer 1

Reset to default
2

You could CROSS JOIN the client table as often as you need to get enough sample rows, SELECT (valid) ids from this query, and then add all other random columns in an outer query. Example (see also dbfiddle):

Tables

create table clients ( id, name )
as
select 101, 'client_101' from dual union all
select 202, 'client_202' from dual union all
select 303, 'client_303' from dual union all
select 404, 'client_404' from dual union all
select 505, 'client_505' from dual union all
select 606, 'client_606' from dual union all
select 707, 'client_707' from dual union all
select 808, 'client_808' from dual union all
select 909, 'client_909' from dual ;

alter table clients add constraint pkey_clients primary key ( id ) ;

create table orders (
  id number primary key
, idclient number references clients ( id )
, productname varchar2( 256 )
, orderdate date
, price number( 12, 2 )
);

INSERT (from SELECT)

insert into orders ( id, idclient, productname, orderdate, price )
select 
  rownum
, idclient
, dbms_random.string( 'L', trunc( dbms_random.value( 2, 50 ) ) ) as productname
, trunc( sysdate + dbms_random.value( 0, 366 ) ) as orderdate
, trunc( dbms_random.value( 15, 2000 ) ) as price 
from 
(
  select 
    C1.id  idclient
  from clients C1, clients C2, clients C3  -- cross join -> 9*9*9 rows
  order by dbms_random.value 
) where rownum <= 200 ;                    -- stop inserting at row 200

200 rows inserted.

Table ORDERS (query)

SQL> select * 
  2  from orders
  3  order by id ; 
ID  IDCLIENT  PRODUCTNAME                                        ORDERDATE  PRICE  
1   404       kiowzgecnajoyxdioxz                                10-APR-19  295    
2   707       tnzdcsvpjlpoujpdndyqxp                             04-OCT-19  1552   
3   404       sqiwohwxkmqpuwoocoeyoaqmnoxbcfogqmyhcmhwc          25-DEC-18  809    
4   808       msbgnuwdzl                                         09-FEB-19  526    
5   303       fyqmldaypwx                                        23-FEB-19  1669   
6   101       etodatstbjtfcqwnvswyp                              26-SEP-19  869    
7   101       auhcvnmlgjzetdbhozdtdirwqmvnhghfnvfvhfehstkv       31-JUL-19  1847   
8   303       krartgctrgcpcuuquffaajgphsnoxgmhimmgnjbwu          24-SEP-19  1628   
9   404       bzvcyylvdelskogqmpbztowqewnodazcjbrgqykbkxzsbs     13-SEP-19  71     
10  909       cwnmrogvjqcykxmfdybnqrxnaruhtqcyjcqrnifkiipjhnq    04-NOV-18  1837   
11  909       ojpyldoiukfiusssqyfrqlgqwyfnoyljqioobx             20-MAR-19  861    
12  808       tqzdab                                             13-JAN-19  597
...
...
...

UPDATE

If there are several "source tables" you want to get the IDs from (as stated in your comment), just use all relevant table names in the cross join. Example:

-- 3 parent tables ("sources" of ids)
create table src1 ( id number primary key ) ;

insert into src1( id ) 
select level + 3000 as id
from dual connect by level <= 5 ;

create table src2 ( id number primary key ) ;

insert into src2( id ) 
select level + 4000 as id
from dual connect by level <= 5 ;

create table src3 ( id number primary key ) ;

insert into src3( id ) 
select level + 5000 as id
from dual connect by level <= 5 ;

-- 1 child table with 3 foreign key constraints
create table dest (
  id number generated always as identity start with 1000 primary key
, src1id number references src1( id )
, src2id number references src2( id )
, src3id number references src3( id )
, shipmentstatus number not null
  check( shipmentstatus in ( 0, 1 ) )
);

Query / insert

insert into dest( src1id, src2id, src3id, shipmentstatus )
select src1id, src2id, src3id, mod( src1id, 2 ) shipmentstatus
-- generate more data as required
-- , dbms_random.string( 'L', trunc( dbms_random.value( 2, 30 ) ) ) as productname
-- , trunc( sysdate + dbms_random.value( 0, 366 ) ) as orderdate
-- , trunc( dbms_random.value( 15, 2000 ) ) as price  
from ( 
  select 
    A.id src1id 
  , B.id src2id 
  , C.id src3id         
  from src1 A, src2 B, src3 C
  order by dbms_random.value
)
where rownum <= 200
;

Result

select * from dest fetch first 12 rows only ;

ID    SRC1ID  SRC2ID  SRC3ID  SHIPMENTSTATUS  
1000  3004    4005    5002    0               
1001  3001    4004    5001    1               
1002  3004    4001    5002    0               
1003  3001    4003    5003    1               
1004  3004    4005    5005    0               
1005  3003    4001    5003    1               
1006  3003    4003    5001    1               
1007  3001    4002    5003    1               
1008  3004    4001    5004    0               
1009  3005    4002    5003    1               
1010  3004    4005    5003    0               
1011  3002    4002    5001    0

If the cross join ( ... from src1, src2, src3 ... ) does not give you enough rows, just run the query again.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thank youcso much! And if I want to add more foreign keys for example: idproduct and idshipment, how should I put it there? I added these ids at the begging just like idclient and then: from ( select C1.id idclient from clients C1, clients C2, clients C3 order by dbms_random.value , select P1.id idproduct from products P1, products P2, products P3 order by dbms_random.value , select S1.id idshipment from shipments S1, shipments S2, shipments S3 order by dbms_random.value ) where rownum <= 200 ; But sql says the construction is bad :(
@Anna - you are welcome! You have written too many cross joins into your code (in your comment), which - if you'd gotten it to work, would probably have given you a massive amount of rows. Simplify it. - See the example in the "UPDATE" above.
It looks so easy to understand now and I was complicating it :D I have one last question. I have argument shipmentStatus - smallint type and I want to put there 0 if shipment was not sent, and 1 if it was sent. But when I do something like that: TRUNC( DBMS_RANDOM.value( 0,1 ) ),sql says that the value can't be null, so I'm assuming it's 0's fault. Is there any way to fix it?
@Anna - NULL does not mean 0 (zero). If you want to have random 0s and 1s in your test table, you could use MOD(). I have added a SHIPMENTSTATUS column to the example. ( I'd not be using TRUNC( DBMS_RANDOM.VALUE() ) for getting 0s and 1s )
Thanks so much again ! :)
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.