Import from Database-Query Tab

The Query tab consists of three parts; the query structure box on the left, the search options on the right, and the query criteria box at the bottom of the window.

Query Structure

The following methods can be used to specify the query structure:

• Dragging a file that contains the query structure to the picture box.

• Double-clicking the box and drawing the structure for the query. It is also possible to import the file or open one in the structure editor.

• Pasting a structure from the clipboard by right-clicking on the box and using the Paste context menu item or by using the Ctrl+V shortcut.

Search Options

Basic Search Options

When opening the Import from Database dialog and connecting to a database table, this group of options opens by default. The group lists the most frequently used search options.

Depending on the selected search type, different options are presented in this group (see the table below). Options that are not displayed when a specific type is selected, can be found at their original place, for example, in Chemistry Search Options or General Search Options.

	Charge matching	Double Bond Stereo Check	Include Tautomers	Maximum Hits	Return Non-Hits	Stereochemistry	Similarity Threshold
Substructure
Similarity
Full
Duplicate
Superstructure
Full Fragment

Chemistry Search Options

The following chemistry search options are available:

• Absolute Stereo (Default value: Table Option)

• Dissimilarity Metric (Default value: Default)

• Exact Query Atom Matching (Default value: False)

• Homology Broad Translation Mode (Default value: None)

• Homology Narrow Translation Mode (Default value: None)

• Hydrogen Count Matching (Default value: Auto)

• Isotope Matching (Default value: Default)

• Radical Matching (Default value: Default)

• Similarity Threshold (Default value: 0.5)

• Target Homology Matching Mode (Default value: All)

• Vague Bond Level (Default value: Level Half)

For more information, see Query Guide.

General Search Options

Chemical Terms Filter

Specify the chemical terms filter by using different expressions described in Chemical Terms Introduction.

Maximum Time

The maximum time interval of running the search can be specified here.

Query Structure

The query structure applied for the search when importing can be specified here.

Structure Search Method

Select from JChem Cartridge and JChem Base search methods. In the case of views, it is not possible to recognize the indexed columns from a schema, therefore all possible search methods are displayed. You have to select manually both the search method and the column with structure.

Target Column

Structure columns available in the connected database table are listed here and can be selected from the drop-down list.

In the case of views, the users can select which one of the listed columns will be handled as a structure-column. The following column names will be selected by default in the following order:

• CD_STRUCTURE

• STRUCTURE

• CD_SMILES

• SMILES

If none of these columns are available the first will be set as target column.

Hit Coloring

The default values are as follows:

• Hit Alignment Mode: Off

• Hit Color: Blue

• Hit Coloring: TRUE

• Non Hit Color: Gray

Other query options can be specified in the property grid.

Hit Alignment Mode

For a detailed description, see Query Guide.

Hit Color

Specify the color of hit structures with this option.

Hit Coloring

By setting this option to TRUE, you can display hit structures with the selected colors.

Hit coloring and hit alignment can be applied separately by setting the Hit Alignment Mode to Rotate or Partial, and Hit Coloring option to TRUE. This way you can display hit structures rotated as the query structure without coloring them.

Non Hit Color

Specify the color of non-hit structures with this option.

Query Criteria

It is possible to specify additional query criteria for import on the Query tab. Columns could be filtered based on the following criteria:

Numeric columns	Text-based columns
=, <>, <, <=, >, >=, IS NULL, IS NOT NULL	=, <>, <, <=, >, >=, IS NULL, IS NOT NULL, Like, Starts with, Ends with, Contains

{info} The decimal separator must be specified as . regardless of cultural settings.

Query Criteria for Related Tables

If the selected table contains related tables, it is possible to specify query criteria on those columns as well.

Related tables are tables that have foreign key-relation between them defined in Oracle. This information is retrieved from the Oracle schema.

How to Specify a Query Criterion

1. Select AND or OR from the AND/OR column. Only one logical operator can be selected for all the criteria.

2. Select the database table (all the related tables are listed here, together with the original table).

3. Select the database column (only columns for the previously selected table are listed here).

4. Select the operator.

5. Enter a value.

Examples

Find all structures where the molecular weight is less than 250, and the formula begins with C10. JChem for Excel automatically recognizes if the selected column is a date type and the date picker becomes available.

By using the date picker application, you can easily specify the desired date. The application uses the dd.MM.yyyy date format.

It is also possible to enter the date manually in other popular date formats. The following formats are supported:

• dd.MM.yyyy

• dd-MM-yyyy

• yyyy-MM-dd

• yyyy.MM.dd

• yyyy/MM/dd

• MM/dd/yyyy

If an unsupported format is entered, a red exclamation mark appears at the end of the Value field with a Should be a DateTime tooltip.

Deleting a Query Criterion

1. Select the row by clicking on the row header.

2. Press the Delete button on the keyboard.

3. Clicking the Next button switches to the Columns tab.