51 lines
1.4 KiB
SQL
51 lines
1.4 KiB
SQL
@@header
|
|
|
|
/*
|
|
Job State
|
|
Not Started(0)
|
|
Running (1)
|
|
Finished(2)
|
|
*/
|
|
|
|
COLUMN job_state FORMAT a15
|
|
COLUMN "Count" FORMAT 99999
|
|
COLUMN max_start_date FORMAT a18
|
|
|
|
SELECT * FROM
|
|
(
|
|
select * from
|
|
(
|
|
select wl.program_name
|
|
, count(1) "Count"
|
|
, DECODE(wl.job_state
|
|
,'-1','Error(-1)'
|
|
,'0','Not Started(0)'
|
|
,'1','Running (1)'
|
|
,'2','Finished(2)'
|
|
,wl.job_state
|
|
) job_state
|
|
, TO_CHAR(max(wl.start_date),'DD-MON-YY HH24:MI:SS') max_start_date
|
|
, TO_CHAR(max(wl.end_date),'DD-MON-YY HH24:MI:SS') max_end_date
|
|
, TO_CHAR(min(wl.start_date),'DD-MON-YY HH24:MI:SS') min_start_date
|
|
, TO_CHAR(min(wl.end_date),'DD-MON-YY HH24:MI:SS') min_end_date
|
|
from pm_own.pm_work_list wl
|
|
where 1=1
|
|
--and program_name ='ctrf'
|
|
and wl.program_name in ( select distinct program_name from pm_own.pm_work_list where job_state = 1)
|
|
and wl.start_date > sysdate - 1
|
|
group by wl.program_name, DECODE(wl.job_state
|
|
,'-1','Error(-1)'
|
|
,'0','Not Started(0)'
|
|
,'1','Running (1)'
|
|
,'2','Finished(2)'
|
|
,wl.job_state
|
|
)
|
|
order by max(wl.start_date) desc
|
|
)
|
|
where rownum <= 20
|
|
)
|
|
ORDER BY program_name, job_state
|
|
;
|
|
|
|
@@footer
|