CHAPTER 10: Functions

Empress functions can be divided into Aggregate Functions, Build-In Functions, Math Library Functions, User Defined Functions and User Defined Aggregate Functions.

The Aggregate Functions are COUNT, MAX, MIN, SUM, AVG.

The Build-In Functions are listed below:

The Math Library Functions are vary with the system being used. They will be a subset of the following:

The User Defined Functions and User Defined Aggregrate Functions are functions defined in accordance with the details specified in the Empress: User Defined Functions manual.

There are five functions which may be applied to groups of records in a SELECT command or expression: COUNT, MAX, MIN, SUM and AVG. These functions are described in the following subsections.

Using the COUNT function in a SELECT or expression gives the number of records in each group. If there is no GROUP BY clause in the command, all the records selected form a group and the function returns the number of records selected.

The COUNT function may appear in the list of items to be selected and in the HAVING clause of the command.

Syntax

COUNT [

|(*) |expr |(DISTINCT |

|attr |expr

|) |

|] | | |

Note

If DISTINCT or UNIQUE is included, duplicate values are eliminated. Note that DISTINCT may only be used once to qualify a list of selected items. If DISTINCT is applied outside the COUNT function, the COUNT does not exclude duplicate records.

Example

1. Simple Example

   The total number of loans outstanding is counted by the command:

      SELECT COUNT FROM loans;
   
   

   which produces:

      COUNT (*)
   
      12
   
   

   or

      SELECT COUNT (*) FROM loans;
   
   

   which produces the same result. Note that the function is used as the column heading.

2. Count the Unique Number of Attribute Values

   The number of employees with loans is counted with the command:

      SELECT COUNT (DISTINCT name) FROM loans;
   
   

   producing:

      COUNT (DISTINCT name)
   
      6
   
   

3. Count the Number of Attribute Values with Condition

   To find out how many records in the loans table are outstanding to Mosca, use:

      SELECT COUNT FROM loans WHERE name = 'Mosca';
   
   

   producing:

      COUNT (*)
   
      2
   
   

   To find out how many records in the personnel table have a phone number beginning with 961, use:

      SELECT COUNT FROM personnel
         WHERE phone match "961*";
   
   

   producing:

      COUNT (*)
   
      1
   
   

4. Use Functions with Other Selected Items

   The COUNT function can be used when other items are selected, as in:

      SELECT name, amount, COUNT FROM loans;
   
   

   It produces:

      name          amount
      
      Mosca         $150.00
      Jones          $33.95
      Kilroy        $250.00
      Wladislaw      $55.00
      Jones          $25.00
      Mosca         $200.00
      Wladislaw      $25.00
      Peterson       $50.00
      Wladislaw      $75.00
      Jones         $300.00
      Scarlatti     $150.00
      Scarlatti     $125.00
      
      COUNT (*) = 12
   
   

The AVG, MAX, MIN and SUM functions are applied to the values of the specified attribute over the group. These functions produce a single value as follows:

SUM	Sum of the values in the group.
AVG	Average of the values in the group.
MAX	Largest value in the group.
MIN	Smallest value in the group.

Syntax

|AVG |MAX |MIN |SUM

| ([DISTINCT] attr) | | |

Note

1. The functions SUM and AVG apply only to numeric attributes. If UNIQUE or DISTINCT is specified, duplicate values are excluded.

2. Note that there is no point in specifying DISTINCT with MAX or MIN, since it does not have any logical effect.

Example

1. Example of MAX Function

   To find the largest loan in the loans table:

      SELECT MAX (amount) FROM loans;
   
   

   the result:

      MAX (amount)
   
      $300.00
   
   

2. Example of MIN Function

   To find the date of the oldest loan, use:

      SELECT MIN (date) FROM loans;
   
   

   the result:

      MIN (date)
   
      2 February 1990
   
   

3. Example of MAX Function with Condition

   To find the highest credit limit of all the personnel whose names start with the letter A to M, use:

      SELECT MAX (credit_limit) FROM personnel
         WHERE name < 'N';
   
   

   the result:

      MAX (credit_limit)
   
      $750.00
   
   

4. Example of AVG Function

   To find the average of all the amounts in the loans table, use:

      SELECT AVG (amount) FROM loans;
   
   

   the result:

      AVG (amount)
   
      119.91
   
   

