Blue icon showing light filtering through a prism.

Filter In-DB Tool

Version:
Current
Last modified: October 21, 2019

The Filter In-DB tool filters records with a basic filter or with a custom expression using the native language of the database, such as SQL. Use the Filter In-DB tool to query records and return records that meet the specified criteria.

In-Database enables blending and analysis against large sets of data without moving the data out of a database and can provide significant performance improvements over traditional analysis methods. For more about the In-Database tool category, see In-Database Overview.

While most In-DB tools do not require SQL commands, this tool requires SQL for more advanced processing.

Tool Configuration

Configure the Tool

  1. Select the appropriate filter type.
    • Basic Filter: Use the basic filter to construct a simple query on a single field in the database.
      1. Use the drop-down to select the column to filter on.
      2. Use the drop-down to select an operator to use.

        Operator

        Meaning

        =

        Equals

        <>

        Does not equal

        >

        Is greater than

        >=

        Is greater than or equal to

        <

        Is less than

        <=

        Is less than or equal to

        IS NULL

        Is a missing or unknown value

        IS NOT NULL

        Is not a missing or unknown value

        LIKE

        Is similar to a specified pattern in a column

      3. Type in the value to complete the query.
    • Custom Filter: The Custom Filter acts as a SQL WHERE query. Use the custom filter to construct a more complex expression or to query from multiple fields in the database.
      1. Use Insert Fields to pick from available fields to construct your expression.
      2. Type the rest of the query in the box using the native language of the database.

    If a query is constructed using the Basic Filter, a read-only query displays in the Custom Filter area. If the Custom Filter option is then selected, the query becomes editable.

  2. Validate expression here at runtime: An option that sends a query to the database to report errors contained in the expression in the results window of this tool.

    By default, errors are reported in the results window of downstream tools.

View the Output

T anchor: Records that meet the specified criteria.

F anchor: Records that do not meet the specified criteria.

Was This Helpful?

Need something else? Visit the Alteryx Community or contact support.