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. images/download/attachments/1802414/Import_from_Database_Query_Tab.png

    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 images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/error.svg
    Similarity images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/error.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/error.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/error.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/error.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg
    Full images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/error.svg
    Duplicate images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/error.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/error.svg
    Superstructure images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/error.svg
    Full Fragment images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/check.svg images/s/en_US/8100/6512c1e2a41cdf14570641b46bd2fe3eaeb38d03/_/images/icons/emoticons/error.svg

    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.

    images/download/attachments/1802414/Date_Picker.png

    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.