Data Definition (Query) Condition Editor : inmydata

The Query Condition form guides you through building your Query Filter Conditions. There are four types of Query Filter Condition; Fixed Value, Required Value, List of Values and Expression. A Fixed Value is a value that is permanently in your Query and is used every time the query runs. A Required Value Condition is one that prompts the user to enter a value when the Query is run. A List of Values is effectively a List of Fixed Values. An expression is a value that is evaluated using an expression each time the query runs.

AND or OR...

You can choose whether each of your Conditions has an 'AND' operator or an 'OR' operator. If you said you wanted to list all Customers "where Country is equal to USA AND Country is equal to UK" then you wouldn't get any results as it would not be possible for the Customer Country to be equal to 'USA' and equal to 'UK' at the same time. However, if your conditions specified customers "where Country is equal to USA OR Country is equal to UK" then you would get a list containing all the Customers in the USA and the UK, because you would be asking for all the customers where Country = USA or UK.

clip254

FIXED VALUE CONDITION

Use the 'Operator' combo box to determine whether this will be an 'AND' condition, or an 'OR' condition.

Then use the 'Field' Combo box, to select the Field that you wish the Query Condition to be built on.

Then select the appropriate Qualifier.

Click on the 'Fixed Value' radio set button to enable the Fixed Value text box.

You can then enter the value that the selected field will be compared against when the Query is run.

clip255

REQUIRED VALUE CONDITION

A Required Value is one that the user is required to enter at run time.

As with a Fixed Value Condition select the field that you wish your condition to operate on.

Click on the 'Required Value' radio set to enable the Required Value Name and Description text boxes.

Enter a Required Value Name and description in the relevant text boxes.

The Required Value Name is the Name or Label that will be displayed when the user is prompted for a value at run time.

The Required Value Description is a description or help message that is displayed at run time to help the user understand what input is required.

By putting a tick in the 'Mandatory' check box you can determine whether the user must input a value at run time.

clip256

LISTED VALUES CONDITION

A Value List Condition is a list of Fixed Values which operates like an 'OR' statement.

Select the field and qualifier and then 'List of Values'. Type the value you wish to be included in the list, in the 'Fixed Value' text box, and then click on 'Add' to transfer it into the list.

To remove a value from the list, click on it and then on 'Delete'.

The List of values operates like an 'OR' statement i.e. if the field 'Country' is equal to USA OR equal to United Kingdom OR equal to Australia....

clip257

EXPRESSION CONDITION

An Expression Condition is a condition that is a condition that evaluates to an expression.

As with a Fixed Value Condition select the field that you wish your condition to operate on.

Click on the 'Expression' radio set to enable the Expression text.

Enter an expression, or generate using the column and function options, that will be evaluated when the query is run.

If your query is running on a Progress AppServer System, your expression should be a valid Progress Expression or Operator. If your query is running on an SQL System your expression should be a valid SQL expression.

The Check Syntax button will check that the expression is valid before leaving the screen

clip258

If the system is configured to allow it, you can change the lookup assigned to a condition using the Lookup Options button. Press the Lookup Options button to open the Lookup Details dialog box.

Note you can the Preview Data button in this screen to preview a sample of data the field contains.

Once your Condition is specified, click 'OK' to return to the Query Wizard - Define Filter Condition form where it will be displayed in the list view.

Progress Expressions

The following list details initial information of some Progress Expressions that can be used. Additional information is available at OpenEdge ABL Reference

