expression

Contents

Datawindow objects allow to work with expressions

In Variant generator, unit of measurement calculation and in other areas for formulas DataWindow-Syntax is used. This is to give a short overview of available functions. Further details see Powerbuilder-description available in the Internet.

Mathematical symbols (operators)

+	Addition (numbers) or consolidation (characters)
-	Subtraction
*	Multiplication
/	Division
^	Power

Auxiliary symbols

(	Left parenthesis
)	Right parenthesis
.	Decimal separators (all nationalities !!!)

- The formula must be logical, '('must be followed by')' etc.

- Any level of nesting is permitted.

abs	Absolute – removes the sign	abs(-3) = 3 abs(3) = 3
ceiling	Return of the next higher integer	ceiling(-4.2) = 4 ceiling(-4.8) = 4
cos	The angle (in rad) for which the cosine should be calculated	cos(0) = 1 cos(1) = 0.540302
exp	The power for the exponential e (2.71828)	Exp(2) = 7.3890560989
fact	Value for the faculty	fact(4) = 24 fact(0) = 1
int	Rounds down to the next integer	int (3.8) = 3 int (-3.2) = 4
log	Calculates the natural logarithm of a number	log(10) = 2.302585092
logten	Calculates the logarithm to base 10	logten(10) = 1
mod	Calculates the remainder of a division	mod(20, 6) = 2 mod(25, 4.5) = 2.5
pi	Multiplies Pi by the specified number	Pi ( 1 ) = 3.14159265
Border	Random number between 1 and specified number	rand(20) = 1 ... 20
round	Rounds the number from the specified position	round(3.333,2) = 3.33 round(3.335,2) = 3.34 round(324,-2) = 300
sign	Calculates whether the number is negative, zero or positive. (by checking the sign)	sign(5) = 1
sin	Calculates the sine of an angle	sin(1)=0.84147098
sqrt	Calculates the square root of a number	sqrt(2) = 1.414213562
tan	Calculates the tangent of an angle	tan(1) = 1.55741
truncate	Reduces a number to the specified places	truncate(9.22, 1) = 9.2 truncate(9.28, 1) = 9.2

<%PROGRAMNAME%> also permits the use of logical formulas with the following structure:

if ( logic , value by true , value by false)

e.g.

if ( red = blue , 3.34 * x , 0 ) + (x * 2)

Logical characters and functions

=	Both values are identical	Price = 100
>	Left value greater than right value	Price > 100
<	Left value smaller than right value	Price < 100
<>	Left value is not right value	Price <> 100
>=	Left value greater than or equal to right value	Price >= 100
<=	Left value smaller than or equal to right value	price <= 100
NOT=	Left value is not right value	Price not = 100
LIKE	Left value is part of right value	Name like 'C%'
IN	Left value contained in right list	Price in (100,200,300)
BETWEEN	Left value is in the range of	Price between 1000 and 2000
NOT LIKE	Left value is not in right value	Price not like 'C%'
NOT IN	Left value is not in right list	Price not in (100,200,300)
NOT BETWEEN	Left value is not in range	Price not between 1000 and 2000

LIKE works with placeholders:

% Any number of characters

_ Any single character

Example:

'c%' corresponds to any string beginning with c

'c_' corresponds to any string beginning with c and containing one additional arbitrary character

Conditions can be linked with AND and OR as well as with NOT AND/OR:

if ( x = y and x = z ,1,0)

if ( x = y or x = z,1,0)

if ( x = y and not x = z,1,0)

Logical formulas with "case"

With case different results can be returned depending on a condition.

case ( column when value1 then result1 { when value2 then result2

{ ... } } { else resultelse } )

case (marriage_status when 'S' then 'Single' when 'M' then 'Married' else 'Unknown')

Characters can be processed.

Characters are combined through +

a = "Adam"

b = "Eve"

a + " " + b = "Adam Eve"

Beginning and end of a string must be identified.

The following are permitted as identifier fields:

" "Hello World"

' 'Hello World'

[.] [Hello World]

The use of identifier fields in string fields is permitted if the string begins with a different identifier field:

"[Hello] 'World'" Result: [Hello] 'World'

There are many character editing functions

fill	Returns a filled string	fill(""),3) = "**" fill("-+"),5) = "-+-+-"
left	String that is truncated at the right	left("hello world,6)="hello "
lefttrim	String that is truncated at the right and has blank spaces removed	leftright("hello world,6)="hello"
len	Return of number of characters in a string	len("hello") = 5
lower	Swaps uppercase letters in a string for lowercase letters	lower("Hello") = hello
match	Checks characters used (for more information, see Sybase Infomaker)	match("A","^[A-Z]) = true
mid	Returns the specified part of a string	mid("Hello",2,2)= "al"
Pos	Returns the position of a given string in the first string	pos("Hello","l") = 3
replace	Replaces a string in another string	replace("David", 4, 2, "e") = "Dave"
right	String which is truncated at the left	right("hello world",5)=" world"
righttrim	String which is truncated at the left and has blank spaces removed	righttrim("hello world",5)="world"
space	Returns specified number of spaces	space(5)=" "
string	Converts a number, date, character into a formatted string. Observes international settings in doing so.	string(5.2344,"#,#0.00") Germany: "5,23" USA: 5.23
trim	Removes the spaces left and right	trim(" hello ") = "hello"
upper	Swaps lowercase letters in a string for uppercase letters	upper("Hello ") = "HELLO"
wordcap	Converts first letter into a capital letter and the remainder into lowercase letters.	wordcap("boston, MASSACHUSETTS") = "Boston, Massachusetts"