We have a requirement to develop a SQL query that will select Objects executed over a certain time period, display their statuses, and display the Users who executed those Objects.
The Query below works fine, except for one point. The User field represents the User who manually executed the Object, but it also lists Objects that were automatically executed by the Schedule. in the latter case, the User is the last person who saved a change to the Schedule Object. So, this is not very helpful.
How could the query be modified to differentiate between Users who manually executed the Object, as apposed to those Objects that were automatically executed by a Schedule?
Thanks,
Gerry
-------------------------------------------------------
SELECT UNIQUE ah_name as "Object Name", ah_RetCode as "Return Code",
CAST((FROM_TZ(CAST(ah_timestamp2 AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Central') AS DATE) as "Start Date Time",
CAST((FROM_TZ(CAST(ah_timestamp4 AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Central') AS DATE) as "End Date Time",
usr_FirstName as "First Name", usr_LastName as "Last Name"
FROM uc4.ah, uc4.oh, uc4.usr
WHERE USR_OH_Idnr = AH_USR_Idnr
AND AH_OH_Idnr = OH_Idnr
AND ah_timestamp2 < SYSDATE -1
AND ah_timestamp2 > SYSDATE -2
AND ah_oh_idnr IN (SELECT oh_idnr
FROM uc4.oh
WHERE oh_otype in ('JOBP','JOBS','EVNT','SCRI','JOBF'))
ORDER BY ah_name ;
-------------------------------------------------------