Record Selection in LANSA Client

Date:Archived
Product/Release:LANSA Client
Abstract:Field conditions help you to filter data so that you only get the records you want
Submitted By:LANSA Technical Support

Field Conditions allow data to be filtered from the corporate AS/400 database to specific requirements. Only the required data is retrieved from the AS/400 to the PC.

Field Conditions

Field Conditions allow data to be filtered from the corporate AS/400 database to specific requirements. Only the required data is retrieved from the AS/400 to the PC.

For example, to filter data to retrieve only sales within a certain period (between certain dates) or sales within a certain region. Using 'AND'  specify that both the period and region conditions have to be met. Using 'OR' specify that the period condition or the region condition has to be met. Conditions can also be grouped which belong together or negate a condition by using 'NOT'.

LANSA Client allows conditions to be specified for any of the fields in a query.

For example, to select all Sales records with a Sales value greater than 1000. the following condition would be specified:

select a field (used in the query)     Sales value
    select an operator                 greater than
    enter a value                      1000

The Operators that can be used in search conditions are:

Equal to
    Less than
    Less than or Equal to
    Greater than
    Greater than or Equal to
    Is in (Contains)
    Is like

Fields that have a condition attached are displayed with a '*' next to their description in the query definition.

A query can be defined that should prompt for a condition value at query time when a condition needs to be flexible. For example, sometimes it may be necessary to select 'Sales greater than 1000' and sometimes 'Sales greater than 5000'. To do this tick the Prompt checkbox and enter a prompt text.

Grouping conditions that belong together:

Before:

Conditions

After:

Conditions

The conditions dialog box allows conditions to be grouped which logically belong together.

Imagine the following example where all exceptionally high and low sales records of the year 1995 need to be displayed. Exceptionally high sales is when year-to-date sales is above 500,000. Exceptionally low sales is when year-to-date sales is below 10,000.

Example before grouping: Year equal to 1995
                AND
YTD-Sales greater than 500,000
                OR
YTD-Sales less than 10,000

This would not give the desired results, because the 'OR YTD-Sales less than 10,000' condition might also be valid for 1994 or other years.

Example after grouping: Year equal to 1995
                AND (
YTD-Sales greater than 500,000
                OR
YTD-Sales less than 10,000)

This will work.

The condition 'Year equal to 1995' must be met, and after that one of the other conditions within the group must be met.

Note: If an 'OR' condition is used in a Join query, that condition will have to be grouped, so the entire condition between brackets will use 'AND'.