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.

search_null.png

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.

query_builder_nulls1.png

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

DatabaseSearchNullsWorkaround.zip
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.

query_builder_nulls_dates.png

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).

Comments:

Note: you can also use a different query format for these special searches. If you manipulate the url like following you can search for null a little easier (also this performs better and does not require an additional component).
&SearchQueryFormat=Database&QueryText=dOutDate is null
Only downsize is that you cannot do this from the regular user interface (but you could build a special template for it).

Posted by Fabian Scherpenzeel on January 27, 2011 at 11:21 PM CST #

Thanks for the post, Kyle! This is very helpful. Also thanks to Fabian. I am running some complex Advanced Search queries from a Site Studio site and was having difficulty figuring out how to query for a null date value.

Posted by Randy Roche on March 24, 2011 at 02:46 AM CDT #

For DATABASE.FULLTEXT there is a way to search for null dates. Treat it as a text search.

In the Search page text field enter the date parameter, example:

dInDate>= {ts '2011-08-01 00:00:00.000'}

That will be interpreted by the CS as:

<ftx>dInDate>= {ts '2011-08-01 00:00:00.000'}</ftx>

To do the null for a date field, the entry would be like this:

dInDate>= {ts '2011-08-01 00:00:00.000'} and dOutDate is null

That will interpreted as:

<ftx>dInDate>= {ts '2011-08-01 00:00:00.000'} and dOutDate is null</ftx>

The resulting query will be:

SELECT IdcColl2.dID, dDocName, dDocTitle, dDocType, dRevisionID, dSecurityGroup, dDocAuthor, dDocAccount, dRevLabel, dFormat, dOriginalName, dExtension, dWebExtension, dInDate, dOutDate, dCreateDate, dPublishType, dRendition1, dRendition2, VaultFileSize, WebFileSize, URL, dFullTextFormat, dFullTextCharset, DocMeta.*
FROM IdcColl2, DocMeta
WHERE IdcColl2.dID=DocMeta.dID AND ((dInDate>= {ts '2011-08-01 00:00:00.000'} and dOutDate is null)) ORDER BY dInDate Desc

If doing this from a soap wsdl, in the xml file replace the < and > with the HTTP &lt; and &gt;

Posted by guest on October 24, 2011 at 03:49 AM CDT #

Hi Kyle,
I have use case to modify the criteria on the search page UCM10g..For example in the search page is it possible to change the criteria value from 'Matches' to 'start' for the 'Security Group' metadata.

Posted by prabhu on July 09, 2012 at 02:13 AM CDT #

Hello Prabhu,

To set it default on an individual field would require a component. But to make 'Starts' the default operator for all option fields, you can set 'DefaultNBFieldSearchOperator=beginsWith' in your config.cfg. For non-option list fields, that is covered in this blog post: https://blogs.oracle.com/kyle/entry/setting_substring_back_as_defa

Thanks,
-Kyle

Posted by guest on July 09, 2012 at 01:39 PM CDT #

Thanks for your reply.. As mentioned in the blog changing the criteria to Substring for a filed 'Title' requires custom component and this is an expensive query.
Is it possible to write the custom component to change the title field criteria to 'Substring' for a particular profile.. Please advise me.

Posted by guest on July 11, 2012 at 02:28 AM CDT #

Yes, a custom component could certainly set the search operator for specific individual fields on specific profiles.

Thanks,
-Kyle

Posted by Kyle Hatlestad on July 11, 2012 at 08:46 AM CDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Kyle Hatlestad is a Solution Architect in the WebCenter Architecture group (A-Team) who works with WebCenter Content and other products in the WebCenter & Fusion Middleware portfolios. The WebCenter A-Team blog can be found at: https://blogs.oracle.com/ ateam_webcenter/

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today