Specifying Filters in a Data Flow
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.
x
Procedure: How to Add a Filter to a Data Flow
-
Right-click
the SQL object in the data flow workspace and select Filter.
The Filter Calculator opens.
-
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.
-
If you
create multiple expressions that must all be satisfied, separate them
with AND.
-
If you
create multiple expressions where any of them must all be satisfied,
separate them with OR.
-
Click OK.
x
Procedure: How to Create a Data Flow Filter That Uses a Date/Time Constant Value
From
the data flow workspace:
-
Right-click
the SQL object and select Filter.
The Filter Calculator opens.
-
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.
-
Use
the calculator buttons to select a relationship.
-
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.
-
Click OK.
x
Procedure: How to Create a Data Flow Filter That Uses a Constant Value
From
the data flow workspace:
-
Right-click
the SQL object and select Filter.
The Filter Calculator opens.
-
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.
-
Use
the calculator buttons to select a relationship.
-
Type
in the constant value. Character and date values must be enclosed in
single quotation marks.
-
Click OK.
x
Procedure: How to Create a Data Flow Filter That Uses a Variable
From
the data flow workspace:
-
Right-click
the SQL object and select Filter.
The Filter Calculator opens.
-
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.
-
Use
the calculator buttons to select a relationship.
-
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.
-
Click OK.
For
more information about variables, see Using Variables in a Flow.
x
Procedure: How to Create a Filter That Uses a Sub-Select
A
filter that uses a sub-select can be typed in to the filter calculator.
For example:
-
Select rows
where the value of PLANT is found in DMSALE.
T1.PLANT IN (SELECT T2.PLANT FROM DMSALE T2 )
-
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 )
x
Reference: Filter Calculator
The Filter Calculator
is available from the data flow workspace by right-clicking the SQL
object and selecting Filter.
The
Filter Calculator contains the following fields/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
-
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 or in a grid.
-
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.