select to_char(xhssts) as "Status", count(xhssts) as "count_records" from proddta.f4215 where xhssts <> ' ' and xhupmj > 118340 group by xhssts union select ' ' as "Status" , count(xhssts) as "count_records" from proddta.f4215 where exists ( select 1 from proddta.f4215 where xhssts = ' ' and xhupmj > 118340 group by xhssts ) and xhssts = ' ' and xhupmj > 118340 group by xhssts union select ' ' as "Status" , 0 as "count_records" from dual where not exists ( select 1 from proddta.f4215 where xhssts = ' ' and xhupmj > 118340 ) select 'Jobs_Waiting_For_Processing' as Job_Name, count(*) as job_count , JCSBMDATE , min(to_char(to_date(lpad(JCSBMTIME, 6, '0'), 'hh24miss'), 'hh24:mi:ss')) as min_time , max(to_char(to_date(lpad(JCSBMTIME, 6, '0'), 'hh24miss'), 'hh24:mi:ss')) as max_time , round((max(to_date(lpad(JCSBMTIME, 6, '0'), 'hh24miss')) - min(to_date(lpad(JCSBMTIME, 6, '0'), 'hh24miss'))) * 24 * 60 *60 , 0) as time_gap from SVM920.f986110 where JCJOBSTS > 'Q' group by JCSBMDATE select 'Watiting For RTE' as "Status_Name" , '3' as "Event_State" , 0 as "Count" from dual where not exists ( select 1 from SY920.f90710 where to_number(to_char(ETEVNTTIME,'RRDDDHH24MISS'))+100000000000<=to_number(to_char(sysdate-(10/1440),'RRDDDHH24MISS'))+100000000000 and ETEVNTST = '3' ) UNION select 'Watiting For RTE' as "Status_Name" , '3' as "Event_State" , count(ETEVNTST) as "Count" from SY920.f90710 where exists ( select 1 from SY920.f90710 where to_number(to_char(ETEVNTTIME,'RRDDDHH24MISS'))+100000000000<=to_number(to_char(sysdate-(10/1440),'RRDDDHH24MISS'))+100000000000 and ETEVNTST = '3' ) and to_number(to_char(ETEVNTTIME,'RRDDDHH24MISS'))+100000000000<=to_number(to_char(sysdate-(10/1440),'RRDDDHH24MISS'))+100000000000 and ETEVNTST = '3' group by ETEVNTST UNION select 'Processed by RTE' as "Status_Name" , '5' as "Event_State" , 0 as "Count" from dual where not exists ( select 1 from SY920.f90710 where to_number(to_char(ETEVNTTIME,'RRDDDHH24MISS'))+100000000000<=to_number(to_char(sysdate-(10/1440),'RRDDDHH24MISS'))+100000000000 and ETEVNTST = '5' ) union select 'Processed by RTE' as "Status_Name" , '5' as "Event_State" , count(ETEVNTST) as "Count" from SY920.f90710 where exists ( select 1 from SY920.f90710 where to_number(to_char(ETEVNTTIME,'RRDDDHH24MISS'))+100000000000<=to_number(to_char(sysdate-(10/1440),'RRDDDHH24MISS'))+100000000000 and ETEVNTST = '5' ) and to_number(to_char(ETEVNTTIME,'RRDDDHH24MISS'))+100000000000<=to_number(to_char(sysdate-(10/1440),'RRDDDHH24MISS'))+100000000000 and ETEVNTST = '5' group by ETEVNTST