Where you
use DataWindow expressions
A DataWindow expression is a combination of data, operators, and
functions that, when evaluated, results in a value. An expression can
include column names, operators, DataWindow expression functions, and
constants such as numbers and text strings.
In painters
DataWindow expressions are associated with DataWindow objects and
reports. You specify them in the DataWindow painter. You can also specify
expressions in the Database painter, although these expressions have a
slightly different format and are used only in validation rules.
For information about DataWindow expression functions that you can
use in expressions, see Using
DataWindow expression functions, or look up the function you want
in online help.
In painters, you use expressions in these ways:
|
In this painter |
Expressions are used in |
|---|---|
|
DataWindow painter |
Computed fields Conditional expressions Validation Filters Sorting Series Columns, rows, and values in |
|
Database painter |
Validation rules |
Other types of expressions you use
You also use expressions in Quick Select, SQL Select, and the
Query painter to specify selection criteria, and in SQL Select and the
Query painter to create computed columns. In these painters you are
using SQL operators and DBMS-specific functions, not DataWindow
expression operators and functions, to create expressions.
You can access and change the value of DataWindow data and
properties in code. The format for expressions you specify in code is
different from the same expression specified in the painter. These
differences are described in Accessing Data in Code and Accessing DataWindow Object Properties in
Code
Some of the specific places where you use expressions are described
here.
In computed fields
Expressions for computed fields can evaluate to any value. The
datatype of the expression becomes the datatype of the computed
field:
|
Expression |
Description |
|---|---|
|
Today ( ) |
Displays the date using the Today |
|
Salary/12 |
Computes the monthly salary |
|
Sum (Salary for group 1) |
Computes the salary for the first group using the |
|
Price*Quantity |
Computes the total cost |
Expressions for graphs and crosstabs
You can use similar expressions for series and values in graphs
and for columns, rows, and values in crosstabs.
In filters
Filter expressions are boolean expressions that must evaluate to
true or false:
|
Expression |
Description |
|---|---|
|
Academics = “*****” AND Cost = “$$$” |
Displays data only for colleges with both a 5-star |
|
Emp_sal < 50000 |
Displays data for employees with salaries less than |
|
Salary > 50000 AND Dept_id BETWEEN 400 AND |
Displays data for employees in departments 400, 500, |
|
Month(Bdate) = 9 OR Month(Bdate) = 2 |
Displays data for people with birth dates in |
|
Match ( Lname, “[ ^ABC ]” ) |
Displays data for people whose last name begins with |
In validation rules for table
columns
Validation rules are boolean expressions that compare column data
with values and that use relational and logical operators. When the
validation rule evaluates to false, the data in the column is
rejected.
In the DataWindow painter
When you specify a validation rule in the DataWindow painter, you
should validate the newly entered value. To refer to the newly entered
value, use the GetText function. Because GetText returns a string, you
also need a data conversion function (such as Integer or Real) if you
compare the value to other types of data.
If you include the column name in the expression, you get the value
that already exists for the column instead of the newly entered value that
needs validating.
In the Database painter
When you specify the validation rule in the Database painter, you
are defining a general rule that can be applied to any column. Use
@placeholder to stand for the newly entered value. The name you use for
@placeholder is irrelevant. You can assign the rule to any column that has
a datatype appropriate for the comparison.
When you define a DataWindow object, a validation rule assigned to a
column is brought into the DataWindow object and converted to DataWindow
object syntax. @placeholder is converted to GetText and the appropriate
datatype conversion function.
Other columns in the rule
You can refer to values in other columns for the current row by
specifying their names in the validation rule:
|
Expression in Database painter |
Expression in DataWindow painter |
Description |
|---|---|---|
|
@column >= 10000 |
Integer(GetText())>= 10000 |
If a user enters a salary below $10,000, an error |
|
@column IN (100, 200, 300) |
Integer(GetText()) IN (100, 200, 300) |
If a user does not enter a department ID of 100, 200, |
|
@salary > 0 |
Long(GetText()) > 0 |
If a user does not enter a positive number, an error |
|
Match(@disc_price, “^[0-9]+$”) and @disc_price < |
Match(GetText( ), “^[0-9]+$”) and |
If a user enters any characters other than digits, or |