Expression	Description	Usage
STRING	Converts a value of any data type into a character value.	STRING ( source ) source An expression of any data type that you want to convert to a character value.
INTEGER	Converts an expression of any data type to an integer value, rounding that value if necessary	INTEGER ( expression ) expression A constant, field name, variable name, or expression whose value can be of any data type.
SUBSTRING	Extracts a portion of a character string from a field or variable	SUBSTRING (source , position , length ) source A character/string expression from which you want to extract characters or bytes. position An integer value (or expression) that indicates the position of the first character you want to extract from source. length An Integer value (or expression) that indicates the number of characters you want to extract from source.
INDEX	Returns an integer that indicates the position of the target string within the source string.	INDEX ( source , target , starting ) source A Character/string expression. target A character / string expression whose position you want to locate in source. starting An integer that specifies at which left-most position in the string to start the search. If the target is not found in the source INDEX returns 0
ENTRY	Used on the left-hand side of an assignment to set the nth element to some value	ENTRY ( element , list , character ) element An integer value that corresponds to the position of a character string in a list of values. list A list of character strings separated with a character delimiter. character A delimiter you define for the list. The default is a comma. expression A constant, field name, variable name, or expression that results in a character string whose value you want to store in the nth element in a list. Progress does not pad or truncate expression.
IF	Evaluates one of two expressions, depending on the value of a specified condition	IF condition THEN expression1 ELSE expression2 condition An expression whose value is logical (TRUE or FALSE). expression1 A constant, field name, variable name, or expression. If the condition is TRUE, then the function returns this value. expression2 A constant, field name, variable name, or expression whose value is of a data type that is compatible with the data type of expression1. If the condition is FALSE or the Unknown value (?), then the function returns this value.
LENGTH	Returns the number of characters in a string.	LENGTH ( string ) string A character expression.
TRIM Also ( RTRIM / LTRIM )	Removes leading and trailing white space, or other specified characters, from a CHARACTER or LONGCHAR expression	TRIM ( expression , trim-chars ) expression An expression whose value is a character or string. Right-Time and Left Trim options are also available
NUM-ENTRIES	Returns the number of elements in a list of character strings	NUM-ENTRIES ( list [ , character ] ) list A character expression containing a list of character strings separated with a character delimiter. character A delimiter you define for the list. The default is a comma (,)
DATE	Converts a single character string, a set of month, day, and year values, an integer expression, a DATETIME expression, or a DATETIME-TZ expression into a date	DATETIME (date-exp ) DATETIME ( string ) DATETIME (month, day, year, hours, minutes) date-exp An expression whose value is a DATE. string A character expression whose value is a string containing a DATETIME. month An expression whose value is an integer from 1 to 12, inclusive. day An expression whose value is an integer from 1 to the highest valid day of the month. year An expression that evaluates to a year. hours An expression whose value is an integer from 0 to 23, inclusive. minutes An expression whose value is an integer from 0 to 59, inclusive.
DATETIME	Converts date and time values, or a character string, into a DATETIME	DATETIME (date-exp ) DATETIME ( string ) DATETIME (month, day, year, hours, minutes ) date-exp An expression whose value is a DATE. string A character expression whose value is a string containing a datetime. month An expression whose value is an integer from 1 to 12, inclusive. day An expression whose value is an integer from 1 to the highest valid day of the month. year An expression that evaluates to a year. hours An expression whose value is an integer from 0 to 23, inclusive. minutes An expression whose value is an integer from 0 to 59, inclusive.
TODAY	Returns the current system date	TODAY
MONTH	Evaluates a date expression and returns a month integer value from 1 to 12, inclusive	MONTH ( date ) MONTH ( datetime-expression ) date A date expression where you want a month value. datetime-expression An expression that evaluates to a datetime.
YEAR	Evaluates a date expression and returns the year value of that date including the century	YEAR ( date ) YEAR ( datetime-expression ) date A date expression for which you want to determine the year. datetime-expression An expression that evaluates to a datetime

Progress Operators

The following list details initial information of some Progress Operators that can be used. Additional information is available at OpenEdge ABL Reference

Operator	Description	Usage
+	Adds two numeric expressions	expression + expression expression Any expression resulting in a numeric value
+	Joins two character string or expressions	expression + expression expression Any expression resulting in a character or string value
+	Adds a number days to a date	date + days date Any expression that results in a date days Any expression with an integer value
-	Subtracts a numeric expression from another	expression - expression expression Any expression resulting in a numeric value
-	Subtracts a number of days, or a date, from a date	date - days date - date date Any expression that results in a date days Any expression with an integer value
*	Multiplies two numeric expressions	expression * expression expression Any expression resulting in a numeric value
/	Divides one numeric expression by another numeric expression	expression / expression expression Any expression resulting in a numeric value
AND	Returns TRUE if each logical expression is true	expression AND expression expression Any expression resulting in a logical value (TRUE or FALSE)
EQ =	Returns a True value if two expressions are equal	expression EQ expression expression = expression expression A field name or expression. The data types on each side must be the same
GE >=	Returns a True value if the first expression is greater or equal to the second	expression GE expression expression >= expression expression A field name or expression. The data types on each side must be the same
GT >	Returns a True value if the first expression is greater than the second	expression GT expression expression > expression expression A field name or expression. The data types on each side must be the same
LE <=	Returns a True value if the first expression is less then or equal to the second	expression LE expression expression <= expression expression A field name or expression. The data types on each side must be the same
LT <	Returns a True value if the first expression is less than the second	expression LT expression expression < expression expression A field name or expression. The data types on each side must be the same
NE <>	Returns a TRUE value if two expression are not equal	expression NE expression expression <> expression expression A field name or expression. The data types on each side must be the same
NOT	Returns TRUE if the expression is false and FALSE if the expression is true	NOT expression expression Any expression resulting in a logical value (TRUE or FALSE)
OR	Returns a TRUE value if either of two logical expression are TRUE	expression OR expression expression Any expression resulting in a logical value (TRUE or FALSE)

Lookup Options

The Lookups Options screen allows you to define which lookup is used for any conditions on a specific field in your query.

clip259

To specify the lookup, select the lookup in the drop down list and press OK. If you do not want a lookup applied to this field, select "<No lookup selected>". If you have the relevant permissions, you can create a new lookup by pressing the Add Lookups button to open the Lookup Wizard.

Once you have selected your lookup options, press OK to continue.

Data Definition (Query) Condition Editor Print

Related Articles