Searching for null values in UCM

Sometimes when searching with metadata in UCM, it’s helpful to be able to find items that have null or blank values. And while it is possible to perform these types of searches, the method to do so is a bit tricky and varies depending on your search engine.

To find out what search engine you’re UCM instance is configured for, go to Administration -> Configuration for [instance name] and look for Index Engine Name.

If you are using ORACLETEXTSEARCH (or still running Verity), then there is nothing you need to change on the server side.

First go to the Standard Search page and switch to the Query Builder Form (if you aren’t already on that form). You can switch by going to the Switch Forms menu at the top right. Then in the QueryBuilder section, select the field you want to search by. For the operator, select ‘matches’ and for the value just type in ‘1’. Now click the ‘show advanced options’ link and click the ‘Modify Query Text’ checkbox. Now just remove the 1 in the back quotes so that it is empty and hit Search.

Now in your results, you should get back all of the items that don’t have a value in that field.

If you are using the DATABASE.METADATA or DATABASE.FULLTEXT index engine, then the search above will not work. You must have a component which translates the empty string into a search for null values in the resulting database query. This

component will do that translation on the queries and work with the same search criteria mentioned above. As an alternative, the OracleCaseInsenstiveSearch component which comes with the CS10gR35UpdateBundle component can also provide this capability. When using that component, the syntax for the query is a bit different. You must specify a <NOT> clause and use a * in the criteria. So the query would look like <NOT>(xComments <matches> `*`). But this component should only be used in smaller collections (under 500k or so) as it can impact query performance.These techniques work for text-based fields, but for date fields they won’t. For DATABASE.METADATA or DATABASE.FULLTEXT as the search engine, I do not know of a query which can find find empty date values. But with ORACLETEXTSEARCH, there is a query that works. Go to the Query Builder Form and select the date field. Keep the operator as ‘On or After’ and select the current date/time. Then add another row, change the ‘AND’ to an ‘OR’, select the date field, change the operator to ‘Before’ and select the current date/time again. Then click the ‘show advanced options’ link and click the ‘Modify Query Text’ checkbox. Surround the query in parenthesis and add in <NOT> at the start.

Now you’ll get a list of all the documents that don’t have a value for the date field.

*Note: This post also applies to Universal Records Management (URM).