Testing expressions
You test your expression to check that results are as expected. The following figure is an example of an expression that has been evaluated and tested.
Certain expressions may fail during runtime, even though clicking Test Expression in the Expression Builder indicated that they were valid.
This is because clicking Test Expression runs a query whereas during runtime, the expression is run as a sub-query. This issue arises partly because the rules that govern queries are slightly different from the rules that govern sub-queries.
For example, a semi-colon (;) is allowed in a query but not in a sub-query.
Testing an expression that contains an analytic function will validate the syntax without actually executing the function. Additionally, the test will only be performed on a single record.
Compose does not check the data types of columns used in an expression for compatibility. For example, if a column of type integer is used in an expression for a column of type varchar, the expression will not be executed successfully.
Testing an expression
To test an expression:
- In the Expression Builder window, build an expression as described in Building expressions.
- Click Parse Expression as described in Parsing expressions.
- View the parameters that are displayed. If your expression is not valid, an error message is displayed.
- Optionally edit the parameters name(s) as described in Editing parameter names.
-
Type values for each parameter and then click Test Expression to see the expression result.
For example, using the expression in Testing an expression, type Mike for FirstName and Smith for LastName. The result displayed is Mike Smith.
-
This step is only available for transformations created in the Edit Mappings window. When you create a transformation in the Edit Mappings window, an additional button called Show Data appears to the left of the Test Expression button. You can click this button to see how your expression translates into actual data.
For example, clicking the Show Data button for the expression UnitPrice*Quantity will open the following window.
For more information on the Edit Mappings window, see Editing column mappings in Creating and managing the data warehouse .