Using Procedures as Expressions
When a procedure returns a value such as a return code, it functions as an expression, as in the example below:
returnval := procname();
Procname is the name of a procedure that returns a value. If you are not passing any parameters to the procedure, use an empty set of parentheses, as shown above.
A procedure can also be used in an expression. A procedure named mod() is used in the following example:
if mod(flag/256, 2) = 1 then
message 'bit 8 set'
endif
System Functions
4GL provides a set of implicit system functions for use in your applications. These include the ifnull function and the following scalar functions (that is, functions that accept single-valued expressions, rather than sets, as their arguments):
Type conversion functions
The following functions set the data type of an expression: c, char, date, decimal, dow, float4, float8, hex, int1, int2, int4, money, numeric, text, varchar, table_key, object_key
Numeric functions
The following functions perform mathematical operations on an expression: abs, atan, cos, exp, log, mod, sin, sqrt
String functions
The following functions operate on character strings: concat, left, length, locate, lowercase, pad, right, shift, size, squeeze, trim, notrim, uppercase, charextract
Date functions
The following functions operate on date values: date_trunc, date_part, date_gmt, interval, date, _time
The following examples demonstrate some of the ways you can use system functions.
• Convert a number in the integer variable "age" to a character string so that the value can be used in a message statement:
message 'average age is: ' + char(:age);
• Retrieve the current date:
curdate := date('now');
• Calculate the number of days between a date and today:
number_of_days := interval ('days',
start_date - 'today');
• To return into a varchar variable the current time on the machine on which an application is running (the time is expressed as the number of seconds since January 1, 1970):
var = varchar(int4(interval('sec',
'now'-date('1970.01.01 00:00:00gmt'))));
If the function returns an error, the variable is set to NULL. If you use a function to assign a value to a variable, use error checking to ensure that the function returned a valid value.
See the SQL Reference Guide for detailed descriptions of the system functions listed above.
You can use the aggregate functions (that is, functions that operate on a column of values, such as count) only within a database access statement, such as a select. This restriction also applies to the dbmsinfo function that retrieves system information. See Using the Dbmsinfo Function for more information about using the dbmsinfo function.
ABF displays a warning message if you define a procedure with the same name as a system function. If you decide to override the system function name, your procedure overrides the system function in the current application.
This is not the case with query expressions. System functions are the only procedures recognized inside query expressions. This means that a system function overrides your procedure only within a query expression.