Custom Filter Functions for Transformation Maps

When you work with transformation maps, you can use default filters, or create custom filters using the functions in this reference. These functions are a subset of PowerBuilder language. In the Transformation Maps window, choose Custom filter… and click the filter icon to open the Specify Filter dialog box.
tdm49
When you work with transformation maps, you can use default filters, or create custom filters using the functions in this reference. These functions are a subset of PowerBuilder language. In the Transformation Maps window, choose
Custom filter…
and click the filter icon to open the
Specify Filter
dialog box.
Abs expression function
Calculates the absolute value of a number.
Syntax
Abs ( n )
  • n
    — The number for which you want the absolute value
Return Values
The datatype of n. Returns the absolute value of n.
Examples
This expression counts all the product numbers where the absolute value of the product number is distinct:
Count(product_number for All DISTINCT Abs (product_number))
Only data with an absolute value greater than 5 passes this validation rule:
Abs(value_set) > 5
ACos expression function
Calculates the arc cosine of an angle.
Syntax
ACos ( n )
  • n
    — The ratio of the lengths of two sides of a triangle for which you want a corresponding angle (in radians). The ratio must be a value between -1 and 1.
Return Values
Double. Returns the arc cosine of n if it succeeds.
Examples
This expression returns 0:
ACos(1)
This expression returns 3.141593 (rounded to six places):
ACos(-1)
This expression returns 1.000000 (rounded to six places):
ACos(.540302)
Asc expression function
Converts the first character of a string to its Unicode code point. A Unicode code point is the numerical integer value given to a Unicode character.
Syntax
Asc ( string )
  • string
    — The string for which you want the code point value of the first character
Return Values
Unsigned integer. Returns the code point value of the first character in string.
Usage
Use Asc to test the case of a character or manipulate text and letters. To find out the case of a character, you can check whether its code point value is within the appropriate range.
Examples
This expression for a computed field returns the string in code_id if the code point value of the first character in code_id is A (65):
If (Asc(code_id) = 65, code_id, "Not a valid code")
This expression for a computed field checks the case of the first character of lname and if it is lowercase, makes it uppercase:
IF (Asc(lname) > 64 AND Asc(lname) < 91, lname, WordCap(lname))
AscA expression function
Converts the first character of a string to its ASCII integer value.
Syntax
AscA ( string )
  • string
    — The string for which you want the ASCII value of the first character
Return Values
Integer. Returns the ASCII value of the first character in string.
Usage
Use AscA to test the case of a character or manipulate text and letters. To find out the case of a character, you can check whether its ASCII value is within the appropriate range.
Examples
This expression for a computed field returns the string in code_id if the ASCII value of the first character in code_id is A (65):
If (AscA(code_id) = 65, code_id, "Not a valid code")
This expression for a computed field checks the case of the first character of lname and if it is lowercase, makes it uppercase:
IF (AscA(lname) > 64 AND AscA(lname) < 91, lname, WordCap(lname))
ASin expression function
Calculates the arc sine of an angle.
Syntax
ASin ( n )
  • n
    — The ratio of the lengths of two sides of a triangle for which you want a corresponding angle (in radians). The ratio must be a value between -1 and 1.
Return Values
Double. Returns the arc sine of n if it succeeds.
Examples
This expression returns .999998 (rounded to six places):
ASin(.84147)
This expression returns .520311 (rounded to six places):
ASin(LogTen (Pi (1)))
This expression returns 0:
ASin(0)
ATan expression function
Calculates the arc tangent of an angle.
Syntax
ATan ( n )
  • n
    — The ratio of the lengths of two sides of a triangle for which you want a corresponding angle (in radians)
Return Values
Double. Returns the arc tangent of n if it succeeds.
Examples
This expression returns 0:
ATan(0)
This expression returns 1.000 (rounded to three places):
ATan(1.55741)
This expression returns 1.267267 (rounded to six places):
ATan(Pi(1))
Avg expression function
Calculates the average of the values of the column.
Syntax
Avg (
column
{ FOR
range
{ DISTINCT {
expres1
{, expres2 {, ... } } } } } )
  • column
    — The column for which you want the average of the data values. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric.
  • FOR
    range
    — (optional) The data to include in the average. For most presentation styles, values for range are:
    • ALL
      – (Default) The average of all values in column.
    • GROUP
      n
      – The average of values in column in the specified group. Specify the keyword GROUP followed by the group number, for example, GROUP 1.
    • PAGE
      – The average of the values in column on a page.
    • CROSSTAB
      – (Crosstabs only) The average of all values in column in the crosstab.
    • GRAPH
      – (Graphs only) The average of values in column in the range specified for the Rows option.
    • OBJECT
      – (OLE objects only) The average of values in column in the range specified for the Rows option.
  • DISTINCT
    — (optional) Causes Avg to consider only the distinct values in column when calculating the average. For a value of column, the first row found with the value is used and other rows that have the same value are ignored.
  • expresn
    — (optional) One or more expressions that you want to evaluate to determine distinct rows. Expresn can be the name of a column, a function, or an expression.
Return Values
The numeric datatype of the column. Returns the average of the values of the rows in range.
Usage
If you specify range, Avg returns the average value of column in range. If you specify DISTINCT, Avg returns the average value of the distinct values in column, or if you specify expresn, the average of column for each distinct value of expresn.
For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include the following:
  • For the Graph or OLE presentation style, Rows is always All.
  • For Graph controls, Rows can be All, Page, or Group.
  • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies.
In calculating the average, null values are ignored.
You cannot use this or other aggregate functions in validation rules or filter expressions. Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a
DataWindow
object always retrieves all rows.
Examples
This expression returns the average of the values in the column named salary:
Avg(salary)
This expression returns the average of the values in group 1 in the column named salary:
Avg(salary for group 1)
This expression returns the average of the values in column 5 on the current page:
Avg(#5 for page)
This computed field returns Above Average if the average salary for the page is greater than the average salary:
If(Avg(salary for page) > Avg(salary), "Above Average", " ")
This expression for a graph value sets the data to the average value of the sale_price column:
Avg(sale_price)
This expression for a graph value sets the data value to the average value of the sale_price column for the entire graph:
Avg(sale_price for graph)
Assuming a DataWindow object displays the order number, amount, and line items for each order, this computed field returns the average of the order amount for the distinct order numbers:
Avg(order_amt for all DISTINCT order_nbr)
Bitmap expression function
Displays the specified bitmap.
Syntax
Bitmap ( string )
  • string
    — A column containing bitmap files, a string containing the name of an image file (a BMP, GIF, JPEG, RLE, or WMF file), or an expression that evaluates to a string containing the name of an image file.
Return Values
The special datatype bitmap, which cannot be used in any other function.
Usage
Use Bitmap to dynamically display a bitmap in a computed field. When
string
is a column containing bitmap files, a different bitmap can display for each row.
You can use the Bitmap function only in a computed field.
Examples
These examples are all expressions for a computed field.
This expression dynamically displays the bitmap file contained in the column named employees:
Bitmap(employees)
If the employees column is column 3, this next expression gives the same result as the expression above:
Bitmap(#3)
This expression displays the bitmap tools.bmp:
Bitmap("TOOLS.BMP")
This expression tests the value in the column named password and then uses the value to determine which bitmap to display:
Bitmap(If(password = "y", "yes.bmp", "no.bmp"))
Case expression function
Tests the values of a column or expression and returns values based on the results of the test.
Syntax
Case ( column WHEN value1 THEN result1 { WHEN value2 THEN result2 { ... } } { ELSE resultelse } )
  • column
    — The column or expression whose values you want to test. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. Column is compared to each valuen.
  • WHEN
    — (optional) Introduces a value-result pair. At least one WHEN is required.
  • valuen
    — One or more values that you want to compare to values of column. A value can be:
    • A single value
    • A list of values separated by commas (for example, 2, 4, 6, 8)
    • A TO clause (for example, 1 TO 20)
    • IS followed by a relational operator and comparison value (for example, IS>5)
    • Any combination of the above with an implied OR between expressions (for example, 1,3,5,7,9,27 TO 33, IS>42)
  • THEN
    — Introduces the result to be returned when column matches the corresponding
    valuen
    .
  • resultn
    — An expression whose value is returned by Case for the corresponding valuen. All
    resultn
    values must have the same datatype.
  • ELSE
    — (optional) Specifies that for any values of column that do not match the values of
    valuen
    already specified, Case returns
    resultelse
    .
  • resultelse
    — An expression whose value is returned by Case when the value of column does not match any WHEN
    valuen
    expression.
Return Values
The datatype of
resultn
. Returns the result you specify in
resultn
. If more than one WHEN clause matches
column
, Case returns the result of the first matching one.
Examples
This expression for the
Background.Color
property of a
Salary
column returns values that represent red when an employee's salary is greater than $70,000, green when an employee's salary is greater than $50,000, and blue otherwise:
Case(salary WHEN IS >70000 THEN RGB(255,0,0) WHEN IS >50000 THEN RGB(0,255,0) ELSE RGB(0,0,255))
This expression for the Background.Color property of an employee Id column returns red for Id 101, gray for Id 102, and black for all other Id numbers:
Case(emp_id WHEN 101 THEN 255 WHEN 102 THEN RGB(100,100,100) ELSE 0)
This expression for the Format property of the
Marital_status
column returns Single, Married, and Unknown based on the data value of the Marital_status column for an employee:
Case(marital_status WHEN 'S'THEN 'Single' WHEN 'M' THEN 'Married' ELSE 'Unknown')
Ceiling expression function
Retrieves the smallest whole number that is greater than or equal to a specified limit.
Syntax
Ceiling ( n )
  • n
    — The number for which you want the smallest whole number that is greater than or equal to it.
Return Values
The datatype of n. Returns the smallest whole number that is greater than or equal to n.
Examples
These expressions both return -4:
Ceiling(-4.2) Ceiling(-4.8)
This expression for a computed field returns ERROR if the value in discount_amt is greater than the smallest whole number that is greater than or equal to discount_factor times price. Otherwise, it returns discount_amt:
If(discount_amt <= Ceiling(discount_factor * price), String(discount_amt), "ERROR")
To pass this validation rule, the value in discount_amt must be less than or equal to the smallest whole number that is greater than or equal to discount_factor times price:
discount_amt <= Ceiling(discount_factor * price)
Char expression function
Converts an integer to a Unicode character.
Syntax
Char ( n )
  • n
    — The integer you want to convert to a character
Return Values
String. Returns the character whose code point value is n.
Examples
This expression returns the escape character:
Char(27)
CharA expression function
Converts an integer to an ASCII character.
Syntax
CharA ( n )
  • n
    — The integer you want to convert to a character.
Return Values
String. Returns the character whose ASCII value is n.
Examples
This expression returns the escape character:
CharA(27)
Cos expression function
Calculates the cosine of an angle.
Syntax
Cos ( n )
  • n
    — The angle (in radians) for which you want the cosine
Return Values
Double. Returns the cosine of n.
Examples
This expression returns 1:
Cos(0)
This expression returns .540302:
Cos(1)
This expression returns -1:
Cos(Pi(1))
Count expression function
Calculates the total number of rows in the specified column.
Syntax
Count ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } )
  • column
    — The column for which you want the number of rows. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column.
  • FOR
    range
    — (optional) The data that will be included in the count. For most presentation styles, values for
    range
    are:
    • ALL
      — (Default) The count of all rows in column.
    • GROUP
      n
      — The count of rows in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1.
    • PAGE
      — The count of the rows in column on a page.
    • CROSSTAB
      — (Crosstabs only) The count of all rows in column in the crosstab.
    • GRAPH
      — (Graphs only) The count of values in column in the range specified for the Rows option.
    • OBJECT
      — (OLE objects only) The count of values in column in the range specified for the Rows option.
  • DISTINCT
    — (optional) Causes Count to consider only the distinct values in column when counting the rows. For a value of column, the first row found with the value is used and other rows that have the same value are ignored.
  • expresn
    — (optional) One or more expressions that you want to evaluate to determine distinct rows. Expresn can be the name of a column, a function, or an expression.
Usage
If you specify
range
, Count determines the number of rows in column in range. If you specify DISTINCT, Count returns the number of the distinct rows displayed in column, or if you specify
expresn
, the number of rows displayed in column where the value of
expresn
is distinct. For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range.
Settings for Rows include the following:
  • For the Graph or OLE presentation style, Rows is always All.
  • For Graph controls, Rows can be All, Page, or Group.
  • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies.
