Need help with ClearQuest advanced query

Share URL
 
DevelTec's picture
DevelTec asked on June 22, 2012 - 2:34pm | Replies (1).

I have a stated record type of Escalation which has a parent/child relationship with a stateless record type of CodeStreams. CodeStreams has a resolution field for each child CodeStream. There can be many CodeStreams to one escalation.

I am trying to create a query that will only return records in which none of the children have a resolution of "fixed".

I have tried a variety of and/or options to no avail, seems some records still come back where a child is "fixed". I am thinking this may have to be done with SQL, any help would be much appreciated.

Tags: 
change management
clearquest

1 Answer

DevelTec's picture
DevelTec replied on July 26, 2012 - 5:01pm.

This SQL query did the trick:

select distinct T1.dbid,T1.id,T4.name,T1.headline,T1.resolution,T40.resolution
from
(
(
( escalation T1 INNER JOIN statedef T4 ON T1.state = T4.id )
LEFT OUTER JOIN parent_child_links T40mm ON T1.dbid = T40mm.parent_dbid and 16783175 = T40mm.parent_fielddef_id
) LEFT OUTER JOIN codestreams T40 ON T40mm.child_dbid = T40.dbid
)
where T1.dbid <> 0 and
(((T40.resolution not in('Fixed') or T40.resolution is not NULL) and T4.name = 'Closed'))
and T1.id not in (
select distinct T1.id
from
(
(
( escalation T1 INNER JOIN statedef T4 ON T1.state = T4.id )
LEFT OUTER JOIN parent_child_links T40mm ON T1.dbid = T40mm.parent_dbid and 16783175 = T40mm.parent_fielddef_id
) LEFT OUTER JOIN codestreams T40 ON T40mm.child_dbid = T40.dbid
)
where T1.dbid <> 0 and
(((T40.resolution in('Fixed')) and T4.name = 'Closed'))) order by T1.id desc

CMCrossroads is a TechWell community.

Through conferences, training, consulting, and online resources, TechWell helps you develop and deliver great software every day.