If you were only interested to run this report in a batch job, you could use a two pass query to go around this silly restriction (lookup in the select only supported from an unload).
// EXEC C2RC
//CKR2PASS DD DISP=(,PASS),DSN=&GRPINFO,SPACE=(TRK,(10,10))
//SYSIN DD *
newlist type=racf nopage dd=ckr2pass
select class=group segment=base
sortlist key(8) supgroup
// EXEC C2RC
//GRPINFO DD DISP=(OLD,PASS),DSN=&GRPINFO
//SYSIN DD *
deftype type=@grpinfo
alloc type=@grpinfo dd=grpinfo
define type=@grpinfo group as substr(record,1,8)
define type=@grpinfo supgroup as substr(record,10,8)
newlist type=racf
select class=user congrpnm:@grpinfo.group.supgroup=xxxxxx
sortlist key(8,"User ID") name ljdate :tlproc :opident connects
//
------------------------------
Rob van Hoboken
------------------------------
Original Message:
Sent: Thu April 28, 2022 04:48 AM
From: Rob van Hoboken
Subject: User report with data from multiple segments
The RACF database holds these fields in separate records, called segments. The segments for a single ID can be in different places in the database; since CARLa can process the database in (physical) sequential order, there is no guarantee the BASE segment is processed before the TSO or CICS segment. Also, since CARLa wants to select profiles during the reading phase, and it only reads each block/record once, some trickery is needed to display fields from different segments when a select is needed on any field other than the PROFILE/KEY field. One such trick is using the SUMMARY command. See this.
Newer releases of CARLa made these workarounds redundant through implicit lookup to find fields belonging to the same profile, but stored in different segments:
newlist type=racf
select class=user dfltgrp=xxxx
sortlist key(8,"User ID") name ljdate :tlproc :opident connects
The select statement implicitly only selects the base segment due to DFLTGRP being in the base segment.
There is a niggle with all lookups, this doesn't work well with repeated (multi-value) fields....
You could use the DEFINE CONNECTS SUBSELECT CONNECTS(condition) command to reduce the number of connect group names listed in the output.
But now comes a challenge, you want to have a list of users that are connected to a group BELOW xxxx. That is something the RACF newlist type does not support for the active RACF db, but only for an unloaded database as input. I guess what would work is
newlist type=racf
select class=user congrpnm:supgroup=xxxx
sortlist key(8,"User ID") name ljdate :tlproc :opident connects
------------------------------
Rob van Hoboken
------------------------------