Oracle SQL to get schedule time in F91300 (Schedule Job Master)

antoine_mpo

Reputable Poster
Hi list,

Several times i wanted to extract a list of scheduled job in JDE, especially to look at schedule times, in my time zone, but so far, i couldn't find a way to get this information by sql, because i couldn't spend too much time to do so.
Lately i really needed this information, and finally took the time to find a solution with Oracle SQL.
I want to share it with you. Maybe it could help some of you.

The information are located in F91300.
You can easily find the job name, its status, the ube name and version, the job queue.
Then there is the field SJSCHSTTIME where is stored what is called "Scheduled Start Date/time" in P91300 where you define jobs.
The Data dictionary says that this alias is "based on Universal Time Coordinate (UTC) Time. UTC Time is the number of seconds elapsed since Midnight on January 1, 1970 in Greenwich, England. UTC Time used to be called Greenwich Mean (GM) Time."

The thing is that data stored here is not the number of seconds since 1970-01-01, but the number of minutes (thanks to JDEList, because it's in a post here that i learned that).

So first, we will convert this SJSCHSTTIME to a standard date/time field.
To do so, we will use the oracle function NUMTODSINTERVAL(n,interval_unit), which allow to add n intervals to a date/time, specifying the interval unit that can be 'DAY','HOUR','MINUTE','SECOND'.
It goes like this : TO_DATE('1970-01-01', 'YYYY-MM-DD') + numtodsinterval(SJSCHSTTIME, 'MINUTE')
What you get here is the start date/time, in GMT hours. If it's what you need, you're done, easy as pie.

If you want it in your time zone, and that Daylight saving apply to your zone, there is a bit of extra work, a littre trickier.
It requires several type conversions, and the use of FROM_TZ function.
First you need to find your zone name. Use the oracle view 'v$timezone_names', with the following sql :
select distinct tzname from v$timezone_names ;
Find yours. Mine was 'Europe/Paris', for instance.

The function FROM_TZ convert a timestamp type to a timestamp_tz type which include time zone. So first of all, you need to convert your GMT date/time to a timespamp with a
'CAST(gmt_date) AS TIMESTAMP'
Then you can use FROM_TZ to convert a UTC time to your zone :
FROM_TZ(gmt_timestamp,'UTC') AT TIME ZONE 'YourZoneName'

So, if you put it all together, the full formula is (with my time zone) :
FROM_TZ(CAST((TO_DATE('1970-01-01', 'YYYY-MM-DD') + numtodsinterval(SJSCHSTTIME, 'MINUTE')) AS TIMESTAMP),'UTC') AT TIME ZONE 'Europe/Paris'

Of course now you can change the way to display this date/time, using the usual TO_CHAR and format that suit you. For instance if you only want to display
time in 24h format :
TO_CHAR(FROM_TZ(CAST((TO_DATE('1970-01-01', 'YYYY-MM-DD') + numtodsinterval(SJSCHSTTIME, 'MINUTE')) AS TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/Paris','HH24:MI:SS')

So here is an example of a sql to get a list of your scheduled jobs, in Europe/Paris time :

select trim(a.SJSCHJBNM) Job_Name,trim(a.SJSCHRPTNM) UBE_Name, trim(a.SJSCHVER) UBE_Version,
a.SJSCHSTTIME ScheduleStart_JDE, trim(SJSCHJBSTAT) Status, trim(a.SJSCHUSER) "User", trim(a.SJJOBQUE) Job_Queue,
to_char(FROM_TZ(CAST((TO_DATE('1970-01-01', 'YYYY-MM-DD') + numtodsinterval(SJSCHSTTIME, 'MINUTE')) AS TIMESTAMP), 'UTC')
AT TIME ZONE 'Europe/Paris','DD/MM/YYYY HH24:MI:SS') ScheduleStart_DateTime,to_char(FROM_TZ(CAST((TO_DATE('1970-01-01', 'YYYY-MM-DD') + numtodsinterval(SJSCHSTTIME, 'MINUTE')) AS TIMESTAMP), 'UTC')
AT TIME ZONE 'Europe/Paris','HH24:MI:SS') ScheduleStartTime_Local
from sys7333.f91300 a
where a.SJSCHJBSTAT ='01'
order by 9 ;

Hope it helps.

Bye.
 
Antoine,

Thank you for taking the time to not only share your solution, but include some explanation of the components leading up to it. Great job!
 
Hi Don,

Thanks, but you're welcome. Isn't it what's JDEList all about : sharing knowledgde and solutions ?
wink.gif

It's a pleasure if it can be any help to someone sometimes. I've found so many interesting information so many times around here myself.


Take care.
 
Hi list,

To get a list of scheduled jobs in JDE with a readable scheduling time was a first step of what i needed. Because when you have to add new jobs, it's not enough, you also need information on how often it is scheduled : is it everyday, every month, .. And furthermore, you may need to know when will occur the next run of the job (at least to know if it's a job that ended because it was meant to run until a specific date for instance).
If you took a look at f91300 table, you probably noticed that it's not that easy to get …
I took the time to try to add these information to my first sql request.

Here it is, if it can be any help (i tried to translate it in english quickly, because of course i designed it in french) :


<font class="small">Code:</font><hr /><pre>

SELECT TRIM (a.sjschjbnm) Scheduler_job_name, TRIM (a.sjschrptnm) ube_name,
TRIM (a.sjschver) version_name, TRIM (sjschjbstat) status,
TRIM (a.sjschuser) submit_user, TRIM (a.sjjobque) jobqueue,
TO_CHAR (FROM_TZ (CAST (( TO_DATE ('1970-01-01', 'YYYY-MM-DD')
+ NUMTODSINTERVAL (sjschsttime, 'MINUTE')
) AS TIMESTAMP
),
'UTC'
) AT TIME ZONE 'Europe/Paris',
'DD/MM/YYYY HH24:MI:SS'
) Schedule_StartDate,
TO_CHAR (FROM_TZ
(CAST (( TO_DATE ('1970-01-01', 'YYYY-MM-DD')
+ NUMTODSINTERVAL (sjschsttime, 'MINUTE')
) AS TIMESTAMP
),
'UTC'
) AT TIME ZONE 'Europe/Paris', 'HH24:MI:SS')
schedule_time,
TRIM (a.sjschrcrtyp) schedule_type,
DECODE (sjschrcrtyp,
'61', 'Every '
|| DECODE (sjschnummns, '1', NULL, sjschnummns)
|| ' min(s)',
'62', 'Every '
|| DECODE (sjschnumhrs, '1', NULL, sjschnumhrs)
|| ' hour(s)',
'11', 'Every '
|| DECODE (sjschnumdy, '1', NULL, sjschnumdy)
|| ' day(s)',
'12', 'Every weekday',
'21', 'Every '
|| DECODE (a.sjschnumwks, '1', NULL, sjschnumwks)
|| ' week(s), on '
|| DECODE (a.sjmonday, '1', 'Monday,', NULL)
|| DECODE (a.sjtuesday, '1', 'Tuesday,', NULL)
|| DECODE (a.sjwednesday, '1', 'Wednesday,', NULL)
|| DECODE (a.sjthursday, '1', 'Thursday,', NULL)
|| DECODE (a.sjfriday, '1', 'Friday,', NULL)
|| DECODE (a.sjsaturday, '1', 'Saturday,', NULL)
|| DECODE (a.sjsunday, '1', 'Sunday,', NULL),
'31', 'on day '
|| sjschnumdy
|| ' of the month, Every '
|| DECODE (sjschnummn, '1', NULL, sjschnummn)
|| ' month(s)',
'32', 'on '
|| DECODE (a.sjschnumwks,
'1', '1st ',
'2', '2nd ',
'3', '3rd ',
'5', 'last ',
sjschnumwks || 'th '
)
|| DECODE (a.sjschday, '1', 'day', NULL)
|| DECODE (a.sjweekday, '1', 'weekday', NULL)
|| DECODE (a.sjweekend, '1', 'Weekend', NULL)
|| DECODE (a.sjmonday, '1', 'Monday,', NULL)
|| DECODE (a.sjtuesday, '1', 'Tuesday,', NULL)
|| DECODE (a.sjwednesday, '1', 'Wednesday,', NULL)
|| DECODE (a.sjthursday, '1', 'Thursday,', NULL)
|| DECODE (a.sjfriday, '1', 'Friday,', NULL)
|| DECODE (a.sjsaturday, '1', 'Saturday,', NULL)
|| DECODE (a.sjsunday, '1', 'Sunday,', NULL)
|| ' of Every '
|| DECODE (sjschnummn, '1', NULL, sjschnummn)
|| ' month(s)',
'41', 'on day '
|| sjschnumdy
|| ' Every '
|| DECODE (sjschnummn, '1', NULL, sjschnummn)
|| ' period(s) of company '
|| sjco,
'42', 'on '
|| DECODE (a.sjschnumwks,
'1', '1st ',
'2', '2nd ',
'3', '3rd ',
'5', 'last ',
sjschnumwks || ' th '
)
|| DECODE (a.sjschday, '1', 'day', NULL)
|| DECODE (a.sjweekday, '1', 'weekday', NULL)
|| DECODE (a.sjweekend, '1', 'Weekend', NULL)
|| DECODE (a.sjmonday, '1', 'Monday,', NULL)
|| DECODE (a.sjtuesday, '1', 'Tuesday,', NULL)
|| DECODE (a.sjwednesday, '1', 'Wednesday,', NULL)
|| DECODE (a.sjthursday, '1', 'Thursday,', NULL)
|| DECODE (a.sjfriday, '1', 'Friday,', NULL)
|| DECODE (a.sjsaturday, '1', 'Saturday,', NULL)
|| DECODE (a.sjsunday, '1', 'Sunday,', NULL)
|| ' of Every '
|| DECODE (sjschnummn, '1', NULL, sjschnummn)
|| ' period(s) of company '
|| sjco,
'51', 'each '
|| sjschnumdy
|| ' of '
|| DECODE (sjschnummn,
'1', 'January',
'2', 'February',
'3', 'March',
'4', 'April',
'5', 'May',
'6', 'June',
'7', 'July',
'8', 'August',
'9', 'September',
'10', 'October',
'11', 'November',
'12', 'December'
),
'52', 'on '
|| DECODE (a.sjschnumwks,
'1', '1st ',
'2', '2nd ',
'3', '3rd ',
'5', 'last ',
sjschnumwks || ' th '
)
|| DECODE (a.sjschday, '1', 'day', NULL)
|| DECODE (a.sjweekday, '1', 'weekday', NULL)
|| DECODE (a.sjweekend, '1', 'Weekend', NULL)
|| DECODE (a.sjmonday, '1', 'Monday', NULL)
|| DECODE (a.sjtuesday, '1', 'Tuesday', NULL)
|| DECODE (a.sjwednesday, '1', 'Wednesday', NULL)
|| DECODE (a.sjthursday, '1', 'Thursday', NULL)
|| DECODE (a.sjfriday, '1', 'Friday', NULL)
|| DECODE (a.sjsaturday, '1', 'Saturday', NULL)
|| DECODE (a.sjsunday, '1', 'Sunday', NULL)
|| ' of '
|| DECODE (sjschnummn,
'1', 'January',
'2', 'February',
'3', 'March',
'4', 'April',
'5', 'May',
'6', 'June',
'7', 'July',
'8', 'August',
'9', 'September',
'10', 'October',
'11', 'November',
'12', 'December'
),
'53', 'on day '
|| sjschnumdy
|| ' of period #'
|| sjschnummn
|| ' of company '
|| sjco,
'54', 'on '
|| DECODE (a.sjschnumwks,
'1', '1st ',
'2', '2nd ',
'3', '3rd ',
'5', 'last ',
sjschnumwks || 'th '
)
|| DECODE (a.sjschday, '1', 'day', NULL)
|| DECODE (a.sjweekday, '1', 'weekday', NULL)
|| DECODE (a.sjweekend, '1', 'Weekend', NULL)
|| DECODE (a.sjmonday, '1', 'Monday', NULL)
|| DECODE (a.sjtuesday, '1', 'Tuesday', NULL)
|| DECODE (a.sjwednesday, '1', 'Wednesday', NULL)
|| DECODE (a.sjthursday, '1', 'Thursday', NULL)
|| DECODE (a.sjfriday, '1', 'Friday', NULL)
|| DECODE (a.sjsaturday, '1', 'Saturday', NULL)
|| DECODE (a.sjsunday, '1', 'Sunday', NULL)
|| ' of period #'
|| sjschnummn
|| ' of company '
|| sjco
) AS scheduling,
(SELECT NVL
(TO_CHAR (FROM_TZ
(CAST
(( TO_DATE ('1970-01-01', 'YYYY-MM-DD')
+ NUMTODSINTERVAL
(MAX (last_exec.jsschsttime),
'MINUTE'
)
) AS TIMESTAMP
),
'UTC'
) AT TIME ZONE 'Europe/Paris',
'DD/MM/YYYY HH24:MI:SS'
),
'NA'
)
FROM sys7333.f91320 last_exec
WHERE last_exec.jsschjbnm = a.sjschjbnm
AND last_exec.jsschlncstat = '20') DateTime_LastExec,
(SELECT NVL
(TO_CHAR (FROM_TZ
(CAST
(( TO_DATE ('1970-01-01', 'YYYY-MM-DD')
+ NUMTODSINTERVAL
(MIN (next_exec.jsschsttime),
'MINUTE'
)
) AS TIMESTAMP
),
'UTC'
) AT TIME ZONE 'Europe/Paris',
'DD/MM/YYYY HH24:MI:SS'
),
'NA'
)
FROM sys7333.f91320 next_exec
WHERE next_exec.jsschjbnm = a.sjschjbnm
AND next_exec.jsschlncstat = '01') DateTime_NextExec
FROM sys7333.f91300 a
WHERE a.sjschjbstat = '01'
ORDER BY sjschjbnm;
</pre><hr />

Cheers,
 
clap,clap,clap... It's great, I have a SQL but it's very basic if compared with this, but from here ahead I'll use this SQL
laugh.gif
 
Here's my crack at a SQL Server T-SQL port of this script (with some exceptions, tweaks, etc).
I'll leave it here in the hopes others stumble across this post looking for a head-start on their own script:

/********************************************/
declare @offset int
set @offset = datediff(minute, GetUTCDate(), GetDate()) / 60

select d.Scheduler_job_name, e.run_count, e.max_run_time, e.avg_run_time, e.max_wait_time, e.avg_wait_time, d.ube_name, d.version_name, d.submit_user, d.jobqueue, d.Schedule_StartDate,
d.Schedule_StartTime, d.schedule_type, d.sched_period, d.sched_desc,
d.sched_Monday, d.sched_Tuesday, d.sched_Wednesday, d.sched_Thursday, d.sched_Friday,
d.sched_Saturday, d.sched_Sunday from
(
SELECT RTRIM (a.sjschjbnm) Scheduler_job_name, RTRIM (a.sjschrptnm) ube_name,
RTRIM (a.sjschver) version_name, RTRIM (a.sjschuser) submit_user, RTRIM (a.sjjobque) jobqueue,
convert(varchar(8), dateadd(hour, @offset, dateadd(minute, a.sjschsttime,{d '1970-01-01'})),108) Schedule_StartTime,
convert(varchar(10), dateadd(hour, @offset, dateadd(minute, a.sjschsttime,{d '1970-01-01'})), 101) Schedule_StartDate,
a.sjschrcrtyp schedule_type,
case
when a.sjschrcrtyp in ('61') then 'MINUTE'
when a.sjschrcrtyp in ('62') then 'HOURLY'
when a.sjschrcrtyp in ('11', '12', '21') then 'DAILY'
when a.sjschrcrtyp in ('31') then 'MONTHLY'
end as sched_period,
case
when a.sjschrcrtyp = '61' then 'Every ' + rtrim(cast(a.sjschnummns as char)) + ' mins'
when a.sjschrcrtyp = '62' then 'Every ' + rtrim(cast(a.sjschnumhrs as char)) + ' hours'
when a.sjschrcrtyp = '11' and a.sjschnumdy = 1 then 'Every Day'
when a.sjschrcrtyp = '11' and a.sjschnumdy != 1 then 'Every ' + rtrim(cast(a.sjschnumdy as char)) + ' days'
when a.sjschrcrtyp = '31' then 'On day ' + rtrim(a.sjschnumdy) + ' of the month, Every ' + rtrim(a.sjschnummn) + ' months'
when a.sjschrcrtyp = '12' then 'Weekdays'
when a.sjschrcrtyp = '21' then 'Specified Days'
end as sched_desc,

case when (a.sjschrcrtyp = '11' and a.sjschnumdy = 1) or (a.sjschrcrtyp = '12' or a.sjschrcrtyp = '21' and a.sjmonday = '1') then 'X' else '' end as sched_Monday,
case when (a.sjschrcrtyp = '11' and a.sjschnumdy = 1) or (a.sjschrcrtyp = '12' or a.sjschrcrtyp = '21' and a.sjtuesday = '1') then 'X' else '' end as sched_Tuesday,
case when (a.sjschrcrtyp = '11' and a.sjschnumdy = 1) or (a.sjschrcrtyp = '12' or a.sjschrcrtyp = '21' and a.sjwednesday = '1') then 'X' else '' end as sched_Wednesday,
case when (a.sjschrcrtyp = '11' and a.sjschnumdy = 1) or (a.sjschrcrtyp = '12' or a.sjschrcrtyp = '21' and a.sjthursday = '1') then 'X' else '' end as sched_Thursday,
case when (a.sjschrcrtyp = '11' and a.sjschnumdy = 1) or (a.sjschrcrtyp = '12' or a.sjschrcrtyp = '21' and a.sjfriday = '1') then 'X' else '' end as sched_Friday,
case when (a.sjschrcrtyp = '11' and a.sjschnumdy = 1) or (a.sjschrcrtyp = '21' and a.sjsaturday = '1') then 'X' else '' end as sched_Saturday,
case when (a.sjschrcrtyp = '11' and a.sjschnumdy = 1) or (a.sjschrcrtyp = '21' and a.sjsunday = '1') then 'X' else '' end as sched_Sunday

FROM sy812.f91300 a
WHERE a.sjschjbstat = '01'
and a.sjschrcrtyp in ('61', '62', '11', '12', '21', '31') ) as d
left outer join

(

select a.sjschjbnm,
count(1) as run_count,
max(
convert(varchar(8), dateadd(second,
datediff(s,
dateadd(hour, @offset, dateadd(minute, b.jsschlnctime,{d '1970-01-01'})),
cast(
convert(varchar(10), dateadd(d, substring(cast(c.jcactdate as char), 4, 3)-1, convert(datetime, '20' + substring(cast(c.jcactdate as char), 2, 2) + '-01-01 ')), 101) +
' ' +
substring(right('000000', 6 - len(c.jcacttime)) + cast(jcacttime as char), 1,2) + ':' +
substring(right('000000', 6 - len(c.jcacttime)) + cast(jcacttime as char), 3,2) + ':' +
substring(right('000000', 6 - len(c.jcacttime)) + cast(jcacttime as char), 5,2)
as datetime)
), 0), 108)) as max_run_time,


convert(varchar(8), dateadd(second,
avg(datediff(s,
dateadd(hour, @offset, dateadd(minute, b.jsschlnctime,{d '1970-01-01'})),
cast(
convert(varchar(10), dateadd(d, substring(cast(c.jcactdate as char), 4, 3)-1, convert(datetime, '20' + substring(cast(c.jcactdate as char), 2, 2) + '-01-01 ')), 101) +
' ' +
substring(right('000000', 6 - len(c.jcacttime)) + cast(jcacttime as char), 1,2) + ':' +
substring(right('000000', 6 - len(c.jcacttime)) + cast(jcacttime as char), 3,2) + ':' +
substring(right('000000', 6 - len(c.jcacttime)) + cast(jcacttime as char), 5,2)
as datetime)
)), 0), 108) as avg_run_time,

max(
convert(varchar(8), dateadd(second,
datediff(s,
dateadd(hour, @offset, dateadd(minute, b.jsschsttime,{d '1970-01-01'})),
dateadd(hour, @offset, dateadd(minute, b.jsschlnctime,{d '1970-01-01'}))
), 0), 108)) as max_wait_time,

convert(varchar(8), dateadd(second,
avg(
datediff(s,
dateadd(hour, @offset, dateadd(minute, b.jsschsttime,{d '1970-01-01'})),
dateadd(hour, @offset, dateadd(minute, b.jsschlnctime,{d '1970-01-01'}))
)), 0), 108) as avg_wait_time

FROM sy812.f91300 a
join sy812.f91320 as b on a.sjschjbnm = b.jsschjbnm
join svm812.f986110 as c on b.jsjobnbr = c.jcjobnbr
WHERE a.sjschjbstat = '01'
and a.sjschrcrtyp in ('61', '62', '11', '12', '21', '31')
and b.jsschlncstat = 20 and jslncenvh = 'PD812'
and b.jsschlnctime < b.jsschsttime + 1440
and c.jcjobsts = 'D'
group by a.sjschjbnm
) as e
on d.Scheduler_job_name = e.sjschjbnm
order by d.sched_period, d.Schedule_StartTime, d.ube_name, d.version_name
 
Here's a db2 version for IBM i

/********************************************/

select d.Scheduler_job_name, e.run_count, d.ube_name, d.version_name, d.submit_user, d.jobqueue, d.Schedule_StartDate,
d.Schedule_StartTime, d.schedule_type, d.sched_period, d.sched_desc,
d.sched_Monday, d.sched_Tuesday, d.sched_Wednesday, d.sched_Thursday, d.sched_Friday,
d.sched_Saturday, d.sched_Sunday from
(
SELECT RTRIM (a.sjschjbnm) Scheduler_job_name, RTRIM (a.sjschrptnm) ube_name,
RTRIM (a.sjschver) version_name, RTRIM (a.sjschuser) submit_user, RTRIM (a.sjjobque) jobqueue,
char(date(timestamp('1970-01-01 00:00:00') + a.sjschsttime minutes + current timezone - 1 hour)) Schedule_StartDate,
char(time(timestamp('1970-01-01 00:00:00') + a.sjschsttime minutes + current timezone - 1 hour)) Schedule_StartTime,
a.sjschrcrtyp schedule_type,
case
when a.sjschrcrtyp in ('61') then 'MINUTE'
when a.sjschrcrtyp in ('62') then 'HOURLY'
when a.sjschrcrtyp in ('11', '12', '21') then 'DAILY'
when a.sjschrcrtyp in ('31', '32') then 'MONTHLY'
end as sched_period,
case
when a.sjschrcrtyp = '61' then 'Every ' || rtrim(cast(a.sjschnummns as char)) || ' mins'
when a.sjschrcrtyp = '62' then 'Every ' || rtrim(cast(a.sjschnumhrs as char)) || ' hours'
when a.sjschrcrtyp = '11' and a.sjschnumdy = 1 then 'Every Day'
when a.sjschrcrtyp = '11' and a.sjschnumdy != 1 then 'Every ' || rtrim(cast(a.sjschnumdy as char)) || ' days'
when a.sjschrcrtyp = '31' then 'On day ' || rtrim(a.sjschnumdy) || ' of the month, Every ' || rtrim(a.sjschnummn) || ' months'
when a.sjschrcrtyp = '32' then 'See Scheduled Job for details'
when a.sjschrcrtyp = '12' then 'Weekdays'
when a.sjschrcrtyp = '21' then 'Specified Days'
end as sched_desc,

case when (a.sjschrcrtyp = '11' and a.sjschnumdy = 1) or (a.sjschrcrtyp = '12' or a.sjschrcrtyp = '21' and a.sjmonday = '1') then 'X' else '' end as sched_Monday,
case when (a.sjschrcrtyp = '11' and a.sjschnumdy = 1) or (a.sjschrcrtyp = '12' or a.sjschrcrtyp = '21' and a.sjtuesday = '1') then 'X' else '' end as sched_Tuesday,
case when (a.sjschrcrtyp = '11' and a.sjschnumdy = 1) or (a.sjschrcrtyp = '12' or a.sjschrcrtyp = '21' and a.sjwednesday = '1') then 'X' else '' end as sched_Wednesday,
case when (a.sjschrcrtyp = '11' and a.sjschnumdy = 1) or (a.sjschrcrtyp = '12' or a.sjschrcrtyp = '21' and a.sjthursday = '1') then 'X' else '' end as sched_Thursday,
case when (a.sjschrcrtyp = '11' and a.sjschnumdy = 1) or (a.sjschrcrtyp = '12' or a.sjschrcrtyp = '21' and a.sjfriday = '1') then 'X' else '' end as sched_Friday,
case when (a.sjschrcrtyp = '11' and a.sjschnumdy = 1) or (a.sjschrcrtyp = '21' and a.sjsaturday = '1') then 'X' else '' end as sched_Saturday,
case when (a.sjschrcrtyp = '11' and a.sjschnumdy = 1) or (a.sjschrcrtyp = '21' and a.sjsunday = '1') then 'X' else '' end as sched_Sunday

FROM sy910.f91300 a
WHERE a.sjschjbstat = '01'
and a.sjschrcrtyp in ('61', '62', '11', '12', '21', '31', '32') ) as d
left outer join
(
select a.sjschjbnm,
count(1) as run_count
FROM sy910.f91300 a
join sy910.f91320 as b on a.sjschjbnm = b.jsschjbnm
join svm910.f986110 as c on b.jsjobnbr = c.jcjobnbr
WHERE a.sjschjbstat = '01'
and a.sjschrcrtyp in ('61', '62', '11', '12', '21', '31', '32')
and b.jsschlncstat = 20 and b.jslncenvh in ('PD910', 'UPD910')
and b.jsschlnctime < b.jsschsttime || 1440
and c.jcjobsts = 'D'
group by a.sjschjbnm
) as e
on d.Scheduler_job_name = e.sjschjbnm
order by d.sched_period, d.Schedule_StartTime, d.ube_name, d.version_name ;
 
Hi - just wanted to drop a quick thanks in here. This was a fantastic thread. This is the kind of thing I love about JDE List. Thank you!
 
Back
Top