Select Random Users


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 platform 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();

Revision #1
Created Sun, Jul 28, 2019 7:06 PM by PeopleSoft Wiki
Updated Sun, Jul 28, 2019 7:09 PM by PeopleSoft Wiki