WHERE Calculator Support for ANY and ALL

Reference:

The WHERE Filter calculator has ANY and ALL buttons and supports their use as predicates.


Top of page

x
Reference: WHERE Filter Calculator

To access the WHERE Filter Calculator from the data flow workspace, right-click the SQL object and select WHERE Filter.

WHERE FIlter Calculator

The Filter Calculator contains the following fields and options.

Conditions window

Displays the expression.

Relational Expression

Displays the Relational Expressions builder.

Test SQL Statement button 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 view or in a list view.

Columns

When displayed in a tree view, expanding the column name shows a list of values.

For a synonym for a data source in a table or file, a query is done to the data source to get a list of unique values. For large data sources, this could take a while.

A list of acceptable values is retrieved from the synonym if the synonym is for any of the following: database stored procedure, web service, application adapter, or any other procedure-based adapter.

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. Type 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.

SQL buttons

The following SQL buttons are available:

  • BETWEEN
  • IN
  • IS NULL
  • LIKE
  • AND
  • OR
  • ANY
  • NOT
  • ALL

WebFOCUS