How to replace a "User ID" with the name of actual USR object in Oracle SQL?

[Eric Felker]

Hi Folks-

We're running AWA 12.0.4 on Oracle 12c. I am trying to build a query that shows the run counts of job objects that do NOT have the "Generate at Runtime" checkbox selected in the Attribute tab. I actually have that working, but now want to go further and display the USR object name for the object creator and last modifier. These 2 fields are available right in the OH table (OH_CrUserIdnr and OH_ModUserIdnr), but are provided as IDNRs. I cannot figure out how to map these IDs to instead have the actual user object name (also from OH) displayed.

I think I need to join OH to itself, but I can't wrap my head around how this would work

Here is my working query, before trying to add in the User object name for the creator and last modifier.

select o.OH_name ObjectName, o.OH_client Client, o.OH_otype OType, count(a.AH_idnr) RunCount
from JBA, OH o, AH a
where o.OH_idnr = a.AH_oh_idnr
and jba_oh_idnr = oh_idnr
and jba_actatrun = 0
and oh_deleteflag = 0
and oh_otype in('JOBP','JOBS','SCRI')
group by o.oh_name, o.oh_client, o.oh_otype
order by count(a.ah_idnr) desc

Thanks for looking!
Eric

[Pete Wirfs]

select o.OH_name ObjectName, o.OH_client Client, o.OH_otype OType, count(a.AH_idnr) RunCount
     , (select oh_name from oh b where b.oh_idnr = o.OH_CrUserIdnr) as CrUser
	 , (select oh_name from oh b where b.oh_idnr = o.OH_ModUserIdnr) as ModUser
from JBA, OH o, AH a
where o.OH_idnr = a.AH_oh_idnr
and jba_oh_idnr = oh_idnr
and jba_actatrun = 0
and oh_deleteflag = 0
and oh_client = 200
and oh_otype in('JOBP','JOBS','SCRI')
group by o.oh_name, o.oh_client, o.oh_otype, oh_cruseridnr, oh_moduseridnr
order by count(a.ah_idnr) desc​

------------------------------
Pete
------------------------------

[Eric Felker]

Works perfectly! So simple, just couldn't get there on my own. THANK YOU, PETE!