You are here:

Using Scalar Functions to Lookup Links

Scalar functions return a single value that is based on the input value. For example, the scalar function UCASE takes the input value and returns that value in UPPER CASE.

To use scalar functions to lookup links

Double-click a link in the Lookup Criteria tab.
Use the Name drop-down list to select a scalar function. When you select a function from the list, the syntax and a brief description of this function appears. In addition, you can:
1. Use Apply to for specifying whether to apply this function to the target Step, the Source, or Both.
2. Select an Operator for the lookup criteria.
3. If desired, click Edit Lookup Formula. The Edit Formula dialog box appears, where you can add additional processing to the lookup formula.
The Where pane at the bottom of the dialog box displays actual function syntax in-progress.

Note: If the selected function Name requires a parameter, a corresponding data entry field displays for you to enter the desired parameter value.

Available scalar functions and operators are determined by the database engines and/or the database driver you are using. Only those functions and operators available for the both the source and target configuration are displayed. If scalar functions are not supported the Name drop-down list will be empty.

Scalar Function Examples

Insight uses scalar functions from the source and target data sources. Only the scalar functions supported by both the source and target are available.

Depending on your data sources, operators may include EQUALS, LIKE, IN, CONTAINS, STARTS WITH, and so on.

For example:

Where LEFT(company_name,8) like LEFT(PROPER(comp_name),8)

Where SOUNDEX(last_name) = SOUNDEX(PARSENAME(full_name,"LE"))