0
  • Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
  • PL/SQL Release 11.2.0.1.0 - Production "CORE 11.2.0.1.0 Production"
  • TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
  • NLSRTL Version 11.2.0.1.0 - Production

I will base this question of the HR Schema for simplicity. I have two tables Employees and Locations. For this example, I have 37 locations and 173 employees.

My locations have a specific number assigned indicating the number of employees that need to be assigned to the location. What I am attempting to do is randomly assign the number of required employees for each location from the employees table.

As such an example of desired results would be:

EMPLOYEE_ID FIRST_NAME LAST_NAME STREET_ADDRESS CITY STATE 
1              A        Smith     737 Any Road Anywhere USA 
2              B         Jones     50 Some Road Nowhere USA 
3              C         Butler   737 Any Road Anywhere USA 
4              D         Thomas    50 Some Road Nowhere USA 
5              E         Marsh    737 Any Road Anywhere USA 
6              F         Ruppert   50 Some Road Nowhere USA 
7              G         Glenn     50 Some Road Nowhere USA 
  • What is your question? You may want to include table definitions and some sample data, in addition to your attempted SQL statement. – mustaccio Jul 5 '17 at 17:11
  • Question was marked as answered in your thread on same at OTN forums - community.oracle.com/thread/4060777 – EdStevens Jul 5 '17 at 21:30
0

Use the random function to generate a number. "Number" your locations with slots equal to the number of positions they need. For example, if location A needs 15 and location B 16 then request a random number from 1 to 31 and:

  • 1-15 go to A
  • 16-31 go to B

Save that result to a table, and then pull all employees assigned per site.

  • How do you use the RANDOM function to generate the numbers from 1 to 31, without duplicates and without gaps? (Hint: you can't; that would definitely not be RANDOM.) – mathguy Jul 5 '17 at 22:30
  • You store them as you generate them and then skip the collisions near the end. – CalZ Jul 6 '17 at 11:28
0

Your professor should have taught you the mod function. You can use rownumber and mod to come up with a number from 0 to n where n is one less than the number that you divide by. Hence mod(rownum, 37)+1 will give you a random number between 1 and 37. This gives you between 4 and 5 people working at every location. Sounds oddly random. Except it would not be random for a database class. This query should help you to get on track.

SELECT 'location_id_'||LPAD(mod(rownum, 10)+1, 3, '0') location_id,
       employee_id, first_name, last_name, street_address, city, state 
  FROM employee
 ORDER BY rowid;
  • Before you post answers, the expectation is that you read the problem statement carefully. The OP stated very clearly that each location has a specific number of needed employees. You seem to have completely missed that part. – mathguy Jul 5 '17 at 22:28
  • Perhaps the OP could have shown the table structure for all of the tables or at least explained how someone would know how many people belonged in each location. Perhaps the OP could have given names for each location. The OP didn't do any of that. But I provided a starting point on how to solve the problem. Perhaps you can post your own code. – Gandolf989 Jul 6 '17 at 13:56
  • Gandolf989 - Just for FYI - I don't have a professor nor a database class. This was for a work issue. At 52 I am still learning. It would probably be best not to be so presumptuous, and perhaps you will find life much more interesting and YOU may even learn something yourself.... – GMoney Aug 26 '17 at 0:43
  • While it might be a work assignment, on the face of it it looks like something that someone would do in a class. Rowid is somewhat random, therefore it would be a reasonable way to guarantee a random and even distribution. Using a random function could easily put too many people in the same branch. Certainly having a computer program tell you which office to work in regardless of how far it is from your house would make life interesting. – Gandolf989 Aug 28 '17 at 13:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.