Specifying Filters in a Data Flow

How to:

Reference:

You can create a filter to control the data included in your data flow. A filter creates WHERE criteria in the SQL of your flow. You can filter your data with a constant value or a variable.

Note: When a filter is saved, it is parsed and any superfluous parentheses are removed.


Top of page

x
Procedure: How to Add a Filter to a Data Flow
  1. Right-click the SQL object in the data flow workspace and select WHERE Filter.

    The WHERE Filter Calculator opens.

  2. Enter an expression in the Conditions window using the Columns/Variables and Functions tabs and the calculator buttons. For information about using functions, see Using an SQL Function. For information about using variables, see Using Variables in a Flow.
  3. If you create multiple expressions that must all be satisfied, separate them with AND.
  4. If you create multiple expressions where any of the expressions must all be satisfied, separate them with OR.
  5. Click OK.

Top of page

x
Procedure: How to Create a Data Flow Filter That Uses a Date/Time Constant Value

From the data flow workspace:

  1. Right-click the SQL object and select WHERE Filter.

    The WHERE Filter Calculator opens.

    Note: If the Relational Expression tab is not selected, click on it to select it.

  2. Double-click a column from the Columns/Variables tab. You can also type the column name in the Conditions field.

    The column is added to the Conditions field.

  3. Use the calculator buttons to select a relationship.
  4. Double-click the constant you want from the Date and Time Constants folder of the Columns/Variables tab, and replace the sample constant with the desired date/time. Character and date values must be enclosed in single quotation marks (‘).

    WHERE Filter Calculator

  5. Click OK.

Top of page

x
Procedure: How to Create a Data Flow Filter That Uses a Constant Value

From the data flow workspace:

  1. Right-click the SQL object and select WHERE Filter.

    The WHERE Filter Calculator opens.

    Note: If the Relational Expression tab is not selected, click on it to select it.

  2. Double-click a column from the Columns/Variables tab. You can also type the column name in the Conditions field.

    The column is added to the Conditions field.

  3. Use the calculator buttons to select a relationship.
  4. Type in the constant value. Character and date values must be enclosed in single quotation marks (‘).

    Constant value

  5. Click OK.

Top of page

x
Procedure: How to Create a Data Flow Filter That Uses a Variable

From the data flow workspace:

  1. Right-click the SQL object and select WHERE Filter.

    The WHERE Filter Calculator opens.

  2. Double-click a column from the Columns/Variables tab. You can also type the column name in the Conditions field.

    The column is added to the Conditions field.

  3. Use the calculator buttons to select a relationship.
  4. Double-click the variable you want from the DM or System Variables folder on the Columns/Variables tab. You can also type in character-valued or date-valued variables, which must be enclosed in single quotation marks (‘).

    Variable

  5. Click OK.

For more information about variables, see Using Variables in a Flow.


Top of page

x
Procedure: How to Create a Filter That Uses a Sub-Select

A filter that uses a sub-select can be typed into the filter calculator or created using the Function Assist.

  1. Drag ibisamp/dmord into a data flow.
  2. Right-click the SQL object and select WHERE Filter.

    The WHERE Filter Calculator opens.

    Note: If the Conditions tab is not selected, click on it to select it.

  3. Click the Functions tab, and expand SQL Operators.
  4. Select the IN ... SELECT function.
  5. A prototype condition is filled in:
    (test_exp1, test_exp2) IN (SELECT exp1, exp2 FROM mfd WHERE condition)
  6. Replace the text with the desired condition. For example, select rows where the value of PLANT is found in DMSALE.
    T1.PLANT IN (SELECT T2.PLANT FROM  DMSALE T2 )

    You can also select rows where the combination of values of PLANT and ORDER_NUM are found in DMSALE.

    (T1.ORDER_NUM  ,T1.PLANT ) IN (SELECT T2.ORDER_NUM , T2.PLANT FROM DMSALE T2 )

Top of page

x
Reference: WHERE Filter Calculator

To access the WHERE Filter Calculator from the data flow workspace, right-click the SQL object and select WHERE Filter.

WHERE FIlter Calculator

The Filter Calculator contains the following fields and options. For a full description of the operators available, see Join Calculator.

Conditions window

Displays the expression.

Relational Expression

Displays the Relational Expressions builder.

Test SQL Statement button Test SQL Statement

Tests the SQL statement and displays the result.

Note: The test button will be grayed out if you have not selected any columns.

Columns/Variables tab

Displays available columns and variables in a hierarchical tree view or in a list view.

Columns

When displayed in a tree view, expanding the column name shows a list of values.

For a synonym for a data source in a table or file, a query is done to the data source to get a list of unique values. For large data sources, this could take a while.

A list of acceptable values is retrieved from the synonym if the synonym is for any of the following: database stored procedure, web service, application adapter, any other procedure based adapter.

Functions tab

Displays a list of SQL functions that are available for your transformations.

Function Assist button

Allows you to specify parameters for the function through a dialog box when creating or editing a transformation.

Calculator buttons

Insert numbers and operators.

( )

Adds parentheses.

''

Inserts two single quotation marks. Enter alphanumeric test values between these.

a->A

Converts selected text to uppercase.

A->a

Converts selected text to lowercase.

Date

Opens the Date Editor window, which lets you use the current date or specify a date from the calendar.

Datetime

Opens the Date Editor window, which lets you use the current date and time or specify a date and time from the calendar.

SQL buttons

The following SQL buttons are available:

  • BETWEEN
  • IN
  • IS NULL
  • LIKE
  • AND
  • OR
  • ANY
  • NOT
  • ALL

iWay Software