5. Example of SUM Function with Condition

   To find the sum of all the loans outstanding to Mosca, use:

      SELECT SUM (amount) FROM loans
         WHERE name = 'Mosca';
   
   

   the result:

      SUM (amount)
   
      350.00
   
   

6. Functions Used In Combination

   The functions may be used in combination:

      SELECT COUNT, SUM (amount), AVG (amount), MAX (amount),
         MIN (amount) FROM loans;
   
   

   the result:

      COUNT(*)  SUM (amount)  AVG (amount)  MAX (amount)  MIN(amount)
   
      12        1438.95       119.91        $300.00       $25.00
   
   

Duplicates may be produced in a selected list of attribute values by default, or by using the keyword ALL. Although the result is consistent with the underlying database operations, it is not always desirable. The command:

   SELECT name FROM loans;

will produce a list of names with 12 entries, in which Mosca and Scarlatti appear twice, and Jones and Wladislaw three times each. To overcome this, the keyword DISTINCT is used in the SELECT command to produce a list of attribute values with no duplicates. UNIQUE may be used as a synonym for DISTINCT.

The keyword DISTINCT may precede the argument of the functions SUM, AVG, and COUNT.

For example, the following command produces a list of names with no duplicates:

    SELECT DISTINCT name FROM loans;

the result:

   name

   Mosca
   Jones
   Kilroy
   Wladislaw
   Peterson
   Scarlatti

To find out how many different employees have loans, use:

   SELECT COUNT (DISTINCT name) FROM loans;

the result:

   COUNT (DISTINCT name)

   6

The following is a list of Empress Build-In Functions and their brief descriptions:

Table 10-1: Build-In Functions

The detail descriptions and the examples of Empress Build-In Functions are described below:

abs ( ) returns the absolute value of a given numeric data.

Syntax

abs (number)

where:

number

is a numeric value or numeric attribute. It can also be a result of a numeric expression.

Notes

abs ( ) will not handle null attributes.

Example

The following yields a value of 750.

   abs (750-1500)

The following yields an absolute value of attribute amount.

   SELECT abs (amount) FROM loans;

ceiling ( )returns a value rounded up to a specified number of decimal digits.

Syntax

ceiling (decimal [, digits])

where:

decimal	is a numeric value, a numeric attribute or a result of a numeric expression.
digits	is the number of digits after the decimal place. It may take on both positive and negative values and the default is zero digits to the right of the decimal point.

Notes

ceiling ( ) will not handle null values.

Example

   ceiling (12.345)

Yields a value of 13.0.

   ceiling (12.345, 2)

Yields a value of 12.35.

   ceiling (12.345, -1)

Yields a value of 20.0.

datenext ( ) returns the next date after a specified date.

Syntax

datenext (date,

|day_name |day_number

|) |

where:

date	is a date string or a DATE or TIME data type attribute.
day_name	is the day of the week, i.e., Monday, Tuesday, etc.
day_number	is the number of the day of the week, i.e., 1 is Monday, 2 is Tuesday, etc.

Notes

1. Day numbering begins with Sunday as day 0.

2. datenext ( ) will not handle null values.

3. Keyword TODAY and NOW can be use as date value.

Example

If today is taken as Monday, February 2, 1998, then:

   datenext ("TODAY", "Monday")

Returns a value of February 9, 1998 in a generic format and 19980209000000 in a TIME format.

If today is taken as Monday, February 2, 1998, then:

   datenext ("TODAY", 1)

Returns a value of February 9, 1998.

If today is taken as Monday, February 2, 1998, then:

datenext ("TODAY", 2)

Returns a value of February 3, 1998.

floor ( ) returns a given value truncated to a specified number of decimal digits.

Syntax

floor (number [, digits])

where:

number	is a numeric value or any numeric data type attribute.
digit	is the number of digits after the decimal place. It may take on both positive and negative values and the default is zero digits to the right of the decimal point.

Notes

1. DOLLAR data is converted to FLOAT.

2. floor ( ) will not handle null values.

Example

   floor (12.345)

Yields a value of 12.0.

   floor (12.345, 2)

Yields a value of 12.34.

   floor (12.345, -1)

This yields a value of 10.0.

is_decimal ( ) returns true for a given character string which can be converted to an Empress decimal value without the loss of precision.

Syntax

is_decimal (string [, max_digits [, decimal_places]])

where:

