AnsweredAssumed Answered

Query to identify projects that do not contain resources from a sub-query

Question asked by matpj on Sep 6, 2010
Latest reply on Sep 7, 2010 by matpj
Hi all,

my mind has gone totally blank, and the more I look at this problem the less luck I am having!
I have a query to identify a group of support projects in 7.5.3:
Select distinct




niku.nbi_project_current_facts nbip,

niku.odf_ca_project cst,

niku.prtask t,

niku.prchargecode ch,

niku.prteam te,

    niku.srm_resources res

where = nbip.project_id

  and te.prprojectid = nbip.project_id

  and te.prresourceid =

and cst.dsti_proj_act = 'SUP'

and nbip.project_class not in ('101BPM', '593TST', '501HIA', '999SUP', '570ATL')

and nbip.project_code NOT IN ('DST015588', 'DST016270')

and nbip.is_active = 1

and (cst.dsti_closed = 0 OR cst.dsti_closed IS NULL) and

t.prprojectid = nbip.project_id and

t.prchargecodeid = ch.prid

and ch.prexternalid like 'SUP%' 
I also have a query to identify a list of support resources:
select res.full_name, res.unique_name , dep.description

from niku.srm_resources res,

niku.pac_mnt_resources pac,

niku.departments dep

where res.unique_name = pac.resource_code

and pac.departcode = dep.departcode

and res.is_active = 1

and description like 'IMS%'

and UPPER(dep.description) like '%SUP%'
I need to incorporate this resource query into the first so that if there is a resource on that list that is NOT included in the project team, then the project should be returned....

Any ideas.
I'm sure it is so blatantly obvious and very easy, but I have drawn a blank and simply cant think of how to do it!! lol

thanks in advance,