Query:
Code: Select all
select JOBNAME as JOB,
(
case when STARTRUN is not null and STARTRUN <> ' '
then substr(STARTRUN, 1, 8) || ' ' || substr(STARTRUN, 9, 2) || ':' || substr(STARTRUN, 11, 2) || ':' || substr(STARTRUN, 13, 2)
else ''
end
) as START,
(
case when ENDRUN is not null and STARTRUN <> ' '
then substr(ENDRUN, 1, 8) || ' ' || substr(ENDRUN, 9, 2) || ':' || substr(ENDRUN, 11, 2) || ':' || substr(ENDRUN, 13, 2)
else ''
end
) as END,
(
case when STATUS = 'Y'
then '0'
else '1'
end
) as STATUS
from CMR_AJF
where APPLGROUP in ('MONTHLY-TABLE')
and HOLDFLAG <> 'D'
ORDER BY END;
Code: Select all
MONTHLY-JOB-1 20150728 19:01:22 20150728 19:01:22 0
MONTHLY-JOB-2 20150728 19:01:24 20150728 19:01:53 0
MONTHLY-JOB-3 20150728 19:01:54 20150728 19:01:56 0
MONTHLY-JOB-4 20150728 19:01:56 20150728 19:01:58 0
MONTHLY-JOB-5 20150728 19:01:57 20150728 19:01:58 0
MONTHLY-JOB-6 20150728 19:01:56 20150728 19:02:03 0
the problem is, I need the data of each time the job were executed, example:
Code: Select all
MONTHLY-JOB-1 20150728 19:01:22 20150728 19:01:22 0
MONTHLY-JOB-2 20150728 19:01:24 20150728 19:01:53 0
MONTHLY-JOB-3 20150728 19:01:54 20150728 19:01:56 0
MONTHLY-JOB-4 20150728 19:01:56 20150728 19:01:58 0
MONTHLY-JOB-5 20150728 19:01:57 20150728 19:01:58 0
MONTHLY-JOB-6 20150728 19:01:56 20150728 19:02:03 0
MONTHLY-JOB-6 20150728 20:01:56 20150728 20:02:03 0 << this is the second time
thanks.