string	is any character string, CHARACTER or TEXT data type attribute.
max_digits	is the maximum number of digits allowed for the converted value. It defaults to 15.
decimal_places	is the number of decimal places allowed for the converted value. It defaults to the number of decimal places in the source string to a maximum of 15.

Notes

1. is_decimal ( ) will not handle null values.

2. is_decimal ( ) function is used in the WHERE clause.

Example

   is_decimal ("12.345", 5, 3)

Returns a value of true.

   is_decimal ("12.3450", 5, 3)

Returns a value of true.

   is_decimal ("12.3456", 5, 3)

Returns a value of false.

   SELECT FROM loans WHERE is_decimal (name);

No record is selected.

is_integer ( ) returns true for a given character string which can be converted into an Empress integer without loss of precision.

Syntax

is_integer (string [, nbytes])

where:

string	is any character string, CHARACTER or TEXT data type attribute.
nbytes	is the number of bytes. The default is 4.

Notes

1. When nbytes is specified, the result is true only when the converted value will fit in an integer with the specified number of bytes. nbytes may take on the values between 1 and 4. If nbytes is greater than 4, it returns a value of false.

2. is_integer ( ) will not handle null values.

3. is_integer ( ) is used in the WHERE clause.

Example

Given table T1 having a four-character attribute ATTR1 and three records, for example, abcd, 123, 1234.

   SELECT FROM T1 WHERE is_integer (ATTR1);

Returns 123 and 1234.

   SELECT FROM T1 WHERE is_integer (ATTR1, 1);

Returns 123.

is_white ( ) returns true when a given string contains only space, tab, new line, carriage return, vertical tab or form feed.

Syntax

is_white (string)

where:

string

is any character string, CHARACTER or TEXT data type attribute.

Notes

1. is_white ( ) will not handle null values.

2. is_white ( ) is used in the WHERE clause.

Example

Given table T1 having a text attribute ATTR1 and three records, for example, line one, <Space>, <Tab>, where the second record contains all spaces and the third record contains a tab, then:

   SELECT FROM T1 WHERE is_white (ATTR1);

Returns the second and third record.

   SELECT FROM T1 WHERE not (is_white (ATTR1));

Returns the first record: line one.

length ( ) returns the length of a given string.

Syntax

length (string)

where:

string

is any character string of any data type attribute.

Note

length ( ) will not handle null values.

Example

Given a table T1 having a ten-character attribute name and three records, for example, Jim, John and Fred, then:

   SELECT T1 WHERE length (name) > 3;

Returns John and Fred.

   length ("abc")

Returns 3.

lpad ( ) returns a string left-padded by a specified string with a given length.

Syntax

