DataWindow operators and expressions

Table 247. 

DataWindow operators

Supported

Arithmetic operators: +, -, *, /, ( ), ^

Relational operators: =, >, <, <>, >=, <=, BETWEEN...AND, IN, LIKE, and any of these operators in combination with NOT

Logical operators for all data types: NOT, AND, OR

Concatenation for string data types: +

Unsupported

Subtraction (-). The setting of the DashesInIdentifiers property is ignored. For example, "A-B" always means subtract B from A.

Operator precedence in DataWindow expressions

Difference from PowerBuilder

The precedence of "AND" is higher than that of "OR" (in PowerBuilder, "AND" and "OR" have the same precedence).

The operators >, <, <=, >= are of higher precedence than = and <> (in PowerBuilder, the operators >, <, <=, >=, =, <> are of the same precedence).

Note: You can use parentheses to make sure the Web application has correct precedence effect as in the PowerBuilder application.

Original expression: a OR b AND c

Modified expression: (a OR b) AND c

Supported & Unsupported expressions

Supported

Conditional expressions for property values (such as Visible, X, Y, Width, Height, BackgroundColor, TextColor)

Expressions for computed fields

Validation rules

Filter and sort criteria

Note: The information on the limitations for these types of expressions is provided in the Specific Requirements for Using Expressions.

Unsupported

Series and values in graphs

Columns, rows, and values in crosstabs

Functions used in DataWindow expressions

Supported

Abs

Acos

Asc

Asin

Atan

Avg

Bitmap

Ceiling

Describe

Char

Case

Cos

Count

CrosstabAvg

CrosstabCount

CrosstabMax

CrosstabMin

CrosstabSum

CumulativePercent

CumulativeSum

CurrentRow

Date

DateTime

Day

DayName

DayNumber

DaysAfter

Exp

Fact

Fill

First

GetText

GetRow

Hour

If

Int

Integer

IsDate

IsNull

IsNumber

IsRowModified

IsRowNew

IsSelected

IsTime

Last

Large

LastPos

Left

LeftTrim

Len

Log

LogTen

Long

LookUpDisplay

Lower

Match

Max

Median

Mid

Min

Minute

Mod

Month

Now

Number

Page

PageCount

Percent

Pi

Pos

Rand

Real

RelativeDate

RelativeTime

Replace

RGB

Right

RightTrim

Round

RowCount

Second

SecondsAfter

Sign

Sin

RowHeight

Small

Space

Sqrt

Stdevp

Stdev

String

Sum

Tan

Time

Today

Trim

Truncate

Upper

WordCap

Year

In addition to the above functions, User functions can be used in DataWindow expressions.

Unsupported

Mode

PageAcross

PageCountAcross

Paint

GetPaintDC

GetPaintRectHeight

GetPaintRectWidth

GetPaintRectX

GetPaintRectY

ProfileInt

ProfileString

Var

VarP

 

In addition to the above functions, System functions and External functions cannot be used in DataWindow expressions.


Specific Requirements for Using Expressions

Rule for all DataWindow expressions

  1. Expressions involving Null values may arrive at different values in JavaScript from their values in PowerScript. For more details, please refer to the Null Values section.

  2. Expressions cannot contain "~~t".

  3. Expressions cannot be used in cursor or stored procedure arguments.

  4. For nested structures, please verify that:

    • It is supported to have single quotes nested with single quotes or double quotes nested with single quotes.

    • It is unsupported to have single quotes nested with double quotes or double quotes nested with double quotes.

    • A multi-layer nested structure may cause problems.

  5. If using user-defined global functions in DataWindow expressions,

    • Arguments and returned values can only be simple data tapes: Int/Integer, Boolean, String, Character, Long, UnsignedLong, UnsignedInteger.

    • Arguments cannot be references.

    • If the computed field expression is a computed field or global function, the formatting for the computed field will not take effect on the Web.

Sort and Filter expressions

  1. Unsupported functions: Avg, Count, Max, Min & Sum.

  2. Behavioral difference: In a Web application, if a Find, Filter or Sort expression contains any special characters (for example, ".", "", "/"), the execution result may differ from PowerBuilder. In a Web application, the DataWindow rows may display in a different order from PowerBuilder.

Property expressions

  1. Overlapped quotes, for example, "sdf~"sdf", "dfg'sdf".

  2. In the Web application, modifying a DataWindow property in a DataWindow expression may conflict with the settings in the application's source code.

String expressions

Operands in the string expression cannot a mix of constants and variables.

Decimal Precision in DataWindow expression

28-digit Decimal is only supported in the following DataWindow expressions: Abs, Avg, CamulativeSum, Median, Sign, and Sum.