Query Resources the jobs

Some examples running with Control-M and Control-EM database.
Post Reply
User avatar
futre25
Nouveau
Nouveau
Posts: 166
Joined: 11 Aug 2009 12:00

Query Resources the jobs

Post by futre25 » 20 Jun 2011 10:40

Hi to all.

I need to know that jobs use two unique resources.

I did a query but duplicate jobs:

select a.job_name, b.RESOURCE_
from def_job a, DEF_LNKI_C b
where RESOURCE_ = 'SINCMULT01'
and a.JOB_ID=b.JOB_ID

Or filter for two resources, the output is 0 rows, when there are jobs with both resources.


select a.job_name, b.RESOURCE_
from def_job a, DEF_LNKI_C b
where RESOURCE_ = 'SINCMULT01' and RESOURCE_ = 'COC700'
and a.JOB_ID=b.JOB_ID

Exists any query or way of knowing that jobs using these two resources.

User avatar
gglau
Nouveau
Nouveau
Posts: 317
Joined: 13 Jun 2007 12:00

Post by gglau » 20 Jun 2011 8:04

try this

SELECT JOB_NAME, A.RESOURCE_, B.RESOURCE_ FROM DEF_JOB AS J,
(SELECT RESOURCE_, JOB_ID, TABLE_ID FROM DEF_LNKI_C WHERE RESOURCE_='SINCMULT01') A,
(SELECT RESOURCE_, JOB_ID, TABLE_ID FROM DEF_LNKI_C WHERE RESOURCE_='COC700') B
WHERE J.JOB_ID=A.JOB_ID AND J.TABLE_ID=A.TABLE_ID
AND J.JOB_ID=B.JOB_ID AND J.TABLE_ID=B.TABLE_ID
AND A.JOB_ID=B.JOB_ID AND A.TABLE_ID=B.TABLE_ID

User avatar
futre25
Nouveau
Nouveau
Posts: 166
Joined: 11 Aug 2009 12:00

Post by futre25 » 21 Jun 2011 9:35

Thanks very much for your reply.

Your answer is what I needed.

Post Reply