lpad (string, pad_string [, [occurrence])

where:

string	is any character string, CHARACTER or TEXT data type attribute.
pad_string	is any character string.
occurrence	is an integer number.

Notes

1. When occurrence is specified, it will pad pad_string as many times as occurrence indicates

2. When occurrence is not specified and if string is a character string or TEXT data attribute, then it will pad pad_string only once.

   If string is a CHARACTER attribute, then it will pad pad_string as many times as possible to fill the maximum length of the CHARACTER data type.

Example

The following example:

   lpad ("program", "MY")

Returns MYprogram.

The following example:

   lpad ("program", "1", 3)

Returns 111program.

Given table T1 with attribute (C, CHAR(10,1)) and values abc, 123456789.

   SELECT lpad (C, "MY") FROM T1;

will produce:

   MYMYMYabc
   123456789

lscan ( ) returns the position of the nth occurrence of a given string.

Syntax

lscan (string, search_string [, occurrence ])

where:

string	is any character string, CHARACTER or TEXT data type attribute.
search_string	is any character string.
occurrence	is an integer number.

Notes

1. The position of the first character in the source string is 1.

2. If occurrence is not specified, the first occurrence will be reported.

3. If the requested occurrence of the search string is not found in the source string, zero is returned.

4. The function name strindex ( ) can be used as an alias to lscan ( ).

5. lscan ( ) will not handle null values.

Example

The following example:

   lscan ("abcdef", "cd")

Returns a value of 3.

The following example:

   lscan ("abcdaef", "a", 2)

Returns a value of 5.

ltrim ( ) trims a specified character string from the left side of a given character string.

Syntax

ltrim (string [, trim_set])

where:

string	is any character string, CHARACTER of TEXT data type attribute.
trim_set	is any character string. The default value is white-space characters including space, tab, new line characters, vertical tab and form feed.

Notes

1. ltrim ( ) will not handle null values.

2. The order of characters listed in trim_set has no bearing on the operation of the function. A trim set of xy will yield the same results as a trim set of yx.

Example

The following example:

   ltrim ("abcdef", "ab")

Returns cdef.

The following example:

   ltrim ("   abc")

Returns abc.

nullval ( ) returns the default value of an attribute with a null value. If the attribute value is not null, then nullvall ( ) returns its value.

Syntax

nullval (attr, default_value)

where:

default_value

is any value and can also be any attribute that has the same generic data type as attr.

Example

Given a table T1 having a ten-character attribute name and three records, for example, null, John, Fred:

   SELECT nullval (name, "X") FROM T1;

Returns X, John, and Fred.

round ( ) returns a number rounded to the number of specified decimal digits.

Syntax

round (number [, digits])

where:

number	is any numeric value and can be a numeric attribute.
digits	is number of digits after the decimal place. It may take on both positive and negative values and the default is zero digits to the right of the decimal point.

Notes

1. DOLLAR data is converted to FLOAT.

2. round ( ) will not handle null values.

Example

The following example:

   round (12.345)

Yields a value of 12.0.

The following example:

   round (12.345, 2)

Yields a value of 12.35.

The following example:

   round (12.345, -1)

Yields a value of 10.0.

rpad ( ) returns a string right-padded by a specified string with a given length.

Syntax

rpad (string, pad_string [, [occurrence])

where:

string	is any character string, CHARACTER or TEXT data type attribute.
pad_string	is any character string.
occurrence	is an integer number.

Notes

1. When occurrence is specified, it will pad pad_string as many times as occurrence indicates

2. When occurrence is not specified and if string is a character string or TEXT data attribute, then it will pad pad_string only once.

   If string is a CHARACTER attribute, then it will pad pad_string as many times as possible to fill the maximum length of the CHARACTER data type.

Example

The following example:

   rpad ("program", "MY")

Returns programMY.

The following example:

   rpad ("program", "1", 3)

Returns program111.

Given table T1 with attribute (C, CHAR(10,1)) and values abc, 123456789.

   SELECT rpad (C, "MY") FROM T1;

will produce:

   abcMYMYMY
   123456789

rscan ( ) returns the starting position of the nth occurrence of the specified string.

Syntax

rscan (string, search_string [, occurrence])

where:

string	is any character string, CHARACTER or TEXT data type attribute.
search_string	is any character string.
occurrence	is an integer number

Notes

1. rscan ( ) searches from right to left. The position of the leftmost character in the source string is 1.

2. If occurrence is not specified, the first occurrence will be reported.

3. If the search_string is not found in the source string, zero is returned.

4. The function name rstrindex ( ) can be used as an alias to rscan ( ).

5. rscan ( ) will not handle null values.

Example

The following command returns 10:

   rscan ("John and John", "John")

The following command returns 1:

   rscan ("John and John", "John", 2)

The following command returns 0:

   rscan (("John and John", "Jim")

rstrindex ( ) returns the starting position of the nth occurrence of the specified string.

Syntax

rstrindex (string, search_string [, occurrence])

where:

string	is any character string, CHARACTER or TEXT data type attribute.
search_string	is any character string.
occurrence	is an integer number.

Notes

1. rstrindex ( ) searches from right to left. The position of the leftmost character in the source string is 1.

2. If occurrence is not specified, the first occurrence will be reported.

3. If the search_string is not found in the source string, zero is returned.

4. The function name rscan ( ) can be used as an alias to rstrindex ( ).

5. rstrindex ( ) will not handle null values.

Example

The following command returns 10:

   rstrindex ("John and John", "John")

The following command returns 1:

   rstrindex ("John and John", "John", 2)

The following command returns 0:

   rstrindex (("John and John", "Jim")

rtrim ( ) trims a specified character string from the right side of a given character string.

Syntax

rtrim (string [, trim_set])

where:

string	is any character string or INTEGER, CHARACTER or TEXT data type attribute.
trim_set	is any character string. It defaults to white-space characters including space, tab, new line characters, vertical tab and form feed.

Notes

1. All characters to the left of the first non- trim_set character in string are transferred to the resulting string.

2. rtrim ( ) will not handle null values.

3. The order of characters listed in trim_set has no bearing on the operation of the function. A trim set of xy will yield the same results as a trim set of yx.

Example

The following command returns abcdef:

   rtrim ("abcdef")

The following command returns abcd:

   rtrim ("abcdef", "fe")

The following command returns abcd:

   rtrim ("abcdef", "ef")

sign ( ) returns a value to indicate whether a given numeric value is positive, negative or zero.

Syntax

sign (number)

where:

number

can be any numeric value and can also be any attribute of numeric data type or a result of an expression.

Notes

1. 1 is returned when number is positive, 0 is returned when number is zero and -1 is returned when number is negative.

2. sign ( ) will not handle null values.

Example

The following command returns -1:

   sign (150-700)

The following command returns 0:

   sign (150-150)

The following command returns 1:

   sign (700-500)

strdel ( ) returns a given string with characters deleted according to the specified position and optional length argument.

Syntax

strdel (string, position [, length])

where:

string	is any character string, CHARACTER or TEXT data type attribute.
position	is the position within the string.
length	is an integer number which indicates the number of characters that will be deleted from the character specified by position. It can also be an INTEGER attribute. The default is 1.

Notes

1. strdel ( ) works on attributes of type CHAR, TEXT and BULK.

2. strdel ( ) will handle null values.

Example

The following command returns ac:

   strdel ("abc", 2)

strindex ( ) returns the position of the nth occurrence of a given string.

Syntax

strindex (string, search_string [, occurrence])

where:

string	is any character string, CHARACTER of TEXT data type attribute.
search_string	is any character string.
occurrence	is an integer number.

Notes

1. The position of the first character in the source string is 1.

2. If occurrence is not specified, the first occurrence will be reported.

3. If the requested occurrence of the search_string is not found in the source string, zero is returned.

4. The function name lscan ( ) can be used as an alias to strindex ( ).

5. strindex ( ) will not handle null values.

Example

The following command returns 3:

   strindex ("abcdef", "cd")

The following command returns 5:

   strindex ("abcdaef", "a", 2)

strins ( ) returns a string with another string inserted in front of a specific character position.

Syntax

strins (string1, position, string2)

where:

string1	is any character string.
string2	is any character string.
position	is a numeric value.

Notes

1. Position 1 is taken to be the first character of the string.

2. strins ( ) does not work with values of type BULK. To achieve similar results with values of type BULK, a combination of substr ( ) and concat can be used.

3. strins ( ) will not handle null values.

Example

The following command returns abcd:

   strins ("ad", 2, "bc")

substr ( ) returns a substring of of a given string, starting at specified character position and ending at the another character position.

Syntax

substr (string, position [, length ])

where:

string	is any character string or, CHARACTER or TEXT data type attribute.
position	is a numeric value.
length	is a numeric value. It defaults to the remaining length of the source string.

Notes

1. substr ( ) will work with values of type BULK, CHAR or TEXT.

2. substr ( ) will handle null values.

Example

The following command returns bcde:

   substr ("abcde", 2)

The following command returns bc:

   substr ("abcde", 2, 2)

The following command returns 4XX56:

   strins (subtr ("12345678910", 4, 3), 2, "XX")

tolower ( ) returns a given string with all alphabetic characters changed to lowercase.

Syntax

tolower (string)

where:

string

is any character string, CHARACTER or TEXT data type attribute.

Example

The following command returns foobar7:

   tolower ("FooBar7")

toupper ( ) returns given string with all alphabetic characters changed to uppercase.

Syntax

toupper (string)

where:

string

is any character string, CHARACTER or TEXT data type attribute.

Notes

toupper ( ) will not handle null values.

Example

The following command returns FOOBAR 7:

   toupper ("Foobar 7")

trunc ( ) returns a number truncated to a specified number of digits following the decimal point.

Syntax

trunc (number [, digits])

where:

number	is any numeric value or numeric attribute or an expression.
digits	is the number of digits after the decimal place. It can take on both positive and negative values and the default value is zero.

Notes

trunc ( ) will not handle null values.

Example

The following command returns a value of 12.00.

   trunc (12.345)

The following command returns a value of 12.34.

   trunc (12.345, 2)

The following command returns a value of 12500.00.

   trunc (12506.78, -2)

The following command returns a value of 30.00.

   trunc (10+20.25)

The math functions available vary from system to system. They will be a subset of the following. For which are available on your system and for full information on individual functions, see your system documentation.

The following is a list of Math Library Functions and their brief descriptions:

Table 10-2: Math Library Functions