It's all about the answers!

Ask a question

How do I querry if fields are NULL or not set?


Donald Mason (5144) | asked Mar 06 '13, 9:46 a.m.
I am using the restful web interface into a 4.0 version of RTC.  I can do many queries for specific =, <, or > but I am unable to find a way to search for NULL values.  I would like to get results for when a workitem does not have a resolutionDate, or if it does have a resolutionDate so that I could get reports of all resolved or unresolved work items.


This same unresolved question appears at http://www.ibm.com/developerworks/forums/thread.jspa?threadID=467391&start=15&tstart=0.

3 answers



permanent link
Lauren Hayward Schaefer (3.3k11427) | answered Mar 07 '13, 7:37 a.m.
JAZZ DEVELOPER
Hi Donald,
Some attributes have an "Unassigned" option:

Resolution date doesn't have that option.  However, if you're simply looking for a list of unresolved or resolved work items, the Status can be queried for Unresolved or Resolved:


Comments
Donald Mason commented Mar 07 '13, 9:12 a.m.

Thank you for answering, but I am not looking for how to do this via the gui options.  I am looking for how to query this with the rest interface. 

Here is an example:
https://jazzserver.com:9443/jazz/rpt/repository/workitem?fields=workitem/workItem[creator/name=%27Donald%27%20or%20owner/name=%27Donald%27%20and%20projectArea/name=%27PRojrect%27]/%28id|summary|target/name|state/name|creator/name|owner/name|type/name|resolution/name|parent%20%29&size=100&pos=100

I can print out the 'resolutionDate', I can also search for a specific date, but I do not know how to search for 'resolutionDate' that is NULL or NOT NULL. 


Lauren Hayward Schaefer commented Mar 07 '13, 10:31 a.m.
JAZZ DEVELOPER

Hi Donald,
I don't have any experience with creating queries using the rest interface.  It seems like it should be possible to query for unresolved or resolved in the same way that the gui does.  https://jazz.net/forum/questions/13870/querying-for-sorted-list-of-quotunresolvedquot-work-item has an example that might help you.


Donald Mason commented Mar 07 '13, 12:38 p.m.

He Lauren,  that example shows some language (java maybe), but I am not sure how to translate that back to the REST web query.  Also, it is a workaround of getting a value by keying off another value.  It does not answer how to do the general query of how to find NULL or NOT NULL.  I will be writing many different queries and would need to check for NULL on any field. 
Thanks for you input, and I hope we can find someone who knows this interface that can give an answer.


permanent link
Glenn Henderson (635) | answered Oct 17 '14, 8:46 a.m.
Is there a valid answer to this question?  I'm tasked with the same issue, trying to create a REST based query where the field / entry is null.

Comments
Donald Mason commented Oct 27 '14, 3:19 p.m.

I wish there was, I was never able to figure it out. 


Donald Nong commented Oct 28 '14, 5:30 a.m.

In OSLC specification, there is no mentioning about testing null value in the query capability, so I doubt that any product implementing OSLC will have such capability.


permanent link
Dave Evans (1382137) | answered Nov 01 '19, 12:18 p.m.

The only way to do this, that I can see, is either:

state/id=open state1id or state/id=open state2id
OR
state/id!=closedState1id and state/id!=closedState2id

So this is stinky. But don't worry, it gets stinkier!

If you need your application to be dynamic to support a variety of projects with a variety of states... or don't want to worry about maintaining the list of open or closed state ids... then you can programatically get the state ids of the states in the closed group, like so:
1) Query the project area for work item type ids (using OSLC):
/ccm/oslc/types/_ProjectAreaId

2) Iteratively query each of the work item shapes for work items you're interested in to find the workflow IDs:
/ccm/oslc/contexts/_ProjectAreaId/shapes/workitems/workItemId (from step 1)
Note: This query requires OSLC version 2.0, so you must set a header: "OSLC-Core-Version: 2.0"

3) Iteratively query the workflow IDs found in step 2 to determine available states, and state groups:
/ccm/oslc/workflows/_ProjectAreaId/states/workFlowId (from step 2).

It sure would be nice if there was an easier way. Normally I would never resurrect an old post... but I had this question and could not see another answer in the forum... so had to go and painfully figure this out.

Your answer


Register or to post your answer.