Hello Pete,
I'm glad you were available to help! After tweaking your query for SQLPlus and adding some conditions, your solution worked fine. Thanks.
Now I'd like to ask you for assistance with another SQLPlus Query......
This SQLPlus query pulls the Schedule and Calendar/Keyword conditions for all Objects:
select jpp_object as OBJECT
, oh_name as SCHEDULE
, JPPC_CaleName as CalendarName
, JPPC_CaleKeyName as CalendarKey
from UC4.oh
inner join UC4.jpp
on oh_idnr = jpp_oh_idnr
left outer join UC4.jppc
on jppc_jpp_lnr = jpp_lnr and jppc_oh_idnr = oh_idnr
where oh_otype = 'JSCH'
and oh_name NOT LIKE '%old%'
and oh_name NOT LIKE '%OLD%'
order by 1,3,2;
So, how would I modify it to pull only those objects that have NEW or MODIFIED Calendar/Keyword conditions?
------------------------------
Gerry Jandeska
Lead Scheduling Systems Engineer
AEA. Core Technology Services
CME Group
20 South Wacker Drive
Chicago, Illinois 60606
------------------------------
Original Message:
Sent: 04-24-2020 03:36 PM
From: Gerry Jandeska
Subject: Database query
Hello Pete.
I have a requirement to produce a SQL report showing a distinct listing of Job Plans, and a column for Schedules. If the Job Plan exists in a Schedule then the Schedule Name should appear in the Schedules column. If the Job Plan does NOT exist in a Schedule, then a blank or null should appear in the Schedules column.
Bases on your info, I started with the following query. It works fine, but it lists ONLY the JobPlans that exist in Schedules. How would I expand the query to also list the Job Plans that do NOT exist in a Schedule?
Select jpp_object as " OBJECT",
oh_name as " SCHEDULE "
from UC4.oh, UC4.jpp
where oh_idnr = jpp_oh_idnr
and oh_otype = 'JSCH'
order by 1,2;
Thanks,
Gerry
------------------------------
Gerry Jandeska
Lead Scheduling Systems Engineer
AEA. Core Technology Services
CME Group
20 South Wacker Drive
Chicago, Illinois 60606
Original Message:
Sent: 10-15-2015 02:29 PM
From: Pete Wirfs
Subject: Database query
This looked pretty useful to me, so I added another column to also display the associated calendar keyword if one exists;
select oh_name as SCHEDULE
, jpp_object as OBJECT
, JPP_ErlstStTime as STARTTIME
, jppc_calekeyname as CALENDAR
from uc4.dbo.oh
inner join uc4.dbo.jpp
on oh_idnr = jpp_oh_idnr
left outer join uc4.dbo.jppc
on jppc_jpp_lnr = jpp_lnr and jppc_oh_idnr = oh_idnr
where oh_otype = 'JSCH'
order by 1,2,3,4;