I have some code, that is generating a random number of rows for an employee_id. For each row I am using a subquery to assign a random location_id.
The result is that I'm getting the same location_id for each employee_id whereas I want a random one from my locations table.
Since the subquery is designed to retrieve only 1 row at a time I believe I'm running into a subquery caching issue.
I have tried to running the subquery inside a CTE and outside a CTE with a predicate that I thought would fix this issue but was unsuccessful. Therefore, I think the issue is with the predicate in the subquery.
Can someone take a look and tell me how to rectify this issue so I can get a random location_id from the locations table for every row.
I am running version 19 of the database and I also tried the code on livesql and seem to see the same behavior in both places.
Thanks in advance to all who answer.
ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';
CREATE TABLE employees AS
SELECT level AS employee_id,
'Employee ' || level AS emp_name
FROM dual
CONNECT BY level <= 5;
CREATE TABLE locations AS
SELECT level AS location_id,
'Location ' || level AS location_name
FROM dual
CONNECT BY level <= 10;
ALTER TABLE locations
ADD ( CONSTRAINT locations_pk
PRIMARY KEY (location_id));
with rws as (
select level rn
from dual
connect by level <= 5
),
data as (
select e.*,
round (dbms_random.value(1,5)) as n
from employees e
)
select d.employee_id,
d.emp_name,
(
select location_id
from locations
where d.employee_id*0 = location_id*0
order by dbms_random.value()
fetch first 1 row only
) as location_id,
trunc (sysdate) + dbms_random.value (0, 5) AS random_date
from rws r
join data d on r.rn <= d.n
order by d.employee_id;
EMPLOYEE_ID EMP_NAME LOCATION_ID RANDOM_DATE
1 Employee 1 4 07162020 20:46:16
1 Employee 1 4 07152020 14:13:59
1 Employee 1 4 07162020 15:51:46
2 Employee 2 3 07152020 23:39:54
2 Employee 2 3 07152020 12:49:40
2 Employee 2 3 07172020 13:46:59
3 Employee 3 5 07172020 09:25:54
3 Employee 3 5 07162020 10:32:59