Null values in the column are ignored and are not included in the count.
You cannot use this or other aggregate functions in validation rules or filter expressions.
Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a DataWindow object always retrieves all rows.
Examples
This expression returns the number of rows in the column named emp_id that are not null:
Count(emp_id)
This expression returns the number of rows in the column named emp_id of group 1 that are not null:
Count(emp_id for group 1)
This expression returns the number of dept_ids that are distinct:
Count(dept_id for all DISTINCT)
This expression returns the number of regions with distinct products:
Count(region_id for all DISTINCT Lower(product_id))
This expression returns the number of rows in column 3 on the page that are not null:
Count(#3 for page)
CrosstabAvg expression function
Calculates the average of the values returned by an expression in the values list of the crosstab. When the crosstab definition has more than one column, CrosstabAvg can also calculate averages of the expression's values for groups of column values. For more information, see How to Use Functions in a Crosstab.
Syntax
CrosstabAvg ( n {, column, groupvalue } )
  • n
    — The number of the crosstab-values expression for which you want the average of the returned values. The crosstab expression must be numeric.
  • column
    — (optional) The number of the crosstab column as it is listed in the Columns box of the Crosstab Definition dialog box for which you want intermediate calculations.
  • groupvalue
    — (optional) A string whose value controls the grouping for the calculation. Groupvalue is usually a value from another column in the crosstab. To specify the current column value in a dynamic crosstab, rather than a specific value, specify @ plus the column name as a quoted string.
Return Values
Double. Returns the average of the crosstab values returned by expression n for all the column values or, optionally, for a subset of column values.
Usage
This function is meaningful only for the average of the values of the expression in a row in the crosstab. This means you can use it only in the detail band, not in a header, trailer, or summary band. Null values are ignored and are not included in the average.
You can use this function only in a crosstab DataWindow object. For details, see How to Use Functions in a Crosstab.
Examples
The first two examples use the crosstab expressions shown below:
Count(emp_id for crosstab),Sum(salary for crosstab)
This expression for a computed field in the crosstab returns the average of the employee counts (the first expression):
CrosstabAvg(1)
This expression for a computed field in the crosstab returns the average of the salary totals (the second expression):
CrosstabAvg(2)
Consider a crosstab that has two columns (region and city) and the values expression Avg(sales for crosstab). This expression for a computed field in the detail band computes the average sales over all the cities in a region:
CrosstabAvg(1, 2, "@region")
This expression for another computed field in the same crosstab computes the grand average over all the cities:
CrosstabAvg(1)
CumulativePercent expression function
Calculates the total value of the rows up to and including the current row in the specified column as a percentage of the total value of the column (a running percentage).
Syntax
CumulativePercent ( column { FOR range } )
  • column
    — The column for which you want the cumulative value of the rows up to and including the current row as a percentage of the total value of the column for range. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric.
  • FOR
    range
    — (optional) The data that will be included in the cumulative percentage. For most presentation styles, values for range are:
    • ALL
      — (Default) The cumulative percentage of all rows in column.
    • GROUP
      n
      — The cumulative percentage of rows in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1.
    • PAGE
      — The cumulative percentage of the rows in column on a page.
    • CROSSTAB
      — (Crosstabs only) The cumulative percentage of all rows in column in the crosstab.
    • GRAPH
      — (Graphs only) The cumulative percentage of values in column in the range specified for the Rows option.
    • OBJECT
      — (OLE objects only) The cumulative percentage of values in column in the range specified for the Rows option.
Return Values
Long. Returns the cumulative percentage value.
Usage
If you specify range, CumulativePercent restarts the accumulation at the start of the range.
For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range.
Settings for Rows include the following:
  • For the Graph or OLE presentation style, Rows is always All.
  • For Graph controls, Rows can be All, Page, or Group.
  • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies.
In calculating the percentage, null values are ignored.
You cannot use this or other aggregate functions in validation rules or filter expressions.
Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a DataWindow object always retrieves all rows.
Examples
This expression returns the running percentage for the values that are not null in the column named salary:
CumulativePercent(salary)
This expression returns the running percentage for the column named salary for the values in group 1 that are not null:
CumulativePercent(salary for group 1)
This expression entered in the Value box on the Data property page for a graph returns the running percentage for the salary column for the values in the graph that are not null:
CumulativePercent(salary for graph)
This expression in a crosstab computed field returns the running percentage for the salary column for the values in the crosstab that are not null:
CumulativePercent(salary for crosstab)
CumulativeSum expression function
Calculates the total value of the rows up to and including the current row in the specified column (a running total).
Syntax
CumulativeSum ( column { FOR range } )
  • column
    The column for which you want the cumulative total value of the rows up to and including the current row for group. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric.
  • FOR
    range
    — (optional) The data that will be included in the cumulative sum. For most presentation styles, values for range are:
    • ALL
      – (Default) The cumulative sum of all values in column.
    • GROUP
      n
      – The cumulative sum of values in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1.
    • PAGE
      – The cumulative sum of the values in column on a page.
    • CROSSTAB
      – (Crosstabs only) The cumulative sum of all values in column in the crosstab.
    • GRAPH
      – (Graphs only) The cumulative sum of values in column in the range specified for the Rows option.
    • OBJECT
      – (OLE objects only) The cumulative sum of values in column in the range specified for the Rows option.
Return Values
The appropriate numeric datatype. Returns the cumulative total value of the rows.
Usage
If you specify range, CumulativeSum restarts the accumulation at the start of the range.
For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include the following:
  • For the Graph or OLE presentation style, Rows is always All.
  • For Graph controls, Rows can be All, Page, or Group.
  • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies.
In calculating the sum, null values are ignored.
Examples
This expression returns the running total for the values that are not null in the column named salary:
CumulativeSum(salary)
This expression returns the running total for the values that are not null in the column named salary in group 1:
CumulativeSum(salary for group 1)
This expression entered in the Value box on the Data property page for a graph returns the running total for the salary column for the values in the graph that are not null:
CumulativeSum(salary for graph)
This expression in a crosstab computed field returns the running total for the salary column for the values in the crosstab that are not null:
CumulativeSum(salary for crosstab)
CurrentRow expression function
Reports the number of the current row (the row with focus).
Definition: The current row is not always a row displayed on the screen. For example, if the cursor is on row 7 column 2 and the user uses the scroll bar to scroll to row 50, the current row remains row 7, unless the user clicks row 50.
Syntax
CurrentRow ( )
Return Values
Long. Returns the number of the row if it succeeds and 0 if no row is current.
Examples
This expression in a computed field returns the number of the current row:
CurrentRow()
This expression for a computed control displays an arrow bitmap as an indicator for the row with focus and displays no bitmap for rows not having focus. As the user moves from row to row, an arrow marks where the user is:
Bitmap(If(CurrentRow() = GetRow(),"arrow.bmp",""))
Alternatively, this expression for the Visible property of an arrow picture control makes the arrow bitmap visible for the row with focus and invisible for rows not having focus. As the user moves from row to row, an arrow marks where the user is:
If(CurrentRow() = GetRow(), 1, 0)
Date expression function
Converts a string whose value is a valid date to a value of datatype date.
Syntax
Date ( string )
  • string
    A string containing a valid date (such as Jan 1, 2004, or 12-31-99) that you want returned as a date.
Return Values
Date. Returns the date in string as a date. If string does not contain a valid date, Date returns null.
Usage
The value of the string must be a valid date.
To make sure you get correct return values for the year, you must verify that yyyy is the Short Date Style for year in the Regional Settings of the user's Control Panel. Your program can check this with the RegistryGet function.
If the setting is not correct, you can ask the user to change it manually or to have the application change it (by calling the RegistrySet function). The user might need to reboot after the setting is changed.
Valid dates
Valid dates can include any combination of day (1–31), month (1–12 or the name or abbreviation of a month), and year (two or four digits). Leading zeros are optional for month and day. If the month is a name or an abbreviation, it can come before or after the day; if it is a number, it must be in the month location specified in the Windows control panel. A 4-digit number is assumed to be a year.
If the year is two digits, the assumption of century follows this rule: for years between 00 and 49, the first two digits are assumed to be 20; for years between 50 and 99, the first two digits are assumed to be 19. If your data includes dates before 1950, such as birth dates, always specify a four-digit year to ensure the correct interpretation.
The function handles years from 1000 to 3000 inclusive.
An expression has a more limited set of datatypes than the functions that can be part of the expression. Although the Date function returns a date value, the whole expression is promoted to a DateTime value. Therefore, if your expression consists of a single Date function, it will appear that Date returns the wrong datatype. To display the date without the time, consult the PowerBuilder documentation, and choose an appropriate display format.
Examples
These expressions all return the date datatype for July 4, 2004 when the default location of the month in Regional Settings is center:
Date("2004/07/04") Date("2004 July 4") Date("July 4, 2004")
DateTime expression function
Combines a date and a time value into a DateTime value.
Syntax
DateTime ( date {, time } )
  • date
    A valid date (such as Jan 1, 2005, or 12-31-99) or a blob variable whose first value is a date that you want included in the value returned by DateTime.
  • time
    — (optional) A valid time (such as 8am or 10:25:23:456799) or a blob variable whose first value is a time that you want included in the value returned by DateTime. If you include a time, only the hour portion is required. If you omit the minutes, seconds, or microseconds, they are assumed to be zeros. If you omit am or pm, the hour is determined according to the 24-hour clock.
Return Values
DateTime. Returns a DateTime value based on the values in date and optionally time. If time is omitted, DateTime uses 00:00:00.000000 (midnight).
Usage
To display microseconds in a time, the display format for the field must include microseconds. For information on valid dates, see Date.
Examples
This expression returns the values in the order_date and order_time columns as a DateTime value that can be used to update the database:
DateTime(Order_Date, Order_Time)
Using this expression for a computed field displays 11/11/01 11:11:00:
DateTime(11/11/01, 11:11)
Day expression function
Obtains the day of the month in a date value.
Syntax
Day ( date )
  • date
    The date for which you want the day
Return Values
Integer. Returns an integer (1–31) representing the day of the month in date.
Examples
This expression returns 31:
Day(2005-01-31)
This expression returns the day of the month in the start_date column:
Day(start_date)
DayName expression function
Gets the day of the week in a date value and returns the weekday's name.
Syntax
DayName ( date )
  • date
    The date for which you want the name of the day
Return Values
String. Returns a string whose value is the name of the weekday (Sunday, Monday, and so on) for date.
Usage
DayName returns a name in the language of the deployment files available on the machine where the application is run. If you have installed localized deployment files in the development environment or on a user's machine, then on that machine the name returned by DayName will be in the language of the localized files.
For information about localized deployment files, please consult the PowerBuilder documentation.
Examples
This expression for a computed field returns Okay if the day in date_signed is not Sunday:
If(DayName(date_signed) <> "Sunday", "Okay", "Invalid Date")
To pass this validation rule, the day in date_signed must not be Sunday:
DayName(date_signed) <> "Sunday"
DayName expression function
Gets the day of the week in a date value and returns the weekday's name.
Syntax
DayName ( date )
  • date
    The date for which you want the name of the day
Return Values
String. Returns a string whose value is the name of the weekday (Sunday, Monday, and so on) for date.
Usage
DayName returns a name in the language of the deployment files available on the machine where the application is run. If you have installed localized deployment files in the development environment or on a user's machine, then on that machine the name returned by DayName will be in the language of the localized files.
For information about localized deployment files, see the chapter on internationalizing an application in Application Techniques.
Examples
This expression for a computed field returns Okay if the day in date_signed is not Sunday:
If(DayName(date_signed) <> "Sunday", "Okay", "Invalid Date")
To pass this validation rule, the day in date_signed must not be Sunday:
DayName(date_signed) <> "Sunday"
DaysAfter expression function
Gets the number of days one date occurs after another.
Syntax
DaysAfter ( date1, date2 )
  • date1
    — A date value that is the start date of the interval being measured.
  • date2
    A date value that is the end date of the interval.
Return Values
Long. Returns a long containing the number of days date2 occurs after date1. If date2 occurs before date1, DaysAfter returns a negative number.
Examples
This expression returns 4:
DaysAfter(2005-12-20, 2005-12-24)
This expression returns -4:
DaysAfter(2005-12-24, 2005-12-20)
This expression returns 0:
DaysAfter(2005-12-24, 2005-12-24)
This expression returns 5:
DaysAfter(2004-12-29, 2005-01-03)
Dec expression function
Converts the value of a string to a decimal.
Syntax
Dec ( string )
  • string
    The string you want returned as a decimal
Return Values
Decimal. Returns the contents of string as a decimal if it succeeds and 0 if string is not a number.
Usage
The decimal datatype supports up to 28 digits. You can also append the letter D in upper or lowercase to identify a number as a decimal constant in DataWindow expressions. For example, 2.0d and 123.456789012345678901D are treated as decimals.
Examples
This expression returns the string 24.3 as a decimal datatype:
Dec("24.3")
This expression for a computed field returns "Not a valid score" if the string in the score column does not contain a number. The expression checks whether the Dec function returns 0, which means it failed to convert the value:
If ( Dec(score) <> 0, score, "Not a valid score")
This expression returns 0:
Dec("3ABC") // 3ABC is not a number
This validation rule checks that the value in the column the user entered is greater than 1999.99:
Dec(GetText()) > 1999.99
This validation rule for the column named score insures that score contains a string:
Dec(score) <> 0
Describe method
Reports the values of properties of a DataWindow object and controls within the DataWindow object. Each column and graphic control in the DataWindow has a set of properties. You specify one or more properties as a string, and Describe returns the values of the properties.
Describe can also evaluate expressions involving values of a particular row and column. When you include Describe's Evaluate function in the property list, the value of the evaluated expression is included in the reported information.
Controls
The three DataWindow types apply to the following controls:
  • PowerBuilder — Applies to DataWindow control, DataWindowChild object, DataStore object.
  • Web — Applies to Server component .
  • Web ActiveX — Applies to DataWindow control, DataWindowChild object.
Syntax
string dwcontrol.Describe ( string propertylist )
  • dwcontrol
    — A reference to a DataWindow control, DataStore, or child DataWindow.
  • propertylist
    — A string whose value is a blank-separated list of properties or Evaluate functions. For a list of valid properties, see "DataWindow Object Properties."
Return Values
Returns a string that includes a value for each property or Evaluate function. A newline character (~n or \n) separates the value of each item in propertylist.
If the property list contains an invalid item, Describe returns an exclamation point (!) for that item and ignores the rest of the property list. Describe returns a question mark (?) if there is no value for a property.
When the value of a property contains an exclamation point or a question mark, the value is returned in quotes so that you can distinguish between the returned value and an invalid item or a property with no value.
If any argument's value is null, in PowerBuilder and JavaScript the method returns null.
Usage
Use Describe to understand the structure of a DataWindow. For example, you can find out which bands the DataWindow uses and what the datatypes of the columns are. You can also use Describe to find out the current value of a property and use that value to make further modifications.
Describe is often used to obtain the DataWindow's SELECT statement in order to modify it (for example, by adding a WHERE clause).
When you can obtain the DataWindow's SQL statement: When you use the Select painter to graphically create a SELECT statement, PowerBuilder saves its own SELECT statement (called a PBSELECT statement), and not a SQL SELECT statement, with the DataWindow definition. When you call Describe with the property Table.Select, it returns a SQL SELECT statement only if you are connected to the database. If you are not connected to the database, Describe returns a PBSELECT statement.
Property syntax
The syntax for a property in the property list is:
controlname.property
When a property returns a list, the tab character separates the values in the list. For example, the Bands property reports all the bands in use in the DataWindow as a list.
header[tab]detail[tab]summary[tab]footer[tab]header.1[tab]trailer.1
If the first character in a property's returned value list is a quotation mark, it means the whole list is quoted and any quotation marks within the list are single quotation marks.
For example, the following is a single property value.
" Student[tab]'Andrew'or'[newline]Andy' "
Specifying special characters
There are different ways of specifying special characters in a string in each environment:
Character
PowerBuilder
JavaScript
tab
~t
\t
newline
~n
\n
single quote
~'
\'
double quote
~"
\"
Quoted property values
Describe returns a property's value enclosed in quotes when the text would otherwise be ambiguous. For example, if the property's value includes a question mark, then the text is returned in quotes. A question mark without quotes means that the property has no value.
Column name or number
When the control is a column, you can specify the column name or a pound sign (#) followed by the column number. For example, if salary is column 5, then "salary.coltype" is equivalent to "#5.coltype".
Control names
The DataWindow painter automatically gives names to all controls. In previous versions of PowerBuilder, the painter only named columns and column labels.
Evaluating an expression
Describe's Evaluate function allows you to evaluate DataWindow painter expressions within a script using data in the DataWindow. Evaluate has the following syntax, which you specify for propertylist.
Evaluate ( 'expression', rownumber )
Expression is the expression you want to evaluate and rownumber is the number of the row for which you want to evaluate the expression. The expression usually includes DataWindow painter functions. For example, in the following statement, Describe reports either 255 or 0 depending on the value of the salary column in row 3:
ls_ret = dw_1.Describe( & "Evaluate('If(salary > 100000, 255, 0)', 3)")
You can call DataWindow control functions in a script to get data from the DataWindow, but some painter functions (such as LookUpDisplay) cannot be called in a script. Using Evaluate is the only way to call them.
Sample property values
To illustrate the types of values that Describe reports, consider a DataWindow called dw_emp with one group level. Its columns are named emp and empname, and its headers are named emp_h and empname_h. The following table shows several properties and the returned value. In the first example below, a sample command shows how you might specify these properties for Describe and what it reports.
The following table shows examples of return values for Describe method:
Property
Reported value
Comment
datawindow.Bands
header[tab]detail[tab]summary[tab]footer[tab]header.1[tab]trailer.1
datawindow.Objects
emp[tab]empname[tab]emp_h[tab]empname_hemp.Type column
empname.Type
column
empname_h.Type
text
emp.Coltype
char(20)
state.Type
!
The exclamation point indicates an invalid item: There is no column named state.
empname_h.Visible
?
PowerBuilder Examples
This example calls Describe with some of the properties shown in the previous table. The reported values (formatted with tabs and newlines) follow. Note that because state is not a column in the DataWindow, state.type returns an exclamation point:
string ls_request, ls_report
ls_request = "DataWindow.Bands DataWindow.Objects "&
+ "empname_h.Text " &
+ "empname_h.Type emp.Type emp.Coltype " &
+ "state.Type empname.Type empname_h.Visible"
ls_report = dw_1.Describe(ls_request)
Describe sets the value of ls_report to the following string:
header~tdetail~tsummary~tfooter~theader.1~ttrailer.1~N emp~tempname~temp_h~tempname_h~N "Employee~R~NName"~N text~N column~Nchar(20)~N!
These statements check the datatype of the column named salary before using GetItemNumber to obtain the salary value:
string ls_data_type
integer li_rate
ls_data_type = dw_1.Describe("salary.ColType")
IF ls_data_type = "number" THEN
li_rate = dw_1.GetItemNumber(5, "salary")
ELSE
. . . // Some processing
END IF
Example: Column name or number
This statement finds out the column type of the current column, using the column name:
s = This.Describe(This.GetColumnName()+ ".ColType")
For comparison, this statement finds out the same thing, using the current column's number:
s = This.Describe("#" + String(This.GetColumn()) &
+ ".ColType")
Example: Scrolling and the current row
This example, as part of the DataWindow control's ScrollVertical event, makes the first visible row the current row as the user scrolls through the DataWindow:
s = This.Describe("DataWindow.FirstRowOnPage")
IF IsNumber(s) THEN This.SetRow(Integer(s))
Example: Evaluating the display value of a DropDownDataWindow
This example uses Describe's Evaluate function to find the display value in a DropDownDataWindow column called state_code. You must execute the code after the ItemChanged event, so that the value the user selected has become the item value in the buffer. This code is the script of a custom user event called getdisplayvalue:
string rownumber, displayvalue
rownumber = String(dw_1.GetRow())
displayvalue = dw_1.Describe( &
"Evaluate('LookUpDisplay(state_code) ', " &
+ rownumber + ")")
This code, as part of the ItemChanged event's script, posts the getdisplayvalue event:
dw_1.PostEvent("getdisplayvalue")
Example: Assigning null values based on the column's datatype
The following excerpt from the ItemError event script of a DataWindow control allows the user to blank out a column and move to the next column. For columns with datatypes other than string, the user cannot leave the value empty (which is an empty string and does not match the datatype) without the return code. Data and row are arguments of the ItemError event:
string s
s = This.Describe(This.GetColumnName() &
+ ".Coltype")
CHOOSE CASE s
CASE "number"
IF Trim(data) = "" THEN
integer null_num
SetNull(null_num)
This.SetItem(row, &
This.GetColumn(), null_num)
RETURN 3
END IF
CASE "date"
IF Trim(data) = "" THEN
date null_date
SetNull(null_date)
This.SetItem(row, &
This.GetColumn(), null_date)
RETURN 3
END IF
. . . // Additional cases for other datatypes
END CHOOSE
Exp expression function
Raises e to the specified power
n
.
Syntax
Exp (
n
)
  • n
    — The power to which you want to raise e (2.71828)
Return Values
Double. Returns e raised to the power n.
Examples
This expression returns 7.38905609893065:
Exp(2)
Fact expression function
Gets the factorial of a number.
Syntax
Fact ( n )
  • n
    — The number for which you want the factorial
Return Values
Double. Returns the factorial of n.
Examples
This expression returns 24:
Fact(4)
Both these expressions return 1:
Fact(1) Fact(0)
Fill expression function
Builds a string of the specified length by repeating the specified characters until the result string is long enough.
Syntax
Fill ( chars, n )
chars A string whose value will be repeated to fill the return string
  • n
    — A long whose value is the number of characters in the string you want returned
Return Values
String. Returns a string n characters long filled with repetitions of the characters in the argument chars. If the argument chars has more than n characters, the first n characters of chars are used to fill the return string. If the argument chars has fewer than n characters, the characters in chars are repeated until the return string has n characters.
Usage
Fill is used to create a line or other special effect. For example, asterisks repeated in a printed report can fill an amount line, or hyphens can simulate a total line in a screen display.
Examples
This expression returns a string containing 35 asterisks:
Fill("*", 35)
This expression returns the string "-+-+-+-":
Fill("-+", 7)
This expression returns 10 tildes (~):
Fill("~", 10)
FillA expression function
Builds a string of the specified length in bytes by repeating the specified characters until the result string is long enough.
Syntax
FillA ( chars, n )
  • chars
    A string whose value will be repeated to fill the return string.
  • n
    — A long whose value is the number of bytes in the string you want returned.
Return Values
String. Returns a string n bytes long filled with repetitions of the characters in the argument chars. If the argument chars has more than n bytes, the first n bytes of chars are used to fill the return string. If the argument chars has fewer than n bytes, the characters in chars are repeated until the return string has n bytes.
Usage
FillA replaces the functionality that Fill had in DBCS environments in PowerBuilder 9. In SBCS environments, Fill and FillA return the same results.
First expression function
Reports the value in the first row in the specified column.
Syntax
First ( column { FOR range { DISTINCT { expresn {, expres2 {, ... } } } } } )
  • column
    The column for which you want the value of the first row. Column can be a column name or a column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column.
  • FOR
    range
    — (optional) The data that will be included when the value in the first row is found. Values for range depend on the presentation style. For most presentation styles, values for range are:
    • ALL
      – (Default) The value in the first of all rows in column.
    • GROUP
      n
      – The value in the first of rows in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1.
    • PAGE
      – The value in the first of the rows in column on a page.
    • CROSSTAB
      – (Crosstabs only) The value in the first of all rows in column in the crosstab.
    • GRAPH
      – (Graphs only) The value in the first row in column in the range specified for the Rows option
    • OBJECT
      – (OLE objects only) The value in the first row in column in the range specified for the Rows option
  • DISTINCT
    — (optional) Causes First to consider only the distinct values in column when determining the first value. For a value of column, the first row found with the value is used and other rows that have the same value are ignored.
  • expresn
    — (optional) One or more expressions that you want to evaluate to determine distinct rows. Expresn can be the name of a column, a function, or an expression.
Return Values
The datatype of the column. Returns the value in the first row of column. If you specify range, First returns the value of the first row in column in range.
Usage
If you specify range, First determines the value of the first row in column in range. If you specify DISTINCT, First returns the first distinct value in column, or if you specify expresn, the first distinct value in column where the value of expresn is distinct.
For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include the following:
  • For the Graph or OLE presentation style, Rows is always All.
  • For Graph controls, Rows can be All, Page, or Group.
  • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies.
You cannot use this or other aggregate functions in validation rules or filter expressions. Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a DataWindow object always retrieves all rows.
Examples
This expression returns the first value in column 3 on the page:
First(#3 for page)
This expression returns the first distinct value in the column named dept_id in group 2:
First(dept_id for group 2 DISTINCT)
This expression returns the first value in the column named dept_id in group 2:
First(dept_id for group 2)
GetRow expression function
Reports the number of a row associated with a band in a DataWindow object.
Syntax
GetRow ( )
Return Values
Long. Returns the number of a row if it succeeds, 0 if no data has been retrieved or added, and –1 if an error occurs. Where you call GetRow determines what row it returns, as follows:
If the control in the DataWindow object is in this band
then GetRow returns:
Header
First row on the page
Group header
First row in the group
Detail
The row in which the expression occurs
Group trailer
Last row in the group
Summary
Last row in the DataWindow object
Footer
Last row on the page
Examples
This expression for a computed field in the detail band displays the number of each row:
GetRow()
This expression for a computed field in the header band checks to see if there is data. It returns the number of the first row on the page if there is data, and otherwise returns No Data:
If(GetRow()= 0, "No Data", String(GetRow()))
Hour expression function
Obtains the hour in a time value. The hour is based on a 24-hour clock.
Syntax
Hour ( time )
  • time
    — The time value from which you want the hour
Return Values
Integer. Returns an integer (00–23) containing the hour portion of time.
Examples
This expression returns the current hour:
Hour(Now())
This expression returns 19:
Hour(19:01:31)
If expression function
Evaluates a condition and returns a value based on that condition.
Syntax
If ( boolean, truevalue, falsevalue )
  • boolean
    — A boolean expression that evaluates to true or false.
  • truevalue
    — The value you want returned if the boolean expression is true. The value can be a string or numeric value.
  • falsevalue
    — The value you want returned if the boolean expression is false. The value can be a string or numeric value.
Return Values
The datatype of truevalue or falsevalue. Returns truevalue if boolean is true and falsevalue if it is false. Returns null if an error occurs.
Examples
This expression returns Boss if salary is over $100,000 and Employee if salary is less than or equal to $100,000:
If(salary > 100000, "Boss", "Employee")
This expression returns Boss if salary is over $100,000, Supervisor if salary is between $12,000 and $100,000, and Clerk if salary is less than or equal to $12,000:
If(salary > 100000, "Boss", If(salary > 12000, "Supervisor", "Clerk"))
In this example of a validation rule, the value the user should enter in the commission column depends on the price. If price is greater than or equal to 1000, then the commission is between .10 and .20. If price is less than 1000, then the commission must be between .04 and .09. The validation rule is:
(Number(GetText()) >= If(price >=1000, .10, .04)) AND (Number(GetText()) <= If(price >= 1000, .20, .09))
The accompanying error message expression might be:
"Price is " + If(price >= 1000, "greater than or equal to", "less than") + " 1000. Commission must be between " + If(price >= 1000, ".10", ".04") + " and " + If(price >= 1000, ".20.", ".09.")
Int expression function
Gets the largest whole number less than or equal to a number.
Syntax
Int ( n )
  • n
    — The number for which you want the largest whole number that is less than or equal to it
Return Values
The datatype of n. Returns the largest whole number less than or equal to n.
Examples
These expressions return 3.0:
Int(3.2) Int(3.8)
These expressions return -4.0:
Int(-3.2) Int(-3.8)
Integer expression function
Converts the value of a string to an integer.
Syntax
Integer ( string )
  • string
    — The string you want returned as an integer
Return Values
Integer. Returns the contents of string as an integer if it succeeds and 0 if string is not a number.
Examples
This expression converts the string 24 to an integer:
Integer("24")
This expression for a computed field returns "Not a valid age" if age does not contain a number. The expression checks whether the Integer function returns 0, which means it failed to convert the value:
If (Integer(age) <> 0, age, "Not a valid age")
This expression returns 0:
Integer("3ABC") // 3ABC is not a number
This validation rule checks that the value in the column the user entered is less than 100:
Integer(GetText()) < 100
This validation rule for the column named age insures that age contains a string:
Integer(age) <> 0
IsDate expression function
Tests whether a string value is a valid date.
Syntax
IsDate ( datevalue )
  • datevalue
    — A string whose value you want to test to determine whether it is a valid date
Return Values
Boolean. Returns true if datevalue is a valid date and false if it is not.
Examples
This expression returns true:
IsDate("Jan 1, 99")
This expression returns false:
IsDate("Jan 32, 2005")
This expression for a computed field returns a day number or 0. If the date_received column contains a valid date, the expression returns the number of the day in date_received in the computed field, and otherwise returns 0:
If(IsDate(String(date_received)),DayNumber(date_received), 0)
IsExpanded expression function
Tests whether a node in a TreeView DataWindow with the specified TreeView level and that includes the specified row is expanded.
Syntax
IsExpanded(long row, long level)
  • row
    — The number of the row that belongs to the node
  • level
    — The TreeView level of the node
Return Values
Returns true if the group is expanded and false otherwise.
Usage
A TreeView DataWindow has several TreeView level bands that can be expanded and collapsed. You can use the IsExpanded function to test whether or not a node in a TreeView DataWindow is expanded.
Examples
This expression returns true if the node that contains row 3 at TreeView level 2 is expanded:
IsExpanded(3,2)
IsNull expression function
Reports whether the value of a column or expression is null.
Syntax
IsNull ( any )
  • any
    A column or expression that you want to test to determine whether its value is null
Return Values
Boolean. Returns true if any is null and false if it is not.
Usage
Use IsNull to test whether a user-entered value or a value retrieved from the database is null.
Examples
This expression returns true if either a or b is null:
IsNull(a + b)
This expression returns true if the value in the salary column is null:
IsNull(salary)
This expression returns true if the value the user has entered is null:
IsNull(GetText())
IsNumber expression function
Reports whether the value of a string is a number.
Syntax
IsNumber ( string )
  • string
    — A string whose value you want to test to determine whether it is a valid number
Return Values
Boolean. Returns true if string is a valid number and false if it is not.
Examples
This expression returns true:
IsNumber("32.65")
This expression returns false:
IsNumber("A16")
This expression for a computed field returns "Not a valid age" if age does not contain a number:
If(IsNumber(age), age, "Not a valid age")
To pass this validation rule, Age_nbr must be a number:
IsNumber(Age_nbr) = true
IsRowModified expression function
Reports whether the row has been modified.
Syntax
IsRowModified ( )
Return Values
Boolean. Returns true if the row has been modified and false if it has not.
Usage
In a DataWindow object, when you use IsRowModified in bands other than the detail band, it reports on a row in the detail band. See GetRow for a table specifying which row is associated with each band for reporting purposes.
Examples
This expression in a computed field in the detail area displays true or false to indicate whether each row has been modified:
IsRowModified()
This expression defined in the Properties view for the Color property of the computed field displays the text (true) in red if the user has modified any value in the row:
If(IsRowModified(), 255, 0)
IsRowNew expression function
Reports whether the row has been newly inserted.
Syntax
IsRowNew ( )
Return Values
Boolean. Returns true if the row is new and false if it was retrieved from the database.
Usage
In a DataWindow object, when you call IsRowNew in bands other than the detail band, it reports on a row in the detail band. See GetRow for a table specifying which row is associated with each band for reporting purposes.
Examples
This expression defined in the Properties view for the Protect property of a column prevents the user from modifying the column unless the row has been newly inserted:
If(IsRowNew(), 0, 1)
IsSelected expression function
Determines whether the row is selected. A selected row is highlighted using reverse video.
Syntax
IsSelected ( )
Return Values
Boolean. Returns true if the row is selected and false if it is not selected.
Usage
When you use IsSelected in bands other than the detail band, it reports on a row in the detail band. See GetRow for a table specifying which row is associated with each band for reporting purposes.
Examples
This expression for a computed field in the detail area displays a bitmap if the row is selected:
Bitmap(If(IsSelected(), "beach.bmp", ""))
This example allows the DataWindow object to display a salary total for all the selected rows. The expression for a computed field in the detail band returns the salary only when the row is selected so that another computed field in the summary band can add up all the selected salaries.
The expression for cf_selected_salary (the computed field in the detail band) is:
If(IsSelected(), salary, 0)
The expression for the computed field in the summary band is:
Sum(cf_selected_salary for all)
IsTime expression function
Reports whether the value of a string is a valid time value.
Syntax
IsTime ( timevalue )
  • timevalue
    A string whose value you want to test to determine whether it is a valid time
Return Values
Boolean. Returns true if timevalue is a valid time and false if it is not.
Examples
This expression returns true:
IsTime("8:00:00 am")
This expression returns false:
IsTime("25:00")
To pass this validation rule, the value in
start_time
must be a time:
IsTime(start_time)
Large expression function
Finds a large value at a specified ranking in a column (for example, third- largest, fifth-largest) and returns the value of another column or expression based on the result.
Syntax
Large ( returnexp, column, ntop { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } )
  • returnexp
    — The value you want returned when the large value is found. Returnexp includes a reference to a column, but not necessarily the column that is being evaluated for the largest value, so that a value is returned from the same row that contains the large value.
  • column
    — The column that contains the large value you are searching for. Column can be a column name or a column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric.
  • ntop
    — The ranking of the large value in relation to the column's largest value. For example, when ntop is 2, Large finds the second-largest value.
  • FOR
    range
    — (optional) The data that will be included when the largest value is found. For most presentation styles, values for range are:
    • ALL
      — (Default) The largest of all values in column.
    • GROUP
      n
      — The largest of values in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1.
    • PAGE
      — The largest of the values in column on a page.
    • CROSSTAB
      — (Crosstabs only) The largest of all values in column in the crosstab.
    • GRAPH
      — (Graphs only) The largest of values in column in the range specified for the Rows option.
    • OBJECT
      — (OLE objects only) The largest of values in column in the range specified for the Rows option.
  • DISTINCT
    — (optional) Causes Large to consider only the distinct values in column when determining the large value. For a value of column, the first row found with the value is used and other rows that have the same value are ignored.
  • expresn
    — (optional) One or more expressions that you need to evaluate to determine distinct rows. Expresn can be the name of a column, a function, or an expression.
Return Values
The datatype of returnexp. Returns the ntop-largest value if it succeeds and –1 if an error occurs.
Usage
If you specify range, Large returns the value in returnexp when the value in column is the ntop-largest value in range. If you specify DISTINCT, Large returns returnexp when the value in column is the ntop-largest value of the distinct values in column, or if you specify expresn, the ntop-largest for each distinct value of expresn.
For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows are as follows:
  • For the Graph or OLE presentation style, Rows is always All
  • For Graph controls, Rows can be All, Page, or Group
  • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies
Tip:
If you do not need a return value from another column, and you want to find the largest value (ntop = 1), use Max(), it is faster.
You cannot use this or other aggregate functions in validation rules or filter expressions.
Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a DataWindow object always retrieves all rows.
Examples
These expressions return the names of the salespersons with the three largest sales (sum_sales is the sum of the sales for each salesperson) in group 2, which might be the salesregion group. Note that sum_sales contains the values being compared, but Large returns a value in the name column:
Large(name, sum_sales, 1 for group 2) Large(name, sum_sales, 2 for group 2) Large(name, sum_sales, 3 for group 2)
This example reports the salesperson with the third-largest sales, considering only the first entry for each person:
Large(name, sum_sales, 3 for all DISTINCT sum_sales)
Last expression function
Gets the value in the last row in the specified column.
Syntax
Last ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } )
  • column
    — The column for which you want the value of the last row. Column can be a column name or a column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column.
  • FOR
    range
    — (optional) The data that will be included when the value in the last row is found. For most presentation styles, values for range are:
    • ALL
      — (Default) The value in the last of all rows in column.
    • GROUP
      n
      — The value in the last row in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1.
    • PAGE
      — The value in the last row in column on a page.
    • CROSSTAB
      — (Crosstabs only) The value in the last row in column in the crosstab.
    • GRAPH
      — (Graphs only) The value in the last row in column in the range specified for the Rows option.
    • OBJECT
      — (OLE objects only) The value in the last row in column in the range specified for the Rows option.
  • DISTINCT
    — (optional) Causes Last to consider only the distinct values in column when determining the last value. For a value of column, the first row found with the value is used and other rows that have the same value are ignored.
  • expresn
    — (optional) One or more expressions that you want to evaluate to determine distinct rows. Expresn can be the name of a column, a function, or an expression.
Return Values
The datatype of the column. Returns the value in the last row of column. If you specify range, Last returns the value of the last row in column in range.
Usage
If you specify range, Last determines the value of the last row in column in range. If you specify DISTINCT, Last returns the last distinct value in column, or if you specify expresn, the last distinct value in column where the value of expresn is distinct.
For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include the following:
  • For the Graph or OLE presentation style, Rows is always All.
  • For Graph controls, Rows can be All, Page, or Group.
  • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies.
You cannot use this or other aggregate functions in validation rules or filter expressions.
Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a DataWindow object always retrieves all rows.
Examples
This expression returns the last distinct value in the column named dept_id in group 2:
Last(dept_id for group 2 DISTINCT)
This expression returns the last value in the column named emp_id in group 2:
Last(emp_id for group 2)
LastPos expression function
Finds the last position of a target string in a source string.
Syntax
LastPos ( string1, string2, searchlength )
  • string1
    The string in which you want to find string2.
  • string2
    The string you want to find in string1.
  • searchlength
    (optional) A long that limits the search to the leftmost
    searchlength
    characters of the source string
    string1
    . The default is the entire string.
Return Values
Long. Returns a long whose value is the starting position of the last occurrence of
string2
in
string1
within the characters specified in
searchlength
. If
string2
is not found in
string1
, or if
searchlength
is 0, LastPos returns 0. If any argument's value is null, LastPos returns null.
Usage
The LastPos function is case sensitive. The entire target string must be found in the source string.
Examples
This statement returns 8, because the position of the last occurrence of HI is position 8:
LastPos("CASTLE HILLS", "HI")
This statement returns 11:
LastPos("CASTLE HILLS", "L")
This statement returns 0, because the case does not match:
LastPos("CASTLE HILLS", "hi")
This statement searches the leftmost 6 characters and returns 0, because the only occurrence of HILL is after position 6:
LastPos("CASTLE HILLS", "HILL", 6)
Left expression function
Obtains a specified number of characters from the beginning of a string.
Syntax
Left ( string, n )
  • string
    — The string containing the characters you want
  • n
    — A long specifying the number of characters you want
Return Values
String. Returns the leftmost n characters in string if it succeeds and the empty string ("") if an error occurs.
If n is greater than or equal to the length of the string, Left returns the entire string. It does not add spaces to make the return value's length equal to n.
Examples
This expression returns CAST:
Left("CASTLE HILLS", 4)
This expression returns CASTLE HILLS:
Left("CASTLE HILLS", 40)
This expression for a computed field returns the first 40 characters of the text in the column home_address:
Left(home_address, 40)
LeftA expression function
Obtains a specified number of bytes from the beginning of a string.
Syntax
LeftA ( string, n )
  • string
    — The string containing the characters you want
  • n
    — A long specifying the number of bytes you want
Return Values
String. Returns the characters in the leftmost n bytes in string if it succeeds and the empty string ("") if an error occurs.
If n is greater than or equal to the length of the string, LeftA returns the entire string. It does not add spaces to make the return value's length equal to n.
Usage
LeftA replaces the functionality that Left had in DBCS environments in PowerBuilder 9. In SBCS environments, Left and LeftA return the same results.
LeftTrim expression function
Removes spaces from the beginning of a string.
Syntax
LeftTrim ( string )
  • string
    — The string you want returned with leading spaces deleted
Return Values
String. Returns a copy of string with leading spaces deleted if it succeeds and the empty string ("") if an error occurs.
Examples
This expression returns CASTLE:
LeftTrim(" CASTLE")
This expression for a computed field deletes any leading blanks from the value in the column lname and returns the value preceded by the salutation specified in salut_emp:
salut_emp + " " + LeftTrim(lname)
Len expression function
Reports the length of a string in characters.
Syntax
Len ( string )
  • string
    — The string for which you want the length
Return Values
Long. Returns a long containing the length of string in characters if it succeeds and –1 if an error occurs.
Examples
This expression returns 0:
Len("")
This validation rule tests that the value the user entered is fewer than 20 characters:
Len(GetText()) < 20
LenA expression function
Reports the length of a string in bytes.
Syntax
LenA ( string )
  • string
    — The string for which you want the length
Return Values
Long. Returns a long containing the length of string in bytes if it succeeds and –1 if an error occurs.
Usage
LenA replaces the functionality that Len had in DBCS environments in PowerBuilder 9. In SBCS environments, Len and LenA return the same results.
Log expression function
Gets the natural logarithm of a number. The inverse of the Log function is the Exp function.
Syntax
Log ( n )
  • n
    — The number for which you want the natural logarithm (base e). The value of n must be greater than 0.
Return Values
Double. Returns the natural logarithm of n. An execution error occurs if n is negative or zero.
Examples
This expression returns 2.302585092:
Log(10)
This expression returns -.693147 ... :
Log(0.5)
Both these expressions result in an error at runtime:
Log(0) Log(-2)
LogTen expression function
Gets the base 10 logarithm of a number. The expression 10^n is the inverse for LogTen(n). To obtain n given number (nbr = LogTen(n)), use n = 10^nbr.
Syntax
LogTen ( n )
  • n
    — The number for which you want the base 10 logarithm. The value of n must not be negative.
Return Values
Double. Returns the base 10 logarithm.
Examples
This expression returns 1:
LogTen(10)
The following expressions both return 0:
LogTen(1) LogTen(0)
This expression results in an execution error:
LogTen(–2)
Long expression function
Converts the value of a string to a long.
Syntax
Long ( string )
  • string
    The string you want returned as a long
Return Values
Long. Returns the contents of string as a long if it succeeds and 0 if string is not a valid number.
Examples
This expression returns 2167899876 as a long:
Long("2167899876")
LookUpDisplay expression function
Obtains the display value in the code table associated with the data value in the specified column.
Syntax
LookUpDisplay ( column )
  • column
    — The column for which you want the code table display value
Return Values
String. Returns the display value when it succeeds and the empty string ("") if an error occurs.
Usage
If a column has a code table, a buffer stores a value from the data column of the code table, but the user sees a value from the display column. Use LookUpDisplay to get the value the user sees.
When a column that is displayed in a graph has a code table, the graph displays the data values of the code table by default. To display the display values, call this function when you define the graph data.
Examples
This expression returns the display value for the column unit_measure:
LookUpDisplay(unit_measure)
Assume the column product_type has a code table and you want to use it as a category for a graph. To display the product type descriptions instead of the data values in the categories, enter this expression in the Category option on the Data page in the graph's property sheet:
LookUpDisplay(product_type)
Lower expression function
Converts all the characters in a string to lowercase.
Syntax
Lower ( string )
  • string
    — The string you want to convert to lowercase letters
Return Values
String. Returns string with uppercase letters changed to lowercase if it succeeds and the empty string ("") if an error occurs.
Examples
This expression returns castle hill:
Lower("Castle Hill")
Match expression function
Determines whether a string's value contains a particular pattern of characters.
Syntax
Match ( string, textpattern )
  • string
    — The string in which you want to look for a pattern of characters
  • textpattern
    — A string whose value is the text pattern
Return Values
Boolean. Returns true if string matches textpattern and false if it does not. Match also returns false if either argument has not been assigned a value or the pattern is invalid.
Usage
Match enables you to evaluate whether a string contains a general pattern of characters. To find out whether a string contains a specific substring, use the Pos function.
Textpattern is similar to a regular expression. It consists of metacharacters, which have special meaning, and ordinary characters, which match themselves. You can specify that the string begin or end with one or more characters from a set, or that it contain any characters except those in a set.
A text pattern consists of metacharacters, which have special meaning in the match string, and nonmetacharacters, which match the characters themselves.
The following tables explain the meaning and use of these metacharacters:
Metacharacter
Meaning
Example
Caret (^)
Matches the beginning of a string
^C matches C at the beginning of a string.
Dollar sign ($)
Matches the end of a string
s$ matches s at the end of a string.
Period (.)
Matches any character
. . . matches three consecutive characters.
Backslash (\)
Removes the following metacharacter's special characteristics so that it matches itself
\$ matches $.
Character class (a group of characters enclosed in square brackets [ ])
Matches any of the enclosed characters
[AEIOU] matches A, E, I, O, or U.
You can use hyphens to abbreviate ranges of characters in a character class. For example, [A-Za-z] matches any letter.
Complemented character class (first character inside the square brackets is a caret)
Matches any character
not
in the group following the caret
[^0-9] matches any character except a digit, and [^A-Za-z] matches any character except a letter.
The metacharacters asterisk (*), plus (+), and question mark (?) are unary operators that are used to specify repetitions in a regular expression:
Metacharacter
Meaning
Example
* (asterisk)
Indicates zero or more occurrences
A* matches zero or more As (no As, A, AA, AAA, and so on)
+ (plus)
Indicates one or more occurrences
A+ matches one A or more than one A (A, AAA, and so on)
? (question mark)
Indicates zero or one occurrence
A? matches an empty string ("") or A
Sample patterns
These text patterns match the following sample text:
  • AB
    — Any string that contains AB, such as ABA, DEABC, graphAB_one.
  • B*
    — Any string that contains 0 or more Bs, such as AC, B, BB, BBB, ABBBC, and so on. Since B* used alone matches any string, you would not use it alone, but notice its use in some the following examples.
  • AB*C
    — Any string containing the pattern AC or ABC or ABBC, and so on (0 or more Bs).
  • AB+C
    — Any string containing the pattern ABC or ABBC or ABBBC, and so on (1 or more Bs).
  • ABB*C
    — Any string containing the pattern ABC or ABBC or ABBBC, and so on (1 B plus 0 or more Bs).
  • ^AB
    — Any string starting with AB.
  • AB?C
    — Any string containing the pattern AC or ABC (0 or 1 B).
  • ^[ABC]
    — Any string starting with A, B, or C.
  • [^ABC]
    — A string containing any characters other than A, B, or C.
  • ^[^abc]
    — A string that begins with any character except a, b, or c.
  • ^[^a-z]$
    — Any single-character string that is not a lowercase letter (^ and $ indicate the beginning and end of the string).
  • [A-Z]+
    — Any string with one or more uppercase letters.
  • ^[0-9]+$
    — Any string consisting only of digits.
  • ^[0-9][0-9][0-9]$
    — Any string consisting of exactly three digits.
  • ^([0-9][0-9][0-9])$
    — Any string consisting of exactly three digits enclosed in parentheses.
Examples
This validation rule checks that the value the user entered begins with an uppercase letter. If the value of the expression is false, the data fails validation:
Match(GetText(), "^[A-Z]")
Max expression function
Gets the maximum value in the specified column.
Syntax
Max ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } )
  • column
    — The column for which you want the maximum value. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric.
  • FOR
    range
    — (optional) The data that will be included when the maximum value is found. For most presentation styles, values for range are:
    • ALL
      — (Default) The maximum value of all rows in column.
    • GROUP
      n
      — The maximum value of rows in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1.
    • PAGE
      — The maximum value of the rows in column on a page.
    • CROSSTAB
      — (Crosstabs only) The maximum value of all rows in column in the crosstab.
    • GRAPH
      — (Graphs only) The maximum value in column in the range specified for the Rows option.
    • OBJECT
      — (OLE objects only) The maximum value in column in the range specified for the Rows option.
  • DISTINCT
    — (optional) Causes Max to consider only the distinct values in column when determining the largest value. For a value of column, the first row found with the value is used and other rows that have the same value are ignored.
  • expresn
    — (optional) One or more expressions that you want to evaluate to determine distinct rows. Expresn can be the name of a column, a function, or an expression.
