MAINTAIN WHERE CLAUSES#

The Maintain Where Clauses (IMWC) form allows you to create and edit Where Clauses within the system. Where clauses are used to conditionally select data from a table. They may be used to filter data on forms, approval processes, LOV’s and people lists.

The definition data for the Maintain Where Clauses screen is stored in the P2K_AM_WHERE_CLAUSES and P2K_AM_CONDITIONS tables.

Data Source

The data source is the table location in which the information being filtered into the where statement is derived from. The data source is a mandatory 10-character alphanumeric field that the user must either manually enter or use the LOV to choose their option.

Where Clause

This field identifies the Where Clause.

Usage

This field will indicate if the Where Clause is Pre-loaded (system provided), User Defined or Obsolete. All Where Clauses created by clients should have a usage of ‘User Defined’.

Description

This field provides a brief description outlining the intention of the ‘Where Clause’. Although it is not a mandatory field, it is recommended to always provide a brief description for the purpose of clarity

Type

This field indicates whether the criteria have been predefined or if the criteria are defined on an ‘as required’ basis. There are two options available, Predefined and Ad Hoc. Users should use the type Ad Hoc.

Predefined

Some 'pre-loaded' where clauses contain predefined java logic, these predefined logic codes are specified in this field. This field is not used with user-defined where clauses.

Column Name

This field allows you to define the filters to be used with the Where Clause. You may use more than one filter on a where clause. If you do use multiple filter statements, they are joined by ‘and’ in the statement.

e.g. You can use multiple columns to return a Positions LOV that will be filtered first by department, next by active status, and finally by date.

Operator

This field allows you to provide an SQL statement to further filter the information.

e.g. Equals, Formatted Equals, Greater Than, In, Is Not Null, Is Null, Less Than,…
By selecting Formatted Equals in the Operator, the user is able to specify a date format in the Format Symbols field.

Value

This field indicates what data the column name is to be compared with. For example this could be the as of date, lexicon values, numbers or specific data within the database such as a department code.

The Column, Operator and Value fields are used together to create a precise filter for information. For example, if you wished to only see the LOV positions that are in the Finance department, you would insert the following information in those fields:

• Column = ID.DPS_ID
• Operator = equal to
• Value = Finance

Description

This field will provide a full description of how the Where Clause operates.

Physical Column Name

This field indicates the name of the column in the table as defined in the database.

Physical Condition

This field indicates the predefined condition implemented within the Java code itself.

Test Where Clause

This button allows you to test the Where Clause to see if it returns the correct information in a timely manner.