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
|
|
Beas 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"
|
|
Used for example in
Variant generator - Formulas
Work order structur view - Adaptation
Help URL: https://help.beascloud.com/beas202106/index.html?datawindow-syntax.htm
|