View Status of Jobs Submitted via Grid Control

select b.job_name, b.job_description, b.job_owner, a.scheduled_time, a.end_time,
DECODE(a.status,
1, 'SCHEDULED',
2, 'RUNNING',
3, 'FAILED INIT',
4, 'FAILED',
5, 'SUCCEEDED',
6, 'SUSPENDED',
7, 'AGENT DOWN',
8, 'STOPPED',
9, 'SUSPENDED/LOCK',
10, 'SUSPENDED/EVENT',
11, 'SUSPENDED/BLACKOUT',
12, 'STOP PENDING',
13, 'SUSPEND PENDING',
14, 'INACTIVE',
15, 'QUEUED',
16, 'FAILED/RETRIED',
17, 'WAITING',
18, 'SKIPPED', a.status) "STATUS"
from mgmt_job_exec_summary a, mgmt_job b
order by b.job_name, a.scheduled_time;

Use PIVOT to Create a Cross-Tabulation

This query will generate a table that shows each employee and the number of days they have called in for either Sick, Bereavement or MISC.  The leave_type_code is a single database column, but the PIVOT allows you to make several columns from its values.

WITH pivot_data AS (
select ed.employee_id NAME, psa.leave_type_code LTC, psa.shift_date SHIFT_DATE
from
employee_detail ed, pa_shift_allocation psa
where ed.employee_id=psa.employee_id
and ed.is_fake='N'
and psa.assignment_type_code='L'
and psa.shift_date BETWEEN '01-JAN-10' and '31-DEC-10')
select * from pivot_data
pivot (count(shift_date) for LTC in ('S' as SK,'B' as BV,'M'as MISC))
order by SK DESC;

NAME                 SK                     BV                     MISC                  
-------------------- ---------------------- ---------------------- ----------------------
EM1018               121                    0                      0                     
EM1034               61                     5                      0                     
EM1022               60                     0                      156                   
EM1019               60                     0                      154                   
EM1065               35                     1                      0                     
EM1107               33                     5                      0                     
EM1029               24                     0                      0                     
EM1136               23                     2                      0                     
EM1134               21                     0                      0                     
EM1151               21                     2                      0                     
EM1025               19                     0                      40