SOA INFRA Queries

In this post you will see queries related to SOA_INFRA ..

SELECT CMPST_ID Composite_ID,
COMPOSITE_NAME,
DECODE(STATE,0, ‘INITIATED’,
1, ‘OPEN_RUNNING’,
2, ‘OPEN_SUSPENDED’,
3, ‘OPEN_FAULTED’,
4, ‘CLOSED_PENDING_CANCEL’,
5, ‘CLOSED_COMPLETED’,
6, ‘CLOSED_FAULTED’,
7, ‘CLOSED_CANCELLED’,
8, ‘CLOSED_ABORTED’,
9, ‘CLOSED_STALE’,
10,’CLOSED_ROLLED_BACK’) STATE,
Status,
TO_CHAR(creation_date,’DD-MON-YYYY HH24:MI:SS’) AS start_date,
TO_CHAR(modify_date,’DD-MON-YYYY HH24:MI:SS’) AS modify_date,
(NVL(modify_date,SYSDATE) – creation_date ) res_time
FROM DEV_SOAINFRA.CUBE_INSTANCE

Query –>  Only Open composites

SELECT CMPST_ID Composite_ID,
COMPOSITE_NAME,
DECODE(STATE,0, ‘INITIATED’,
1, ‘OPEN_RUNNING’,
2, ‘OPEN_SUSPENDED’,
3, ‘OPEN_FAULTED’,
4, ‘CLOSED_PENDING_CANCEL’,
5, ‘CLOSED_COMPLETED’,
6, ‘CLOSED_FAULTED’,
7, ‘CLOSED_CANCELLED’,
8, ‘CLOSED_ABORTED’,
9, ‘CLOSED_STALE’,
10,’CLOSED_ROLLED_BACK’
) STATE,
Status,
TO_CHAR(creation_date,’DD-MON-YYYY HH24:MI:SS’) AS start_date,
TO_CHAR(modify_date,’DD-MON-YYYY HH24:MI:SS’) AS modify_date,
(NVL(modify_date,SYSDATE) – creation_date ) res_time
FROM DEV_SOAINFRA.CUBE_INSTANCE
where 1=1
–and composite_name = ‘xxxx’
AND STATE IN (0,1,2,3)
ORDER BY creation_date DESC

Query –> For Datewise count for each composite in Different State’s

SELECT COMPOSITE_NAME,
DECODE(STATE,0, ‘INITIATED’,
1, ‘OPEN_RUNNING’,
2, ‘OPEN_SUSPENDED’,
3, ‘OPEN_FAULTED’,
4, ‘CLOSED_PENDING_CANCEL’,
5, ‘CLOSED_COMPLETED’,
6, ‘CLOSED_FAULTED’,
7, ‘CLOSED_CANCELLED’,
8, ‘CLOSED_ABORTED’,
9, ‘CLOSED_STALE’,
10,’CLOSED_ROLLED_BACK’
) STATE,
TO_CHAR(creation_date,’DD-MON-YYYY’) AS start_date,
Count(*)
FROM DEV_SOAINFRA.CUBE_INSTANCE
where 1=1
–and composite_name = ‘xxxx’
AND STATE IN (0,1,2,3)
GROUP BY COMPOSITE_NAME, state,TO_CHAR(creation_date,’DD-MON-YYYY’)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s