PeopleSoft Wiki

The following SQL is a modification of this example of looping through data and presenting it as a comma separated list in one row.

It gives a comma separated list of roles for each user. Note that this will only work on Oracle databases as far as I'm aware.

select
   ROLEUSER as USER_ID,
   substr(SYS_CONNECT_BY_PATH(ROLENAME, ','),2) as ROLES
from
   (
   select
     ROLEUSER,
     ROLENAME,
     count(*) OVER ( partition by ROLEUSER ) as ROLECOUNT,
     ROW_NUMBER () OVER ( partition by ROLEUSER order by ROLENAME) ROLESEQUENCE
   from
     PSROLEUSER
   where
     ROLEUSER is not null)
where
   ROLECOUNT=ROLESEQUENCE
start with
   ROLESEQUENCE = 1
connect by prior
   ROLESEQUENCE+1=ROLESEQUENCE
and prior
   ROLEUSER=ROLEUSER
;

How does it work? Well here's a break down.

The query inside the FROM clause essentially groups the data for looping.

select
   ROLEUSER,
   ROLENAME,
   count(*) OVER ( partition by ROLEUSER ) as ROLECOUNT,
   ROW_NUMBER () OVER ( partition by ROLEUSER order by ROLENAME) ROLESEQUENCE
from
   PSROLEUSER
where
   ROLEUSER is not null

It returns the user (ROLEUSER), the name of the role ((ROLENAME), a count of the number of roles for that user ((ROLECOUNT) and a sequence number for each role row (ROLESEQUENCE).

So for example if the user PS has the roles:

• PeopleSoft Administrator
• PeopleTools
• Portal Administrator
• ProcessSchedulerAdmin
• Security Administrator

The result would look like this:

This essentially sets up the loop - PS has 5 roles and each of them has been placed in sequence using the Oracle system ROW_NUMBER partitioned over the role user and ordered by the role name.

The outside part of the query then uses the SYS_CONNECT_BY_PATH starting with the ROLECOUNT equal to the ROLESEQUENCE which starts at 1 and is incremented by 1 each time. To ensure that we are looping for the same user each time, the SYS_CONNECT_BY_PATH uses the line prior ROLEUSER=ROLEUSER. So when we move to the next user, the loop starts again with a ROLESEQUENCE of 1.

You don't have to fully understand the syntax, but what this SQL demonstrates is how to create a simple loop in Oracle using plain old SQL.