Defining a WHERE Clause for a filter field

To set a filter for a report or selector, you must configure a WHERE clause for the property you want the user to define in the filter field. The WHERE clause must be defined as follows:

• When defining the value for the condition, enter @ followed by a technical name of the filter field.

  An example WHERE clause for the generation of a filter field in a native SQL query:

  • WHERE APPLICATION.NAME LIKE @ApplicationName

  and in Alfabet query language:

  • WHERE Application.Name LIKE @ApplicationName
• Select a condition operator that requires specification of a value. For example, IS NULL cannot be used for a filter because it does not require the specification of a value whereas, for example, the condition LIKE allow the specification of filter fields.

Depending on the data type of the property and condition that you have configured, different type of filter fields may be defined. In general, the following rules apply to all filter definitions in WHERE clauses:

• Queries with filter field name parameters can be defined for all queries in a report, including color rules and indicator rules.
• A filter field value can be used in multiple WHERE clauses by defining the same parameter name.
• When a filter field is not filled, the query processing mechanisms of the Alfabet components delete the condition with the filter parameter from the WHERE statement before executing the query. For example the following WHERE statement of a native SQL query
  • • WHERE APPLICATION.NAME CONTAINS @PARAM
    • AND APPLICATION.STATUS = ’Plan’

  is shortened to the following statement before executing the query if the filter field with the name @PARAM is not filled:

  • • WHERE APPLICATION.STATUS = ’Plan’
• In standard Alfabet views and configured reports based on Alfabet queries entries in filter fields are interpreted case insensitive. Filter values passed on to a native SQL query are interpreted case sensitive. To enhance usability it is recommended that the WHERE condition of the native SQL query that contains the parameter substituted by the filter field value is designed to enable a case insensitive comparison. This can be achieved by using the UPPER() or LOWER() function:
  • WHERE UPPER[APPLICATION.NAME LIKE UPPER(@ApplicationName)
• The search behaviour in edit fields is controlled by the setting of the AutoWildCard attribute of the XML object SearchManager . When AutowildCard is set to true , a wildcard is added automatically to search strings entered by the user. Therefore, you cannot use a = condition for the definition of edit fields because the condition does not allow wildcards in the argument. Use LIKE instead. It is also recommended to inform users with a static text in the filter section whether wildcards are automatically added to strings.