Return Values
The datatype of the column. Returns the maximum value in the rows of column. If you specify range, Max returns the maximum value in column in range.
Usage
If you specify range, Max determines the maximum value in column in range. If you specify DISTINCT, Max returns the maximum distinct value in column, or if you specify expresn, the maximum distinct value in column where the value of expresn is distinct.
For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include the following:
  • For the Graph or OLE presentation style, Rows is always All.
  • For Graph controls, Rows can be All, Page, or Group.
  • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies.
Null values are ignored and are not considered in determining the maximum.
You cannot use this or other aggregate functions in validation rules or filter expressions.
Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a DataWindow object always retrieves all rows.
Examples
This expression returns the maximum of the values in the age column on the page:
Max(age for page)
This expression returns the maximum of the values in column 3 on the page:
Max(#3 for page)
This expression returns the maximum of the values in the column named age in group 1:
Max(age for group 1)
Assuming a DataWindow object displays the order number, amount, and line items for each order, this computed field returns the maximum of the order amount for the distinct order numbers:
Max(order_amt for all DISTINCT order_nbr)
Median expression function
Calculates the median of the values of the column. The median is the middle value in the set of values, for which there is an equal number of values greater and smaller than it.
Syntax
Median ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } )
  • column
    — The column for which you want the median of the data values. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric.
  • FOR
    range
    — (optional) The data that will be included in the median. For most presentation styles, values for range are:
    • ALL
      — (Default) The median of all values in column.
    • GROUP
      n
      — The median of values in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1.
    • PAGE
      — The median of the values in column on a page.
    • CROSSTAB
      — (Crosstabs only) The median of all values in column in the crosstab.
    • GRAPH
      — (Graphs only) The median of values in column in the range specified for the Rows.
    • OBJECT
      — (OLE objects only) The median of values in column in the range specified for the Rows option.
  • DISTINCT
    — (optional) Causes Median to consider only the distinct values in column when determining the median. For a value of column, the first row found with the value is used and other rows that have the same value are ignored.
  • expresn
    — (optional) One or more expressions that you want to evaluate to determine distinct rows. Expresn can be the name of a column, a function, or an expression.
