Using The Query Builder

You can build a custom query to select only those fields or records you need from your source data.

To build a custom query

  1. In the Workbench, click Source > Configure. The Configure Source dialog box appears.
  2. Select Custom Query.
  3. Do one of the following:
    • Click Open Query.
    • Define a query on the tabs:  
      • Data Objects Tab
        1. Select a primary data object from the list of available objects.
        2. Optionally, select a child data object. Select the Required box to include only primary object records with at least one matching child object.
        3. Optionally, you can join other objects in the query, select the appropriate object from the Child Data Object or Parent Data Object lists. The field in parenthesis after the object name in the list is the field used to join the objects.
        4. Optionally, select up to three parent data objects. Check the Required box to include only primary object records with at least one matching parent object.

          5. Note: When using a custom query with parent and child objects where you are filtering on a child object field, the Required (inner join) check box should be checked.

          With the check box enabled, TIBCO Scribe® Insight returns only those parent records that have a child with field data that matches the filter criteria. With the check box disabled, TIBCO Scribe® Insight returns all parent records whether they have a child that meets the filter criteria or not. However, only those children with matching filter criteria are returned.

      • Fields Tab
        1. Select a data object and then select a field for that data object.
        2. Click Add to add additional fields.
        3. Enter an alias to distinguish between fields with the same name.
        4. Select a row, by clicking the row heading box to the left of the row, and use the up and down arrows to move the row up or down in the list.
        5. Click Add All Fields. Each field displays in a separate row.
      • Sort Tab
        1. Specify how you want to sort the query results. Select a field and then select how you want the field sorted.
        2. Click Add to add additional fields to sort by.
        3. Select a row and use the up and down arrows to move the row up or down in the list.

        Queries always sort by the primary object fields first, then by the child object fields. You cannot sort by parent object fields.

      • Filters Tab
        1. Click Add Condition or Edit Condition. The Query Condition dialog box appears, where you can add or edit the conditions used to filter the query results.

          Note: Always use double quotes around character data in the query builder.

        2. Link conditions by adding AND or OR operators.
        3. Use the arrow buttons to rearrange the filter conditions.

          For example, select the account data object, the active field, the = operator, and type Y in the text box. The result set for this query is all active accounts.

        4. Optionally, click Variables. The Variables dialog box appears.
        5. Either select from the available system variables or user variables, or create new user variables.

          For example, use the system variable :LastRunDateTime with a source-modified date/time to select all records that have changed since the last time the process was run. See Adapter Limitations for more information about performing lookups on DateTime fields.

        6. Optionally, use the Function Browser to find an available function. Click Insert Function in Expression to add the function.
        7. Click OK.
      • Query Test Tab
        1. Review the XML code generated by the query builder for the objects, fields, and filters you have defined.
        2. Click Test/Requery to test the syntax and execution time of your query.
      • Actual Query Tab
        • This tab only becomes available after you run a test query.
        • View the values substituted for the variables used in the query.

        Not all features are supported by all adapters. If an adapter does not support a function, such as sorting or filtering, the tab is unavailable.

  4. In the Data Objects tab, click Save Query to save your query.

See also

Connecting Via A DTS File

Connecting Via A Query Publisher