/* Formatted on 2012/08/09 15:07 (Formatter Plus v4.8.7) */
SELECT 'SELECT TO_CHAR('
|| column_name
|| ',''MON-YYYY'') "month-year('||table_name||')",COUNT(*) FROM SOA_SOAINFRA.'
|| table_name
|| ' GROUP BY TO_CHAR('
|| column_name
|| ',''MON-YYYY'' ) order by max(CREATION_DATE) ;'
FROM (SELECT DISTINCT table_name, column_name
FROM cols
WHERE ( column_name LIKE 'CREATION_DATE%'
OR column_name LIKE 'CREATED_TIME%'
))
/* Formatted on 2012/08/09 17:35 (Formatter Plus v4.8.7) */
SELECT (CASE
WHEN state = 1
THEN 'OPEN AND RUNNING'
WHEN state = 2
THEN 'OPEN AND SUSPENDED'
WHEN state = 3
THEN 'OPEN AND FAULTED'
WHEN state = 4
THEN 'CLOSED AND PENDING'
WHEN state = 5
THEN 'CLOSED AND COMPLETED'
WHEN state = 6
THEN 'CLOSED AND FAUTED'
WHEN state = 7
THEN 'CLOSED AND CANCELLED'
WHEN state = 8
THEN 'CLOSED AND ABORTED'
WHEN state = 9
THEN 'CLOSED AND STALE'
WHEN state = 10
THEN 'NON-RECOVERABLE'
ELSE state || ''
END
)|| '-' ||state AS state,
TO_CHAR (creation_date, 'MON-YYYY') "month-year(CUBE_INSTANCE)",
COUNT (*)
FROM soa_soainfra.cube_instance
GROUP BY TO_CHAR (creation_date, 'MON-YYYY'), state
ORDER BY MAX (creation_date);
/* Formatted on 2012/08/09 17:35 (Formatter Plus v4.8.7) */
SELECT (CASE
WHEN component_state = 0
THEN 'No faults but there still might be running instances'
WHEN component_state = 1
THEN ' At least one case is aborted by user'
WHEN component_state = 2
THEN ' At least one case is faulted (non-recoverable)'
WHEN component_state = 3
THEN ' At least one case is faulted and one case is aborted'
WHEN component_state = 4
THEN ' At least one case is in recovery required state'
WHEN component_state = 5
THEN 'At least one case is in recovery required state and at least one is aborted'
WHEN component_state = 6
THEN 'At least one case is in recovery required state and at least one is faulted'
WHEN component_state = 7
THEN 'At least one case is in recovery required state, one faulted and one aborted'
WHEN component_state >= 8 and component_state <16
THEN 'Running'
WHEN component_state >= 16
THEN 'Stale'
ELSE component_state || ''
END
) ||'-'||component_state AS component_state,
TO_CHAR (created_time, 'MON-YYYY') "month-year(CUBE_INSTANCE)",
COUNT (*)
FROM soa_soainfra.mediator_instance
GROUP BY TO_CHAR (created_time, 'MON-YYYY'), component_state
ORDER BY MAX (created_time);