Return Values
The numeric datatype of the column. Returns the median of the values of the rows in range if it succeeds and –1 if an error occurs.
Usage
If you specify range, Median returns the median value of column in range. If you specify DISTINCT, Median returns the median value of the distinct values in column, or if you specify expresn, the median of column for each distinct value of expresn.
For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range.
Settings for Rows include the following:
  • For the Graph or OLE presentation style, Rows is always All.
  • For Graph controls, Rows can be All, Page, or Group.
  • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies.
In calculating the median, null values are ignored.
You cannot use this or other aggregate functions in validation rules or filter expressions.
Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a DataWindow object always retrieves all rows.
Examples
This expression returns the median of the values in the column named salary:
Median(salary)
This expression returns the median of the values in the column named salary of group 1:
Median(salary for group 1)
This expression returns the median of the values in column 5 on the current page:
Median(#5 for page)
This computed field returns Above Median if the median salary for the page is greater than the median for the report:
If(Median(salary for page) > Median(salary), "Above Median", " ")
This expression for a graph value sets the data value to the median value of the sale_price column:
Median(sale_price)
This expression for a graph value entered on the Data page in the graph's property sheet sets the data value to the median value of the sale_price column for the entire graph:
Median(sale_price for graph)
Assuming a DataWindow object displays the order number, amount, and line items for each order, this computed field returns the median of the order amount for the distinct order numbers:
Median(order_amt for all DISTINCT order_nbr)
Mid expression function
Obtains a specified number of characters from a specified position in a string.
Syntax
Mid ( string, start {, length } )
  • string
    The string from which you want characters returned.
  • start
    A long specifying the position of the first character you want returned (the position of the first character of the string is 1).
  • length
    (optional) A long whose value is the number of characters you want returned. If you do not enter length or if length is greater than the number of characters to the right of start, Mid returns the remaining characters in the string.
Return Values
String. Returns characters specified in length of string starting at character start. If start is greater than the number of characters in string, the Mid function returns the empty string (""). If length is greater than the number of characters remaining after the start character, Mid returns the remaining characters. The return string is not filled with spaces to make it the specified length.
Examples
This expression returns "":
Mid("CASTLE HILLS", 40, 5)
This expression returns "LE HILLS":
Mid("CASTLE HILLS", 5)
This expression in a computed field returns ACCESS DENIED if the fourth character in the column password is not R:
If(Mid(password, 4, 1) = "R", "ENTER", "ACCESS DENIED")
To pass this validation rule, the fourth character in the column password must be 6:
Mid(password, 4, 1) = "6"
MidA expression function
Obtains a specified number of bytes from a specified position in a string.
Syntax
MidA ( string, start {, length } )
  • string
    The string from which you want characters returned.
  • start
    A long specifying the position of the first byte you want returned (the position of the first byte of the string is 1).
  • length
    (optional) A long whose value is the number of bytes you want returned. If you do not enter length or if length is greater than the number of bytes to the right of start, MidA returns the remaining bytes in the string.
Return Values
String. Returns characters specified by the number of bytes in length of string starting at the byte specified by start. If start is greater than the number of bytes in string, the MidA function returns the empty string (""). If length is greater than the number of bytes remaining after the start byte, MidA returns the remaining bytes. The return string is not filled with spaces to make it the specified length.
Usage
MidA replaces the functionality that Mid had in DBCS environments in PowerBuilder 9. In SBCS environments, Mid and MidA return the same results.
Min expression function
Gets the minimum value in the specified column.
Syntax
Min ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } )
  • column
    The column for which you want the minimum value. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric.
  • FOR
    range
    — (optional) The data that will be included in the minimum. For most presentation styles, values for range are:
    • ALL
      — (Default) The minimum of all values in column.
    • GROUP n
      — The minimum of values in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1.
    • PAGE
      — The minimum of the values in column on a page.
    • CROSSTAB
      — (Crosstabs only) The minimum of all values in column in the crosstab.
    • GRAPH
      — (Graphs only) The minimum of values in column in the range specified for the Rows option.
    • OBJECT
      — (OLE objects only) The minimum of values in column in the range specified for the Rows option.
  • DISTINCT
    — (optional) Causes Min to consider only the distinct values in column when determining the minimum value. For a value of column, the first row found with the value is used and other rows that have the same value are ignored.
  • expresn
    (optional) One or more expressions that you want to evaluate to determine distinct rows. Expresn can be the name of a column, a function, or an expression.
