Querying with Date Field

As you can see on the screenshot below, Date field query contains unique query operators.

The query supports standard operators (such as 'equals') and in addition to that, it offers the following unique extras:

• Today - this operator translates as "Last 1 day"

• Last - this operator will search back for all date fields in a specified range, including "today"

• Before and After - those operators are equivalent to previously used < and > respectively

The query operators are also supported in the Form Based Query as you can see on the screenshot below

Case Insensitive Text Search

This useful text search option has been introduced in IJC 5.7. All search operators are supported except "Is null" and "Is not null" where it's logically not an option. Text searches are case sensitive by default when using Oracle or Derby. MySQL allows only case insensitive search by now, regardless of the actual setting in the schema/query.

This property must be specified separately at the given text field. There is currently no way how to set it globally for all text fields present in the schema. Settings are controlled at two different levels, permanent and temporary. You can permanently set flag Case Insensitive Search in Extra attributes of a text field in Data trees/Entities editor. Default behaviour is determined by that Extra attribute and it's stored permanently in the schema settings.

You may want to change it instantly in query builder. Simple thick box is shown at a text field. This is also possible in a form based query where it's accessible in pop-up menu. Query can be combined and for the same field setting of Case Insensitive Search can differ. Current settings will be held in memory until you stay connected to the schema unless the query is cleared.

Additionally all query settings can be stored as a permanent (temporary) query. Textual chemical terms field are supported as well.

Structure search operators

With structure fields you can specify queries of type:

• Substructure: The target molecule (graph) contains the specified query structure (graph)

• Superstructure: The target molecule (graph) is contained within the query structure (graph)

• Similarity: A measure of how similar the target is to the query, based upon structural fragment fingerprints and as defined by the associated normalised co-efficient

• Duplicate: The target is identical to query, including things like stereochemistry and isotopes. Note: this type of search used to be referred to as 'Perfect'.

• Full: The target is identical to query, allowing user to define things like stereochemistry and isotope matching. Note: this type of search used to be referred to as 'Exact'.

• Full Fragment: as for Exact, but match only has to be for one component of the target (target can contain multiple molecules). Note: this type of search used to be referred to as 'Exact fragment'.

In order to edit the structure of the queried field, double-click the structure panel to open Marvin Sketch.

Check the 'Return non-hits' check box if you want to reverse the meaning of the search e.g. find all the structures that don't match the specified structure query.

Structure search options

The different types of search operators have different sets of options. Default options are specified and are often OK, but you may want to fine tune how the search executes by specifying different options. To define the options click on the options button ( ) for a dialog that allows you to specify advanced searching settings, such as stereochemistry options and similarity search threshold (floor). Alternatively, once you have selected your search type you can right click and select 'Options'.

Duplicate search options

Duplicate search has a very limited set of options, just those that allow stereochemistry to be turned off and to enable tautomer searching. This will have the effect of return more hits which cover all stereoisomers and tautomeric forms found in the target

Full, Full fragment, Substructure and Superstructure search options

These search types have a wide range of options to control the stereochemistry, atom matching, bond matching and tautomer search options. Please refer to stereo chemistry options and absolute stereo options which describes the option definition and the effect on the query results. Options for searching generic R-groups are described on Group matching of undefined R-atoms which also explains their behaviour. Homology search options are documented in the JChem homology groups subsection for narrow translation and on the Markush structures search page for broad translation.
Halt on Error option defines what will happen if an error in the query or in CT filter query occur. By default search finishes with an error and results are not returned. If you set Halt on Error to no, structures where an error occur are omitted and all other structures which satisfy the query are returned.
See the screen shot below for these options which are the same implementation in IJC.

Similarity search options

Similarity search has quite different options to the other search types. The basic option to specify is the similarity threshold, a number between 0 and 1, where 0 is completely dissimilar and 1 is 100% identical.

In addition to the threshold you can specify a Screening Configuration to use. For normal tables containing molecules the default is Tanimoto distance, but other metrics are available and can be selected from the drop down list. The most interesting of these is Tversky, which has some additional parameters that can be specified. These are entered into the text box. For Tversky two parameters are needed:

1. Query weight: number between 0 and 1

2. Target weight: number between 0 and 1
   These are entered as comma separated values as shown in the screen shot below.

Other metric types either do not have parameters or there parameters are hard coded special cases of Tversky (e.g. DICE is Tversky with query weight and target weight both being equal to 0.5).

The Screening Configuration is specific to the type of structure table. Reaction tables have a different set of metrics which allow the type of similarity to be defined. The options are:

• Reactant Tanimoto: similarity of the reactants

• Reactant Tanimoto: similarity of the products

• Strict reaction Tanimoto

• Medium reaction Tanimoto

• Coarse reaction Tanimoto: these last three use similarity of both sides of the reaction but differ in the extent around the reacting centre that is considered (see the documentation for details)

Specifying a Chemical Terms filter

With structures you can also specify a Chemical Terms filter that can be applied to the query. To do this, enter the Chemical Terms expression into the Chemical Terms filter box located beneath the Marvin Sketch panel; alternately, click on the advanced button ( ) to open the Chemical Terms editor which will allow you to enter the expression or use one of the pre-defined favourites. This filter is applied to each result of the search and used as an additional filter for the search results. An example would be to retrieve only structures that have a logP of less that 5 by entering the expression

Not exists capability

There is a subtle difference in IJC (and RDBMS in general) between the IS NULL operation and rows which don't exist. In the case of IS NULL, the question asked is to return rows that exist but contain no data for particular field(s). This means the basic join operation does complete successfully, for a given case and the foreign key field value does exist in the child table, displaying some rows, with no apparent values for the field(s) in question. The slightly different case of the 'Not exists' query is such that is facilitates queries described like 'display only parent rows that have no child data' or indeed 'display only parent rows that have X child data'. In IJC, to be able to construct queries of this nature, one should use the available 'COUNT ' operator. A small example is given next to show this working.

For example if we have a simple parent (Molecules/Structures) and child (Assay points) in a one to many relationship based upon some primary key (parent unique identifier) and wish to obtain all molecules that are yet to be screened we can assemble a query such as below in the Query builder. You will note that the COUNT field is only available at the detail level to facilitate this sort of query, it does not make sense at the parent level to retrieve a number of rows without any other criterion defined.

Simple AND/OR query terms

You will notice initially, at the root of the query tree the AND expression is the default and this logic applies to all subsequent fields making the query read like 'all' field/operator conditions must be met. This initial choice can be modified to OR by right click on the query root and select 'change to OR'. If OR was already specified the inverse is visible 'change to AND'. By changing from AND to OR we effectively change from 'all' to 'any condition can be met'. Fields can be easily removed using right click and delete.

Complex relational AND/OR query terms

More complex queries can be constructed than the relatively simple 'any'/OR or 'all'/AND arrangements described above. For example you can construct queries which contain mixtures of nested AND/OR logic by right click on the query root node (or subtree) and then either 'add OR element' or 'add AND element'. The choice available depends upon the existing value set for that node on which you click. These new nodes are considered as sub-trees and you can see this by the Explorer style display which shows the nesting arrangement. You might wish to include fields from different entities in your subtrees to build up more complex queries. Nesting can continue several layers deep and this allows you to build up relational queries where conditions from multiple Entities are used in the query. Subtrees can be easily removed by right click and select 'DELETE subtree'.