Use the Expression Builder

There are two ways to add an expression, with different advantages:

• Use the Expression Builder user interface. This more cumbersome process makes the transformations, validation checks, and custom listings that use it easier to copy and map in the new study.

• Enter code as free text directly in the Expression Text field. This is a simpler process for a programmer but results in a less easily reusable transformation, validation check, or custom listing.

  Important:

  • If you reference a static package or function in free text, you must select it in the Selected Packages tab.

  • In free text, use just the column name, not the table.column format, unless you need to use an alias, as in a self-join. In that case the alias.column format is required.

To use the Expression Builder:

1. In the Expression Criteria pane, select the following as needed to build the expression from left to right.

   • Add Group to add the parentheses () that surround a phrase in an expression or group smaller units of logic.

   • Add Item to add a unit of logic smaller than a group.

2. To add a phrase within a group, click the parentheses ().

   To add a phrase outside a group, click Expression.

3. To add an item, in the Expression Item pane select either Column, Function (for functions written by your company), or Standard Function (for Oracle SQL functions).

   To create an expression using column values:

   1. For Item Type, select Column.

   2. Click the Select Column icon.

      In the Select Column window, you can filter above any of the attribute columns to find the table column you want. Select a column and click OK.

   3. If needed, select an operator from the list.

   4. If needed, enter a data value. The system encloses the value you enter in single quotes.

   5. If needed, select a conjunction from the list.

      Tip:

      If you select a conjunction within a group, it appears within the group, at the end. If you need a conjunction outside the group, click Expression above, then select the conjunction.

   6. Click Add. The system generates the SQL expression and displays it in the Expression Text pane.

      Note:

      You can edit the generated code in the Expression Text pane, but if you do, you cannot continue to build the expression in the user interface. See "To make a correction in the Expression Builder" to continue in the user interface.

      Click Validate to check the generated code.

   To use a function in your library:

   1. For Item Type, select Function. The Select Function window appears, displaying a list of Oracle functions.

   2. Select a function and click OK.

   To use a standard SQL function:

   1. For Item Type, select Standard Function.

   2. Click the Select Standard Function icon. A search window appears. To filter, enter all or part of the name in the field above. You can use the wildcard %.

   3. Select a function and click OK.

4. Define additional groups and items to complete the expression as necessary.

5. Click Save.

6. Click Validate. The system validates the code and displays any errors or warnings.

To make a correction in the Expression Builder:

1. Select the faulty item in the Expression Criteria pane. An Update button appears in the Expression Item pane.

2. Make your changes in the Expression Item pane and click Update.

For more information, see:

• Pass data as input parameter values

• Pass constant values

• Develop a library of SQL functions

• Pass data as input parameter values
  
• Pass constant values