Return Values
The datatype of the column. Returns the minimum value in the rows of column. If you specify range, Min returns the minimum value in the rows of column in range.
Usage
If you specify range, Min determines the minimum value in column in range. If you specify DISTINCT, Min returns the minimum distinct value in column, or if you specify expresn, the minimum distinct value in column where the value of expresn is distinct.
For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include:
  • For the Graph or OLE presentation style, Rows is always All.
  • For Graph controls, Rows can be All, Page, or Group.
  • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies.
Null values are ignored and are not considered in determining the minimum.
You cannot use this or other aggregate functions in validation rules or filter expressions.
Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a DataWindow object always retrieves all rows.
Examples
This expression returns the minimum value in the column named age in group 2:
Min(age for group 2)
This expression returns the minimum of the values in column 3 on the page:
Min(#3 for page)
Assuming a DataWindow object displays the order number, amount, and line items for each order, this computed field returns the minimum of the order amount for the distinct order numbers:
Min(order_amt for all DISTINCT order_nbr)
Minute expression function
Obtains the number of minutes in the minutes portion of a time value.
Syntax
Minute ( time )
  • time
    — The time value from which you want the minutes.
Return Values
Integer. Returns the minutes portion of time (00 to 59).
Examples
This expression returns 1:
Minute(19:01:31)
Mod expression function
Obtains the remainder (modulus) of a division operation.
Syntax
Mod ( x, y )
  • x
    — The number you want to divide by y
  • y
    — The number you want to divide into x
Return Values
The datatype of x or y, whichever datatype is more precise.
Examples
This expression returns 2:
Mod(20, 6)
This expression returns 1.5:
Mod(25.5, 4)
This expression returns 2.5:
Mod(25, 4.5)
Mode expression function
Calculates the mode of the values of the column. The mode is the most frequently occurring value.
Syntax
Mode ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } )
  • column
    — The column for which you want the mode of the data values. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric.
  • FOR
    range
    — (optional) The data that will be included in the mode. For most presentation styles, values for range are:
    • ALL
      — (Default) The mode of all values in column.
    • GROUP
      n
      — The mode of values in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1.
    • PAGE
      — The mode of the values in column on a page.
    • CROSSTAB
      — (Crosstabs only) The mode of all values in column in the crosstab.
    • GRAPH
      — (Graphs only) The mode of values in column in the range specified for the Rows option.
    • OBJECT
      — (OLE objects only) The mode of values in column in the range specified for the Rows option.
  • DISTINCT
    — (optional) Causes Mode to consider only the distinct values in column when determining the mode. For a value of column, the first row found with the value is used and other rows that have the same value are ignored.
  • expresn
    — (optional) One or more expressions that you want to evaluate to determine distinct rows. Expresn can be the name of a column, a function, or an expression.
Return Values
The numeric datatype of the column. Returns the mode of the values of the rows in range if it succeeds and –1 if an error occurs.
Usage
If you specify range, Mode returns the mode of column in range. If you specify DISTINCT, Mode returns the mode of the distinct values in column, or if you specify expresn, the mode of column for each distinct value of expresn.
For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include:
  • For the Graph or OLE presentation style, Rows is always All.
  • For Graph controls, Rows can be All, Page, or Group.
  • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies.
In calculating the mode, null values are ignored.
You cannot use this or other aggregate functions in validation rules or filter expressions.
Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a DataWindow object always retrieves all rows.
Examples
This expression returns the mode of the values in the column named salary:
Mode(salary)
This expression returns the mode of the values for group 1 in the column named salary:
Mode(salary for group 1)
This expression returns the mode of the values in column 5 on the current page:
Mode(#5 for page)
This computed field returns Above Mode if the mode of the salary for the page is greater than the mode for the report:
If(Mode(salary for page) > Mode(salary), "Above Mode", " ")
This expression for a graph value sets the data value to the mode of the sale_price column:
Mode(sale_price)
This expression for a graph value entered on the Data page in the graph's property sheet sets the data value to the mode of the sale_price column for the entire graph:
Mode(sale_price for graph)
Assuming a DataWindow object displays the order number, amount, and line items for each order, this computed field returns the mode of the order amount for the distinct order numbers:
Mode(order_amt for all DISTINCT order_nbr)
Month expression function
Gets the month of a date value.
Syntax
Month ( date )
  • date
    The date from which you want the month.
Return Values
Integer. Returns an integer (1 to 12) whose value is the month portion of date.
Examples
This expression returns 1:
Month(2005-01-31)
This expression for a computed column returns Wrong Month if the month in the column expected_grad_date is not 6:
If(Month(expected_grad_date) = 6, "June", "Wrong Month")
This validation rule expression checks that the value of the month in the date in the column expected_grad_date is 6:
Month(expected_grad_date) = 6
Now expression function
Obtains the current time based on the system time of the client machine.
Syntax
Now ( )
Return Values
Time. Returns the current time based on the system time of the client machine.
Usage
Use Now to compare a time to the system time or to display the system time on the screen. The timer interval specified for the DataWindow object determines the frequency at which the value of Now is updated. For example, if the timer interval is one second, it is updated every second. The default timer interval is one minute (60,000 milliseconds).
Examples
This expression returns the current system time:
Now()
This expression sets the column value to 8:00 when the current system time is before 8:00 and to the current time if it is after 8:00:
If(Now() < 08:00:00, '08:00:00', String(Now()))
The displayed time refreshes every time the specified time interval period elapses.
If a static value of time is required (for example, the time when a report has been executed or the retrieve has started), you can use a static text field that you modify as follows:
//Set the time when the report was executed in //the text field t_now dw1.Modify("t_now.text='"+ String(Now(),"hh:mm")+"'") //execute the report dw1.retrieve()
Number expression function
Converts a string to a number.
Syntax
Number ( string )
  • string
    The string you want returned as a number
Return Values
A numeric datatype. Returns the contents of string as a number. If string is not a valid number, Number returns 0.
Examples
This expression converts the string 24 to a number:
Number("24")
This expression for a computed field tests whether the value in the age column is greater than 55 and if so displays N/A; otherwise, it displays the value in age:
If(Number(age) > 55, "N/A", age)
This validation rule checks that the number the user entered is between 25,000 and 50,000:
Number(GetText())>25000 AND Number (GetText())<50000
Page expression function
Gets the number of the current page.
Syntax
Page ( )
Return Values
Long. Returns the number of the current page.
Usage
The vertical size of the paper less the top and bottom margins is used to calculate the page count. When the print orientation is landscape, the vertical size of the paper is the shorter dimension. If the DataWindow object is not set to print preview, then the size of the control determines the page number. When Page() is in the header, it uses the first row currently visible on the page to determine the page number. When it is in the footer, it uses the last row currently visible. Therefore, it is possible for the the values to be different.
Examples
This expression returns the number of the current page:
Page()
In the DataWindow object's footer band, this expression for a computed field displays a string showing the current page number and the total number of pages in the report. The result has the format "Page
n
of
total
":
'Page ' + Page() + ' of ' + PageCount()
PageAbs expression function
Gets the absolute number of the current page.
Syntax
PageAbs ( )
Return Values
Long. Returns the absolute number of the current page.
Usage
Use this function for group reports that have ResetPageCount = yes. It returns the absolute page number, ignoring the page reset count. This enables you to number the grouped pages, but also to obtain the absolute page when the user wants to print the current page, regardless of what that page number is in a grouped page report.
Examples
This expression returns the absolute number of the current page:
PageAbs()
This example obtains the absolute page number for the first row on the page in the string variable
ret
:
string ret, row row = dw1.Object.DataWindow.FirstRowOnPage ret = dw1.Describe("Evaluate('pageabs()', "+row+")")
PageAcross expression function
Gets the number of the current horizontal page. For example, if a report is twice the width of the print preview window and the window is scrolled horizontally to display the portion of the report that was outside the preview, PageAcross returns 2 because the current page is the second horizontal page.
Syntax
PageAcross ( )
Return Values
Long. Returns the number of the current horizontal page if it succeeds and –1 if an error occurs.
Examples
This expression returns the number of the current horizontal page:
PageAcross()
PageCount expression function
Gets the total number of pages when a DataWindow object is being viewed in Print Preview. This number is also the number of printed pages if the DataWindow object is not wider than the preview window. If the DataWindow object is wider than the preview window, the number of printed pages will be greater than the number PageCount gets.
Syntax
PageCount ( )
Return Values
Long. Returns the total number of pages.
Usage
PageCount applies to Print Preview. The vertical size of the paper less the top and bottom margins is used to calculate the page count. When the print orientation is landscape, the vertical size of the paper is the shorter dimension. If the DataWindow object is not set to print preview, then the size of the control determines the page count.
Examples
This expression returns the number of pages:
PageCount()
In the DataWindow object's footer band, this expression for a computed field displays a string showing the current page number and the total number of pages in the report. The result has the format "Page
n
of
total
":
'Page ' + Page() + ' of ' + PageCount()
PageCountAcross expression function
Gets the total number of horizontal pages that are wider than the Print Preview window when a DataWindow object is viewed in Print preview.
Syntax
PageCountAcross ( )
Return Values
Long. Returns the total number of horizontal pages if it succeeds and –1 if an error occurs.
Usage
PageCountAcross applies to Print Preview.
Examples
This expression returns the number of horizontal pages in the Print Preview window:
PageCountAcross()
Paint expression function
Takes a string expression argument and returns the same string, allowing you to paint inside a DataWindow object in a way that respect the position and z-order of other DataWindow objects.
Syntax
Paint ( expr )
  • expr
    — Any valid DataWindow expression. It should contain a function call to a drawing global function with rendering logic. If expr is a string expression and the value is not null, the computed field will render the evaluated string expression.
Return Values
String. The Paint expression function takes a string expression argument and returns the same string.
Examples
This example instantiates the drawing functions and, if the drawing function returns false, the text "No Pie" displays.
Paint
(
MyDrawPieSlice
(
GetPaintDC()
GetPaintRectX()
GetPaintRectY()
GetPaintRectWidth()
GetPaintRectHeight()
GetRow()*100/RowCount()
)
)
Paint
(
MyDrawPieSlice
(
GetPaintDC(),
GetRow()*100/RowCount()
)
)
Paint
(
if MyDrawPieSlice(GetPaintDC()),"","No Pie")
)
Percent expression function
Gets the percentage that the current value represents of the total of the values in the column.
Syntax
Percent ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } )
  • column
    — The column for which you want the value of each row expressed as a percentage of the total of the values of the column. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric.
  • FOR
    range
    — (optional) The data to be included in the percentage. For most presentation styles, values for range are:
    • ALL
      — (Default) The percentage that the current value represents of all rows in column.
    • GROUP
      n
      — The percentage that the current value represents of rows in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1.
    • PAGE
      — The percentage that the current value represents of the rows in column on a page.
    • CROSSTAB
      — (Crosstabs only) The percentage that the current value represents of all rows in column in the crosstab.
    • GRAPH
      — (Graphs only) The percentage that the current value represents of values in column in the range specified for the Rows option.
    • OBJECT
      — (OLE objects only) The percentage that the current value represents of values in column in the range specified for the Rows option.
  • DISTINCT
    — (optional) Causes Percent to consider only the distinct values in column when determining the percentage. For a value of column, the first row found with the value is used and other rows that have the same value are ignored.
  • expresn
    (optional) One or more expressions that you want to evaluate to determine distinct rows. Expresn can be the name of a column, a function, or an expression.
