The Expression Builder is a window that allows you to create
SQL expressions and define certain conditions (such as select list
expressions and join conditions) in a data flow by using a combination
of operations and keywords. This window is also referred to as the
SQL Condition Builder in some contexts.
All predefined SQL functions are categorized into the following
five categories and listed in the Expression Builder window:
- MATH
- STRING
- AGGREGATION
- CAST
- DATE/TIME
Note: The functions under the AGGREGATION category are displayed
in the Expression Builder window only when the window is launched
through the following operators:
The SQL Expression Builder window is divided into four distinct
areas as follows:
- Inputs
- This area lists the table sources and the columns contained in
these table sources.
- Keywords
- Lists the valid keywords that you can use to create SQL expressions.
The valid keywords are as follows:
- AND
- BETWEEN
- EXISTS
- IN
- IS NULL
- IS NOT NULL
- LIKE
- NOT
- NOT BETWEEN
- NOT EXISTS
- NOT LIKE
- OR
- Operations
- Lists the various operators that you can use in conjunction with
the keywords to create valid SQL expressions. The valid operations
are as follows:
- +
- -
- *
- /
- %
- ||
- (
- )
- =
- <>
- >
- <
- <=
- >=
- SQL Text
- A text box for entering an SQL expression.
- Variables
- This pane displays the variable groups and the variables defined
inside each group. The variables are displayed in the pane only when
you have defined them in your data warehousing project using the Manage
Variables wizard. You can use these variables for creating the SQL
expression.
Features of the SQL Expression Builder
- Content assist
- In the SQL text area, where you enter your SQL expression, you
can type a part of a table name or a column name and press the . character
or CTRL + SPACE simultaneously. A content assist
box displays the possible <table name>.<column
name> completions. If a white space is entered before invoking
the content assist box, then all valid keyword and operations appear
in the content assist list.