PeopleSoft Wiki

When testing, a common requirement is to select from a list of random Users (OPRID) or IDs (EMPLID). The following SQL provides a way to do this using:

• PSOPRDEFN for a list of users (OPRIDs)
• PS_PERSON for a list of people (EMPLIDs)

Note that this SQL is database specific.

Oracle

Select 100 random users (OPRIDs):

select  OPRID
from    
(
    select      OPRID
    from        PSOPRDEFN
    order by    dbms_random.value
) 
where rownum <= 100;

Select 100 random people (EMPLIDs):

select  EMPLID
from    
(
    select      EMPLID
    from        PS_PERSON
    order by    dbms_random.value
) 
where rownum <= 100;

Change the rownum <= 100 line to adjust the number of rows returned.

SQL Server

Select 100 random users (OPRIDs):

select top 100 OPRID
from PSOPRDEFN
order by NEWID();

Select 100 random people (EMPLIDs):

select top 100 EMPLID
from PS_PERSON
order by NEWID();

As usual, the Oracle syntax is far more cumbersome!

See this article on ideas on how to change this SQL for other database platforms such as DB2.