Return Values
A numeric datatype (decimal, double, integer, long, or real). Returns the percentage the current row of column represents of the total value of the column.
Usage
Usually you use Percent in a column to display the percentage for each row. You can also use Percent in a header or trailer for a group. In the header, Percent displays the percentage for the first value in the group, and in the trailer, for the last value in the group.
If you specify range, Percent returns the percentage that the current row of column represents relative to the total value of range. For example, if column 5 is salary, Percent(#5 for group 1) is equivalent to salary/(Sum(Salary for group 1)).
If you specify DISTINCT, Percent returns the percent that a distinct value in column represents of the total value of column. If you specify expresn, Percent returns the percent that the value in column represents of the total for column in a row in which the value of expresn is distinct.
The percentage is displayed as a decimal value unless you specify a format for the result. A display format can be part of the computed field's definition.
For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include the following:
  • For the Graph or OLE presentation style, Rows is always All.
  • For Graph controls, Rows can be All, Page, or Group.
  • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies.
Null values are ignored and are not considered in the calculation.
You cannot use Percent or other aggregate functions in validation rules or filter expressions. Percent does not work for crosstabs; specifying "for crosstab" as a range is not available for Percent.
Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a DataWindow object always retrieves all rows.
Examples
This expression returns the value of each row in the column named salary as a percentage of the total of salary:
Percent(salary)
This expression returns the value of each row in the column named cost as a percentage of the total of cost in group 2:
Percent(cost for group 2)
This expression entered in the Value box on the Data tab page in the Graph Object property sheet returns the value of each row in the qty_ordered as a percentage of the total for the column in the graph:
Percent(qty_ordered for graph)
Assuming a DataWindow object displays the order number, amount, and line items for each order, this computed field returns the order amount as a percentage of the total order amount for the distinct order numbers:
Percent(order_amt for all DISTINCT order_nbr)
Pi expression function
Multiplies pi by a specified number.
Syntax
Pi ( n )
  • n
    — The number you want to multiply by pi (3.14159265358979323...)
Return Values
Double. Returns the result of multiplying n by pi if it succeeds and –1 if an error occurs.
Usage
Use Pi to convert angles to and from radians.
Examples
This expression returns pi:
Pi(1)
Both these expressions return the area of a circle with the radius Rad:
Pi(1) * Rad^2 Pi(Rad^2)
This expression computes the cosine of a 45-degree angle:
Cos(45.0 * (Pi(2)/360))
Pos expression function
Finds one string within another string.
Syntax
Pos ( string1, string2 {, start } )
  • string1
    — The string in which you want to find string2.
  • string2
    — The string you want to find in string1.
  • start
    — (optional) A long indicating where the search will begin in string. The default is 1.
Return Values
Long. Returns a long whose value is the starting position of the first occurrence of string2 in string1 after the position specified in start. If string2 is not found in string1 or if start is not within string1, Pos returns 0.
Usage
The Pos function is case sensitive.
Examples
This expression returns the position of the letter a in the value of the last_name column:
Pos(last_name, "a")
This expression returns 8:
Pos("CASTLE HILLS", "HI")
This expression returns 3:
Pos("CASTLE HILLS", "S")
This expression returns 0 (because the case does not match):
Pos("CASTLE HILLS", "hi")
This expression returns 0 (because it starts searching at position 6, after the occurrence of CA):
Pos("CASTLE HILLS", "CA", 6)
PosA expression function
Finds one string within another string.
Syntax
PosA ( string1, string2 {, start } )
  • string1
    — The string in which you want to find string2.
  • string2
    — The string you want to find in string1.
  • start
    — (optional) A long indicating the position in bytes where the search will begin in string. The default is 1.
Return Values
Long. Returns a long whose value is the starting position of the first occurrence of string2 in string1 after the position in bytes specified in start. If string2 is not found in string1 or if start is not within string1, PosA returns 0.
Usage
PosA replaces the functionality that Pos had in DBCS environments in PowerBuilder 9. In SBCS environments, Pos and PosA return the same results.
ProfileInt expression function
Obtains the integer value of a setting in the specified profile file.
Syntax
ProfileInt ( filename, section, key, default )
  • filename
    —  A string whose value is the name of the profile file. If you do not specify a full path, ProfileInt uses the operating system's standard file search order to find the file.
  • section
    — A string whose value is the name of a group of related values in the profile file. In the file, section names are in square brackets. Do not include the brackets in section. Section is not case sensitive.
  • key
    — A string specifying the setting name in section whose value you want. The setting name is followed by an equal sign in the file. Do not include the equal sign in key. Key is not case sensitive.
  • default
    — An integer value that ProfileInt returns if filename is not found, if section or key does not exist in filename, or if the value of key cannot be converted to an integer.
Return Values
Integer. Returns default if filename is not found, section is not found in filename, key is not found in section, or the value of key is not an integer. Returns –1 if an error occurs.
Usage
Use ProfileInt and ProfileString to get configuration settings from a profile file you have designed for your application. ProfileInt and ProfileString can read files with ANSI or UTF16-LE encoding on Windows systems, and ANSI or UTF16–BE encoding on UNIX systems.
In PowerBuilder, you can use PowerScript SetProfileString to change values in the profile file to customize your application's configuration at runtime. Before you make changes, you can use ProfileInt and ProfileString to obtain the original settings so you can optionally restore them when the user exits the application.
Examples
This example uses the following PROFILE.INI file:
[MyApp]
Maximized=1
[Security]
Class = 7
This expression tries to return the integer value of the keyword Minimized in section MyApp of file C:\PROFILE.INI. It returns 3 if there is no MyApp section or no Minimized keyword in the MyApp section. Based on the sample file above, it returns 3:
ProfileInt("C:\PROFILE.INI", "MyApp", "minimized", 3)
ProfileString expression function
Obtains the string value of a setting in the specified profile file.
Syntax
ProfileString ( filename, section, key, default )
  • filename
    — A string whose value is the name of the profile file. If you do not specify a full path, ProfileString uses the operating system's standard file search order to find the file.
  • section
    — A string whose value is the name of a group of related values in the profile file. In the file, section names are in square brackets. Do not include the brackets in section. Section is not case sensitive.
  • key
    — A string specifying the setting name in section whose value you want. The setting name is followed by an equal sign in the file. Do not include the equal sign in key. Key is not case sensitive.
  • default
    — A string value that ProfileString returns if filename is not found, if section or key does not exist in filename, or if the value of key cannot be converted to an integer.
Return Values
String, with a maximum length of 4096 characters. Returns the string from key within section within filename. If filename is not found, section is not found in filename, or key is not found in section, ProfileString returns default. If an error occurs, it returns the empty string ("").
Usage
Use ProfileInt and ProfileString to get configuration settings from a profile file you have designed for your application. ProfileInt and ProfileString can read files with ANSI or UTF16-LE encoding on Windows systems, and ANSI or UTF16-BE encoding on UNIX systems.
In PowerBuilder, you can use PowerScript SetProfileString to change values in the profile file to customize your application's configuration at runtime. Before you make changes, you can use ProfileInt and ProfileString to obtain the original settings so you can optionally restore them when the user exits the application.
Examples
This example uses the following section in the PROFILE.INI file:
[Employee]
Name="Smith"
[Dept]
Name="Marketing"
This expression returns the string for the keyword Name in section Employee in file C:\PROFILE.INI. It returns None if the section or keyword does not exist. In this case it returns Smith:
ProfileString("C:\PROFILE.INI", "Employee", "Name", "None")
Rand expression function
Obtains a random whole number between 1 and a specified upper limit.
Syntax
Rand ( n )
  • n
    — The upper limit of the range of random numbers you want returned. The lower limit is always 1. The upper limit cannot exceed 32,767.
Return Values
A numeric datatype, the datatype of n. Returns a random whole number between 1 and n.
Usage
The sequence of numbers generated by repeated calls to the Rand function is a computer-generated pseudorandom sequence.
You can control whether the sequence is different each time your application runs by calling the PowerScript Randomize function to initialize the random number generator.
Examples
This expression returns a random whole number between 1 and 10:
Rand(10)
Real expression function
Converts a string value to a real datatype.
Syntax
Real ( string )
  • string
    — The string whose value you want to convert to a real
Return Values
Real. Returns the contents of a string as a real. If string is not a valid number, Real returns 0.
Examples
This expression converts 24 to a real:
Real("24")
This expression returns the value in the column temp_text as a real:
Real(temp_text)
RelativeDate expression function
Obtains the date that occurs a specified number of days after or before another date.
Syntax
RelativeDate ( date, n )
  • date
    — A date value
  • n
    — An integer indicating the number of days
Return Values
Date. Returns the date that occurs n days after date if n is greater than 0. Returns the date that occurs n days before date if n is less than 0.
Examples
This expression returns 2005-02-10:
RelativeDate(2005-01-31, 10)
This expression returns 2005-01-21:
RelativeDate(2005-01-31, -10)
RelativeTime expression function
Obtains a time that occurs a specified number of seconds after or before another time within a 24-hour period.
Syntax
RelativeTime ( time, n )
  • time
    — A time value
  • n
    — A long number of seconds
Return Values
Time. Returns the time that occurs n seconds after time if n is greater than 0. Returns the time that occurs n seconds before time if n is less than 0. The maximum return value is 23:59:59.
Examples
This expression returns 19:01:41:
RelativeTime(19:01:31, 10)
This expression returns 19:01:21:
RelativeTime(19:01:31, -10)
Replace expression function
Replaces a portion of one string with another.
Syntax
Replace ( string1, start, n, string2 )
  • string1
    — The string in which you want to replace characters with string2.
  • start
    — A long whose value is the number of the first character you want replaced. (The first character in the string is number 1.)
  • n
    — A long whose value is the number of characters you want to replace.
  • string2
    — The string that replaces characters in string1. The number of characters in string2 can be greater than, equal to, or fewer than the number of characters you are replacing.
Return Values
String. Returns the string with the characters replaced if it succeeds and the empty string ("") if it fails.
Usage
If the start position is beyond the end of the string, Replace appends string2 to string1. If there are fewer characters after the start position than specified in n, Replace replaces all the characters to the right of character start.
If n is zero, then in effect Replace inserts string2 into string1.
Examples
This expression changes the last two characters of the string David to e to make it Dave:
Replace("David", 4, 2, "e")
This expression returns MY HOUSE:
Replace("YOUR HOUSE", 1, 4, "MY")
This expression returns Closed for the Winter:
Replace("Closed for Vacation", 12, 8, "the Winter")
ReplaceA expression function
Replaces a portion of one string with another.
Syntax
ReplaceA ( string1, start, n, string2 )
  • string1
    — The string in which you want to replace bytes with string2.
  • start
    — A long whose value is the number of the first byte you want replaced. (The first byte in the string is number 1.)
  • n
    — A long whose value is the number of bytes you want to replace.
  • string2
    — The string that replaces bytes in string1. The number of bytes in string2 can be greater than, equal to, or fewer than the number of bytes you are replacing.
Return Values
String. Returns the string with the bytes replaced if it succeeds and the empty string ("") if it fails.
Usage
If the start position is beyond the end of the string, ReplaceA appends string2 to string1. If there are fewer bytes after the start position than specified in n, ReplaceA replaces all the bytes to the right of character start.
If n is zero, then in effect ReplaceA inserts string2 into string1.
ReplaceA replaces the functionality that Replace had in DBCS environments in PowerBuilder 9. In SBCS environments, Replace and ReplaceA return the same results.
RGB expression function
Calculates the long value that represents the color specified by numeric values for the red, green, and blue components of the color.
Syntax
RGB ( red, green, blue )
  • red
    — The integer value of the red component of the color
  • green
    — The integer value of the green component of the color
  • blue
    — The integer value of the blue component of the color
Return Values
Long. Returns the long that represents the color created by combining the values specified in red, green, and blue. If an error occurs, RGB returns null.
Usage
The formula for combining the colors is:
Red + (256 * Green) + (65536 * Blue)
Use RGB to obtain the long value required to set the color for text and drawing objects. You can also set an object's color to the long value that represents the color. The RGB function provides an easy way to calculate that value.
The value of a component color is an integer between 0 and 255 that represents the amount of the component that is required to create the color you want. The lower the value, the darker the color; the higher the value, the lighter the color.
The following table lists red, green, and blue values for the 16 standard colors:
Color
Red value
Green value
Blue value
Black
0
0
0
White
255
255
255
Light Gray
192
192
192
Dark Gray
128
128
128
Red
255
0
0
Dark Red
128
0
0
Green
0
255
0
Dark Green
0
128
0
Blue
0
0
255
Dark Blue
0
0
128
Magenta
255
0
255
Dark Magenta
128
0
128
Cyan
0
255
255
Dark Cyan
0
128
128
Yellow
255
255
0
Brown
128
128
0
Examples
This expression returns as a long 8421376, which represents dark cyan:
RGB(0,128,128)
This expression for the Background.Color property of a salary column returns a long that represents red if an employee's salary is greater than $50,000 and white if salary is less than or equal to $50,000:
If(salary>50000, RGB(255,0,0), RGB(255,255,255))
RichText expression function
Takes as argument a string expression interpreted as RTF and renders it as such. If the argument is not RTF nothing is rendered.
Syntax
RichText ( string )
  • string
    The string expression to render as RTF
Return Values
None.
Examples
This expression displays the contents of the short_desc column's as rich text.
RichText( short_desc )
RichTextFile expression function
Takes as argument a string expression interpreted as a RTF file name and renders the contents. If the argument is not a RTF file, nothing is rendered.
Syntax
RichTextFile ( string )
  • string
    The string expression to render as RTF file
Return Values
None.
Examples
This expression displays the contents of the richtext.rtf file as rich text.
RichTextFile("richtext.rtf")
Right expression function
Obtains a specified number of characters from the end of a string.
Syntax
Right ( string, n )
  • string
    The string from which you want characters returned
  • n
    — A long whose value is the number of characters you want returned from the right end of string
Return Values
String. Returns the rightmost n characters in string if it succeeds and the empty string ("") if an error occurs.
If n is greater than or equal to the length of the string, Right returns the entire string. It does not add spaces to make the return value's length equal to n.
Examples
This expression returns HILL:
Right("CASTLE HILL", 4)
This expression returns CASTLE HILL:
Right("CASTLE HILL", 75)
RightA expression function
Obtains a specified number of characters from the end of a string.
Syntax
Right ( string, n )
  • string
    The string from which you want characters returned
  • n
    — A long whose value is the number of characters you want returned from the right end of string
Return Values
String. Returns the rightmost n characters in string if it succeeds and the empty string ("") if an error occurs.
If n is greater than or equal to the length of the string, RightA returns the entire string. It does not add spaces to make the return value's length equal to n.
Usage
RightA replaces the functionality that Right had in DBCS environments in PowerBuilder 9. In SBCS environments, Right and RightA return the same results.
RightTrim expression function
Removes spaces from the end of a string.
Syntax
RightTrim ( string )
  • string
    The string you want returned with trailing blanks deleted
Return Values
String. Returns a copy of string with trailing blanks deleted if it succeeds and the empty string ("") if an error occurs.
Examples
This expression returns "CASTLE":
RightTrim("CASTLE ")
Round expression function
Rounds a number to the specified number of decimal places.
Syntax
Round ( x , n )
  • x
    — The number you want to round.
  • n
    — The number of decimal places to which you want to round x. Valid values are 0 through 28.
Return Values
Decimal. If n is positive, Round returns x rounded to the specified number of decimal places. If n is negative, it returns x rounded to (- n +1) places before the decimal point. Returns –1 if it fails.
Examples
This expression returns 9.62:
Round(9.624, 2)
This expression returns 9.63:
Round(9.625, 2)
This expression returns 9.600:
Round(9.6, 3)
This expression returns -9.63:
Round(-9.625, 2)
This expression returns -10:
Round(-9.625, -1)
RowCount expression function
Obtains the number of rows that are currently available in the primary buffer.
Syntax
RowCount ( )
Return Values
Long. Returns the number of rows that are currently available, 0 if no rows are currently available, and –1 if an error occurs.
Examples
This expression in a computed field returns a warning if no data exists and the number of rows if there is data:
If(RowCount() = 0, "No Data", String(RowCount()))
RowHeight expression function
Reports the height of a row associated with a band in a DataWindow object.
Syntax
RowHeight ( )
Return
Values
Long. Returns the height of the row in the units specified for the DataWindow object if it succeeds, and –1 if an error occurs.
Usage
When you call RowHeight in a band other than the detail band, it reports on a row in the detail band. See GetRow for a table specifying which row is associated with each band for reporting purposes.
When a band has Autosize Height set to true, you should avoid using the RowHeight DataWindow expression function to set the height of any element in the row. Doing so can result in a logical inconsistency between the height of the row and the height of the element. If you need to use RowHeight, you must set the Y coordinate of the element to 0 on the Position page in the Properties view, otherwise the bottom of the element might be clipped. You must do this for every element that uses such an expression. If you move any elements in the band, make sure that their Y coordinates are still set to 0.
You should not use an expression whose runtime value is greater than the value returned by RowHeight. For example, you should not set the height of a column to rowheight() + 30. Such an expression produces unpredictable results at runtime.
Examples
This expression for a computed field in the detail band displays the height of each row:
RowHeight()
Second expression function
Obtains the number of seconds in the seconds portion of a time value.
Syntax
Second ( time )
  • time
    The time value from which you want the seconds
Return Values
Integer. Returns the seconds portion of time (00 to 59).
Examples
This expression returns 31:
Second(19:01:31)
SecondsAfter expression function
Gets the number of seconds one time occurs after another.
Syntax
SecondsAfter ( time1, time2 )
  • time1
    — A time value that is the start time of the interval being measured
  • time2
    — A time value that is the end time of the interval
Return Values
Long. Returns the number of seconds time2 occurs after time1. If time2 occurs before time1, SecondsAfter returns a negative number.
Examples
This expression returns 15:
SecondsAfter(21:15:30, 21:15:45)
This expression returns -15:
SecondsAfter(21:15:45, 21:15:30)
This expression returns 0:
SecondsAfter(21:15:45, 21:15:45)
Sign expression function
Reports whether the number is negative, zero, or positive by checking its sign.
Syntax
Sign ( n )
  • n
    — The number for which you want to determine the sign.
Return Values
Integer. Returns a number (–1, 0, or 1) indicating the sign of n.
Examples
This expression returns 1 (the number is positive):
Sign(5)
This expression returns 0:
Sign(0)
This expression returns –1 (the number is negative):
Sign(-5)
Sin expression function
Calculates the sine of an angle.
Syntax
Sin ( n )
  • n
    — The angle (in radians) for which you want the sine.
Return Values
Double. Returns the sine of n if it succeeds and –1 if an error occurs.
Examples
This expression returns .8414709848078965:
Sin(1)
This expression returns 0:
Sin(0)
This expression returns 0:
Sin(pi(1))
Small expression function
Finds a small value at a specified ranking in a column (for example, third-smallest, fifth-smallest) and returns the value of another column or expression based on the result.
Syntax
Small ( returnexp, column, nbottom { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } )
  • returnexp
    The value you want returned when the small value is found. Returnexp includes a reference to a column, but not necessarily the column that is being evaluated for the small value, so that a value is returned from the same row that contains the small value.
  • column
    The column that contains the small value you are searching for. Column can be a column name or a column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric.
  • nbottom
    The relationship of the small value to the column's smallest value. For example, when nbottom is 2, Small finds the second-smallest value.
  • FOR
    range
    (optional) The data that will be included when finding the small value. For most presentation styles, values for range are:
    • ALL
      (Default) The small value of all rows in column.
    • GROUP n
      The small value of rows in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1.
    • PAGE
      The small value of the rows in column on a page.
    • CROSSTAB
      (Crosstabs only) The small value of all rows in column in the crosstab.
    • GRAPH
      (Graphs only) The small value in column in the range specified for the Rows option.
    • OBJECT
      (OLE objects only) The small value in column in the range specified for the Rows option.
  • DISTINCT
    (optional) Causes Small to consider only the distinct values in column when determining the small value. For a value of column, the first row found with the value is used and other rows that have the same value are ignored.
  • expresn
    (optional) One or more expressions that you want to evaluate to determine distinct rows. Expresn can be the name of a column, a function, or an expression.
Return Values
The datatype of returnexp. Returns the nbottom-smallest value if it succeeds and -1 if an error occurs.
Usage
If you specify range, Small returns the value in returnexp when the value in column is the nbottom-smallest value in range. If you specify DISTINCT, Small returns returnexp when the value in column is the nbottom-smallest value of the distinct values in column, or if you specify expresn, the nbottom-smallest for each distinct value of expresn.
For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range.
Settings for Rows include the following:
  • For the Graph or OLE presentation style, Rows is always All.
  • For Graph controls, Rows can be All, Page, or Group.
  • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies.
Tip:
If you do not need a return value from another column and you want to find the smallest value (nbottom = 1), use Min; it is faster.
You cannot use this or other aggregate functions in validation rules or filter expressions.
Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a DataWindow object always retrieves all rows.
Examples
These expressions return the names of the salespersons with the three smallest sales (sum_sales is the sum of the sales for each salesperson) in group 2, which might be the salesregion group. Note that sum_sales contains the values being compared, but Small returns a value in the name column:
Small(name, sum_sales, 1 for group 2) Small(name, sum_sales, 2 for group 2) Small(name, sum_sales, 3 for group 2)
This example reports the salesperson with the third-smallest sales, considering only the first entry for each salesperson:
Small(name, sum_sales, 3 for all DISTINCT sum_sales)
Space expression function
Builds a string of the specified length whose value consists of spaces.
Syntax
Space ( n )
  • n
    — A long whose value is the length of the string you want filled with spaces
Return Values
String. Returns a string filled with n spaces if it succeeds and the empty string ("") if an error occurs.
Examples
This expression for a computed field returns 10 spaces in the computed field if the value of the rating column is Top Secret; otherwise, it returns the value in rating:
If(rating = "Top Secret", Space(10), rating)
Sqrt expression function
Calculates the square root of a number.
Syntax
Sqrt ( n )
  • n
    — The number for which you want the square root.
Return Values
Double. Returns the square root of n.
Usage
Sqrt(n) is the same as n ^.5. Taking the square root of a negative number causes an execution error.
Examples
This expression returns 1.414213562373095:
Sqrt(2)
This expression results in an error at execution time:
Sqrt(-2)
StDev expression function
Calculates an estimate of the standard deviation for the specified column. Standard deviation is a measurement of how widely values vary from average.
Syntax
StDev ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } )
  • column
    The column for which you want an estimate for the standard deviation of the values in the rows. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric.
  • FOR range
    (optional) The data to be included in the estimate of the standard deviation. For most presentation styles, values for range are:
    • ALL
      (Default) The estimate of the standard deviation for all values in column.
    • GROUP
      n
      The estimate of the standard deviation for values in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1.
    • PAGE
      The estimate of the standard deviation for the values in column on a page.
    • CROSSTAB
      (Crosstabs only) The standard deviation for all values in column in the crosstab.
    • GRAPH
      (Graphs only) The standard deviation in column in the range specified for the Rows option.
    • OBJECT
      (OLE objects only) The standard deviation in column in the range specified for the Rows option.
  • DISTINCT
    (optional) Causes StDev to consider only the distinct values in column when determining the standard deviation. For a value of column, the first row found with the value is used and other rows that have the same value are ignored.
  • expresn
    (optional) One or more expressions that you want to evaluate to determine distinct rows. Expresn can be the name of a column, a function, or an expression.
