PeopleSoft Wiki

Hi Gilbert,

Thanks for your question, I've actually created an article that shows the SQL required to do this in either SQL Server or Oracle. This is available in the following article:

Select Random Users

What you'll need to do in PS Query is create a sub-query that produces the SQL shown above for your database platform. If you are on SQL server this will be fairly straightforward.

If you are on Oracle, this will be a lot harder. I tried messing around with it for a bit but the catch is making it order by dbms_random.value in a sub-query, which isn't possible as you can only have one field in a sub-query and you can only order by that field.

The way to make this work in Oracle is to create a view with the logic that generates the random EMPLIDs. The SQL in this view will be:

SELECT EMPLID 
  FROM ( 
 SELECT EMPLID 
  FROM PS_PERSON 
  ORDER BY dbms_random.value )

And you will need to save it as a record and add it to the query tree so you can see it through query manager. Join to this view in your existing query, and then add one more expression: rownum and add that expression to your criteria equal to a constant (which will = 450).

Good luck with this and please post any more questions.

Great tip, thanks.