Return Values
Double. Returns an estimate of the standard deviation for column.
Usage
If you specify range, StDev returns an estimate for the standard deviation of column within range. If you specify DISTINCT, StDev returns an estimate of the standard deviation for the distinct values in column, or if you specify expresn, the estimate of the standard deviation of the rows in column where the value of expresn is distinct.
For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data tab page (the Range property), and the aggregation function uses that range. Settings for Rows include the following:
  • For the Graph or OLE presentation style, Rows is always All.
  • For Graph controls, Rows can be All, Page, or Group.
  • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies.
When estimating or calculating actual standard deviation, StDev assumes that the values in column are a sample of the values in the rows in the column in the database table. If you selected all the rows in the column in the DataWindow object's SELECT statement, use StDevP to compute the standard deviation of the population.
You cannot use this or other aggregate functions in validation rules or filter expressions. Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a DataWindow object always retrieves all rows.
Examples
These examples all assume that the SELECT statement did not retrieve all the rows in the database table. StDev is intended to work with a subset of rows, which is a sample of the full set of data.
This expression returns an estimate for standard deviation of the values in the column named salary:
StDev(salary)
This expression returns an estimate for standard deviation of the values in the column named salary in group 1:
StDev(salary for group 1)
This expression returns an estimate for standard deviation of the values in column 4 on the page:
StDev(#4 for page)
This expression entered in the Value box on the Data tab page in the graph's property sheet returns an estimate for standard deviation of the values in the qty_used column in the graph:
StDev(qty_used for graph)
This expression for a computed field in a crosstab returns the estimate for standard deviation of the values in the qty_ordered column in the crosstab:
StDev(qty_ordered for crosstab)
Assuming a DataWindow object displays the order number, amount, and line items for each order, this computed field returns the estimated standard deviation of the order amount for the distinct order numbers:
StDev(order_amt for all DISTINCT order_nbr)
StDevP expression function
Calculates the standard deviation for the specified column. Standard deviation is a measurement of how widely values vary from average.
Syntax
StDevP ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } )
  • column
    The column for which you want the standard deviation of the values in the rows. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric.
  • FOR range
    — (optional) The data to be included in the standard deviation. For most presentation styles, values for range are:
    • ALL
      — (Default) The standard deviation for all values in column.
    • GROUP
      n
      — The standard deviation for values in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1.
    • PAGE
      — The standard deviation for the values in column on a page.
    • CROSSTAB
      (Crosstabs only) The standard deviation for all values in column in the crosstab.
    • GRAPH
      (Graphs only) The standard deviation for values in column in the range specified for the Rows option.
    • OBJECT
      (OLE objects only) The standard deviation for values in column in the range specified for the Rows option.
  • DISTINCT
    — (optional) Causes StDevP to consider only the distinct values in column when determining the standard deviation. For a value of column, the first row found with the value is used and other rows that have the same value are ignored.
  • expresn
    — (optional) One or more expressions that you want to evaluate to determine distinct rows. Expresn can be the name of a column, a function, or an expression.
Return Values
Double. Returns the standard deviation for column.
Usage
If you specify range, StDevP returns the standard deviation for column within range. If you specify DISTINCT, StDevP returns an estimate of the standard deviation for the distinct values in column, or if you specify expresn, the estimate of the standard deviation of the rows in column where the value of expresn is distinct.
For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data tab page (the Range property), and the aggregation function uses that range. Settings for Rows include the following:
  • For the Graph or OLE presentation style, Rows is always All.
  • For Graph controls, Rows can be All, Page, or Group.
  • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies.
When estimating or calculating actual standard deviation, StDevP assumes that the values in
column
are the values in all the rows in the column in the database table. If you did not select all rows in the column in the SELECT statement, use StDev to compute an estimate of the standard deviation of a sample.
You cannot use this or other aggregate functions in validation rules or filter expressions. Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a DataWindow object always retrieves all rows.
Examples
These examples all assume that the SELECT statement retrieved all rows in the database table. StDevP is intended to work with a full set of data, not a subset.
This expression returns the standard deviation of the values in the column named salary:
StDevP(salary)
This expression returns the standard deviation of the values in group 1 in the column named salary:
StDevP(salary for group 1)
This expression returns the standard deviation of the values in column 4 on the page:
StDevP(#4 for page)
This expression entered in the Value box on the Data tab page in the graph's property sheet returns the standard deviation of the values in the qty_ordered column in the graph:
StDevP(qty_ordered for graph)
This expression for a computed field in a crosstab returns the standard deviation of the values in the qty_ordered column in the crosstab:
StDevP(qty_ordered for crosstab)
Assuming a DataWindow object displays the order number, amount, and line items for each order, this computed field returns the standard deviation of the order amount for the distinct order numbers:
StDevP(order_amt for all DISTINCT order_nbr)
String expression function
Formats data as a string according to a specified display format mask. You can convert and format date, DateTime, numeric, and time data. You can also apply a display format to a string.
Syntax
String ( data {, format } )
  • data
    — The data you want returned as a string with the specified formatting. Data can have a date, DateTime, numeric, time, or string datatype.
  • format
    — (optional) A string of the display masks you want to use to format the data. The masks consist of formatting information specific to the datatype of data. If data is type string, format is required.
    The format string can consist of more than one mask, depending on the datatype of data. Each mask is separated by a semicolon. See Usage for details on each datatype.
Return Values
String. Returns data in the specified format if it succeeds and the empty string ("") if the datatype of data does not match the type of display mask specified or format is not a valid mask.
Usage
For date, DateTime, numeric, and time data, the system's default format is used for the returned string if you do not specify a format. For numeric data, the default format is the [General] format.
For string data, a display format mask is required. (Otherwise, the function would have nothing to do.)
The format can consist of one or more masks:
  • Formats for date, DateTime, string, and time data can include one or two masks. The first mask is the format for the data; the second mask is the format for a null value.
  • Formats for numeric data can have up to four masks. A format with a single mask handles both positive and negative data. If there are additional masks, the first mask is for positive values, and the additional masks are for negative, zero, and null values.
A format can include color specifications.
If the display format does not match the datatype, the attempt to apply the mask produces unpredictable results. For information on specifying display formats, consult the PowerBuilder documentation.
When you use String to format a date and the month is displayed as text (for example, when the display format includes "mmm"), the month is in the language of the deployment files available when the application is run. If you have installed localized files in the development environment or on a user's machine, then on that machine the month in the resulting string will be in the language of the localized files.
For information about localized deployment files, see the chapter on internationalizing an application in Application Techniques.
Examples
This expression returns Jan 31, 2005:
String(2005-01-31, "mmm dd, yyyy")
This expression returns Jan 31, 2005 6 hrs and 8 min:
String(2005-01-31 06:08:00, 'mmm dd, yyyy, h "hrs and" m "min"')
This expression:
String(nbr, "0000;(000);****;empty")
returns:
  • 0123 if nbr is 123
  • (123) if nbr is -123
  • **** if nbr is 0
  • empty if nbr is null
This expression returns A-B-C:
String("ABC", "@[email protected]@")
This expression returns A*B:
String("ABC", "@*@")
This expression returns ABC:
String("ABC", "@@@")
This expression returns a space:
String("ABC", " ")
This expression returns 6 hrs and 8 min:
String(06:08:02,'h "hrs and" m "min"')
This expression returns 08:06:04 pm:
String(20:06:04, "hh:mm:ss am/pm")
This expression returns 8:06:04 am:
String(08:06:04, "h:mm:ss am/pm")
This expression returns 6:11:25.300000:
String(6:11:25.300000, "h:mm:ss.ffffff")
StripRTF expression function
Removes the rich text formatting from the specified column
Syntax
StripRTF ( string )
  • string
    — The column to be stripped of rich text formatting.
Examples
This expression is used in a compute field expression to remove the formatting from a rich text edit column and display plain text in the compute field.
StripRTF(rte_description)
Sum expression function
Calculates the sum of the values in the specified column.
Syntax
Sum ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } )
  • column
    The column for which you want the sum of the data values. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric.
  • FOR
    range
    (optional) The data to be included in the sum. For most presentation styles, values for range are:
    • ALL
      (Default) The sum of all values in column.
    • GROUP n
      The sum of values in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1.
    • PAGE
      The sum of the values in column on a page.
    • CROSSTAB
      (Crosstabs only) The sum of all values in column in the crosstab.
    • GRAPH
      (Graphs only) The sum of values in column in the range specified for the Rows option of the graph.
    • OBJECT
      (OLE objects only) The sum of values in column in the range specified for the Rows option of the OLE object.
  • DISTINCT
    (optional) Causes Sum to consider only the distinct values in column when determining the sum. For a value of column, the first row found with the value is used and other rows that have the same value are ignored.
  • expresn
    (optional) One or more expressions that you want to evaluate to determine distinct rows. Expresn can be the name of a column, a function, or an expression.
Return Values
The appropriate numeric datatype. Returns the sum of the data values in column.
Usage
If you specify range, Sum returns the sum of the values in column within range. If you specify DISTINCT, Sum returns the sum of the distinct values in column, or if you specify expresn, the sum of the values of column where the value of expresn is distinct.
For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include the following:
  • For the Graph or OLE presentation style, Rows is always All.
  • For Graph controls, Rows can be All, Page, or Group.
  • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies.
Null values are ignored and are not included in the calculation.
You cannot use this or other aggregate functions in validation rules or filter expressions.
Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a DataWindow object always retrieves all rows.
Examples
This expression returns the sum of the values in group 1 in the column named salary:
Sum(salary for group 1)
This expression returns the sum of the values in column 4 on the page:
Sum(#4 for page)
Assuming a DataWindow object displays the order number, amount, and line items for each order, this computed field returns the sum of the order amount for the distinct order numbers:
Sum(order_amt for all DISTINCT order_nbr)
Tan expression function
Calculates the tangent of an angle.
Syntax
Tan ( n )
  • n
    — The angle (in radians) for which you want the tangent
Return Values
Double. Returns the tangent of n if it succeeds and –1 if an error occurs.
Examples
Both these expressions return 0:
Tan(0)
Tan(Pi(1))
This expression returns 1.55741:
Tan(1)
Time expression function
Converts a string to a time datatype.
Syntax
Time ( string )
  • string
    — A string containing a valid time (such as 8 am or 10:25) that you want returned as a time datatype. Only the hour is required; you do not have to include the minutes, seconds, or microseconds of the time or am or pm. The default value for minutes and seconds is 00 and for microseconds is 000000. am or pm is determined automatically.
Return Values
Time. Returns the time in string as a time datatype. If string does not contain a valid time, Time returns 00:00:00.
Examples
This expression returns the time datatype for 45 seconds before midnight (23:59:15):
Time("23:59:15")
This expression for a computed field returns the value in the time_received column as a value of type time if time_received is not the empty string. Otherwise, it returns 00:00:00:
If(time_received = "", 00:00:00, Time(time_received))
This example is similar to the previous one, except that it returns 00:00:00 if time_received contains a null value:
If(IsNull(time_received), 00:00:00, Time(time_received))
Today expression function
Obtains the system date and time.
Syntax
Today ( )
Return Values
DateTime. Returns the current system date and time.
Usage
To display both the date and the time, a computed field must have a display format that includes the time.
The PowerScript and DataWindow painter versions of the Today function have different datatypes. The return value of the PowerScript Today function is date.
Examples
This expression for a computed field displays the date and time when the display format for the field is "mm/dd/yy hh:mm":
Today()
Trim expression function
Removes leading and trailing spaces from a string.
Syntax
Trim ( string )
string The string you want returned with leading and trailing spaces deleted
Return Values
String. Returns a copy of string with all leading and trailing spaces deleted if it succeeds and the empty string ("") if an error occurs.
Usage
Trim is useful for removing spaces that a user might have typed before or after newly entered data.
Examples
This expression returns "CASTLE HILLS":
Trim(" CASTLE HILLS ")
Truncate expression function
Truncates a number to the specified number of decimal places.
Syntax
Truncate ( x, n )
  • x
    — The number you want to truncate.
  • n
    — The number of decimal places to which you want to truncate x. Valid values are 0 through 28.
Return Values
The datatype of x. If n is positive, returns x truncated to the specified number of decimal places. If n is negative, returns x truncated to (- n +1) places before the decimal point. Returns –1 if it fails.
Examples
This expression returns 9.2:
Truncate(9.22, 1)
This expression returns 9.2:
Truncate(9.28, 1)
This expression returns 9:
Truncate(9.9, 0)
This expression returns -9.2:
Truncate(–9.29, 1)
This expression returns 0:
Truncate(9.2, –1)
This expression returns 50:
Truncate(54, –1)
Upper expression function
Converts all characters in a string to uppercase letters.
Syntax
Upper ( string )
  • string
    — The string you want to convert to uppercase letters
Return Values
String. Returns string with lowercase letters changed to uppercase if it succeeds and the empty string ("") if an error occurs.
Examples
This expression returns "CASTLE HILLS":
Upper("Castle Hills")
Var expression function
Calculates an estimate of the variance for the specified column. The variance is the square of the standard deviation.
Syntax
Var ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } )
  • column
    The column for which you want an estimate for the variance of the values in the rows. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric.
  • FOR
    range
    (optional) The data to be included in the estimate of the variance. For most presentation styles, values for range are:
    • ALL
      (Default) The estimate of the variance for all rows in column.
    • GROUP
      n
      The estimate of the variance for rows in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1.
    • PAGE
      The estimate of the variance for the rows in column on a page.
    • CROSSTAB
      (Crosstabs only) The estimate of the variance for all rows in column in the crosstab.
    • GRAPH
      (Graphs only) The estimate of the variance for rows in column in the range specified for the Rows option.
    • OBJECT
      (OLE objects only) The estimate of the variance for rows in column in the range specified for the Rows option.
  • DISTINCT
    (optional) Causes Var to consider only the distinct values in column when determining the variance. For a value of column, the first row found with the value is used and other rows that have the same value are ignored.
  • expresn
    (optional) One or more expressions that you want to evaluate to determine distinct rows. Expresn can be the name of a column, a function, or an expression.
Return Values
Double or decimal if the arguments are decimal. Returns an estimate for the variance for column. If you specify group, Var returns an estimate for the variance for column within group.
Usage
If you specify range, Var returns an estimate for the variance for column within range. If you specify DISTINCT, Var returns the variance for the distinct values in column, or if you specify expresn, the estimate for the variance of the rows in column where the value of expresn is distinct.
For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range.
Settings for Rows include the following:
  • For the Graph or OLE presentation style, Rows is always All.
  • For Graph controls, Rows can be All, Page, or Group.
  • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies.
When estimating variance or calculating actual variance, Var assumes that the values in column are a sample of the values in rows in the column in the database table. If you select all rows in the column in the SELECT statement, use VarP to compute the variance of a population.
You cannot use this or other aggregate functions in validation rules or filter expressions. Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a DataWindow object always retrieves all rows.
Examples
These examples all assume that the SELECT statement did not retrieve all of the rows in the database table. Var is intended to work with a subset of rows, which is a sample of the full set of data.
This expression returns an estimate for the variance of the values in the column named salary:
Var(salary)
This expression returns an estimate for the variance of the values in the column named salary in group 1:
Var(salary for group 1)
This expression entered in the Value box on the Data property page in the graph's property sheet returns an estimate for the variance of the values in the quantity column in the graph:
Var(quantity for graph)
This expression for a computed field in a crosstab returns an estimate for the variance of the values in the quantity column in the crosstab:
Var(quantity for crosstab)
Assuming a DataWindow object displays the order number, amount, and line items for each order, this computed field returns the estimate for the variance of the order amount for the distinct order numbers:
Var(order_amt for all DISTINCT order_nbr)
VarP expression function
Calculates the variance for the specified column. The variance is the square of the standard deviation.
Syntax
VarP ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } )
  • column
    The column for which you want the variance of the values in the rows. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric.
  • FOR
    range
    (optional) The data that will be included in the variance. For most presentation styles, values for range are:
    • ALL
      (Default) The variance for all rows in column.
    • GROUP
      n
      The variance for rows in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1.
    • PAGE
      The variance for the rows in column on a page.
    • CROSSTAB
      (Crosstabs only) The variance for all rows in column in the crosstab.
    • GRAPH
      (Graphs only) The variance for rows in column in the range specified for the Rows option.
    • OBJECT
      (OLE objects only) The variance for rows in column in the range specified for the Rows option.
  • DISTINCT
    (optional) Causes VarP to consider only the distinct values in column when determining the variance. For a value of column, the first row found with the value is used and other rows that have the same value are ignored.
  • expresn
    (optional) One or more expressions that you want to evaluate to determine distinct rows. Expresn can be the name of a column, a function, or an expression.
Return Values
Double or decimal if the arguments are decimal. Returns the variance for column. If you specify group, Var returns the variance for column within range.
Usage
If you specify range, VarP returns the variance for column within range. If you specify DISTINCT, VarP returns the variance for the distinct values in column, or if you specify expresn, the variance of the rows in column where the value of expresn is distinct.
For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include the following:
  • For the Graph or OLE presentation style, Rows is always All.
  • For Graph controls, Rows can be All, Page, or Group.
  • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies.
When estimating variance or calculating actual variance, VarP assumes that the values in column are the values in all rows in the column in the database table. If you did not select all the rows in the column in the SELECT statement, use Var to compute an estimate of the variance of a sample.
You cannot use this or other aggregate functions in validation rules or filter expressions. Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a DataWindow object always retrieves all rows.
Examples
These examples all assume that the SELECT statement retrieved all rows in the database table. VarP is intended to work with a full set of data, not a subset.
This expression returns the variance of the values in the column named salary:
VarP(salary)
This expression returns the variance of the values in group 1 in the column named salary:
VarP(salary for group 1)
This expression returns the variance of the values in column 4 on the page:
VarP(#4 for page)
This expression entered in the Value box on the Data property page in the graph's property sheet returns the variance of the values in the quantity column in the graph:
VarP(quantity for graph)
This expression for a computed field in a crosstab returns the variance of the values in the quantity column in the crosstab:
VarP(quantity for crosstab)
Assuming a DataWindow object displays the order number, amount, and line items for each order, this computed field returns the variance of the order amount for the distinct order numbers:
VarP(order_amt for all DISTINCT order_nbr)
WordCap expression function
Sets the first letter of each word in a string to a capital letter and all other letters to lowercase (for example, ROBERT E. LEE would be Robert E. Lee).
Syntax
WordCap ( string )
  • string
    A string or expression that evaluates to a string that you want to display with initial capital letters (for example, Monday Morning)
Return Values
String. Returns string with the first letter of each word set to uppercase and the remaining letters lowercase if it succeeds, and null if an error occurs.
Examples
This expression returns "Boston, Massachusetts":
WordCap("boston, MASSACHUSETTS")
This expression concatenates the characters in the emp_fname and emp_lname columns and makes the first letter of each word uppercase:
WordCap(emp_fname + " " + emp_lname)
Year expression function
Gets the year of a date value.
Syntax
Year ( date )
  • date
    The date value from which you want the year
Return Values
Integer. Returns an integer whose value is a 4-digit year adapted from the year portion of date if it succeeds and 1900 if an error occurs.
If the year is two digits, then the century is set as follows. If the year is between 00 to 49, the first two digits are 20; if the year is between 50 and 99, the first two digits are 19.
Usage
Obtains the year portion of date. Years from 1000 to 3000 inclusive are handled.
If your data includes dates before 1950, such as birth dates, always specify a 4–digit year so that Year (and other functions, such as Sort) interpret the date as intended.
To make sure you get correct return values for the year, you must verify that yyyy is the Short Date Style for year in the Regional Settings of the user's Control Panel. Your program can check this with the RegistryGet function. If the setting is not correct, you can ask the user to change it manually or to have the application change it (by calling the RegistrySet function). The user might need to reboot after the setting is changed.
Examples
This expression returns 2005:
Year(2005-01-31)