Database Expressions

In CodeBase, a database expression is represented as a character string and is evaluated using the expression evaluation functions. database expressions are used to define the keys and filters of an index file. They can be useful for other purposes such as interactive queries.

Observe that the first part the qualifier specifies a data file alias (see Data4::.alias). This is usually just the name of the data file. Then there is the "->" followed by the field name.

Database Expression Constants

Database Expressions can consist of a Numeric, Character or Logical constant. However, database expressions that are constants are usually not very useful. Constants are usually used within a more complicated database expression. A Numeric constant is a number. For example, "5", "7.3", and "18" are all database expressions containing Numeric constants.

Character constants are letters with quote marks around them. " 'This is data' ", " 'John Smith' ", and " \"John Smith\" " are all examples of database expressions containing Character constants. If you wish to specify a character constant with a single quote or a double quote contained inside it, use the other type of quote to mark the Character constant. For example," \"Man's\" " and " ' \"Ok\" ' " are both legitimate Character constants. Unless otherwise specified, all database character constants in this manual are denoted by single quote characters.

Constants .TRUE. and .FALSE. are the only legitimate Logical constants. Constants .T. and .F. are legitimate abbreviations.

Database Expression Operators                      

Operators like '+' , ' * ', or '<' are used to manipulate constants and fields. For example, "3+8" is an example of a database expression in which the Add operator acts on two Numeric constants to return the Numeric value "11". The values an operator acts on must have a type appropriate for the operator. For example, the divide '/' operator acts on two Numeric values.

Precedence Operators have a precedence that specifies operator evaluation order. The precedence of each operator is specified in the following tables that describe the various operators. The higher the precedence, the earlier the operation will be performed. For example, 'divide' has a precedence of 6 and 'plus' has a precedence of 5 which means 'divide' is evaluated before 'plus'. Consequently, "1+4/2" is "3". Evaluation order can be made explicit by using brackets. For example, "1+2 * 3" returns "7" and "(1+2) * 3" returns "9".

Numeric Operators The Numeric operators all operate on Numeric values.

Character Operators There are two Character operators, named "Concatenate I" and "Concatenate II", which combine two Character values into one. They are distinguished from the Add and Subtract operators by the types of the values they operate on.

Examples: " 'John ' + 'Smith' " becomes " 'John Smith' "

" 'ABC' + 'DEF' " becomes " 'ABCDEF' "

Concatenate II is slightly different in that any spaces at the end of the first Character value are moved to the end of the result.

" 'John'-'Smith ' " becomes " 'JohnSmith ' "

" 'ABC' - 'DEF' " becomes " 'ABCDEF' "

" 'A ' - 'D ' " becomes " 'AD ' "

Relational Operators Relational Operators are operators that return a Logical result (which is either true or false). All operators, except Contain, operate on Numeric, Character or Date values. Contain operates on two character values and returns true if the first is contained in the second.

Examples: " 'CD' $ 'ABCD' " returns ".T."

" 8<7 " returns ".F."

Logical Operators Logical Operators return a Logical Result and operate on two Logical values.

Examples " .NOT. .T. " returns ".F."

" .T. .AND. .F." returns ".F."

Database Expression Functions

A function can be used as a database expression or as part of an database expression. Like operators, constants, and fields, database functions return a value. Functions always have a function name and are followed by a left and right parentheses. Some functions take parameters within the parentheses.

ALLtRIM(CHAR_VALUE)

This function trims all of the blanks from both the beginning and the end of the expression.

CHR( Integer_Value )

This function returns the character whose numeric ASCII code is identical to the given integer. The integer must be between 0 and 255.

Example: CHR(65) returns A.

CTOD( Char_Value )

The function converts a Character value into a Date value.

Example: . " CTOD( "11/30/88" ) "  

DATE()

This functions returns the current system date.   

Example:  Date_Field >= Date() .and. Date_Field < Date() + 7

DATETIME( Year, Month, Day [, Hour [, Minute [, Second ] ] ] )

The given date and time is returned as a DateTime.
Example: "STOPTIME = DATETIME( 2003,03,21,13,30,00 )"
This expression would evaluate to .TRUE. if the DateTime field STOPTIME is set to March 21, 2003 at 1:30 pm.

DAY( Date_Value )

Returns the day of the Date parameter as a Numeric value from "1" to "31".
Example:  "DAY(DATE())"  
Returns "30" if it is the thirtieth of the month.

DELETED()

DESCEND() accepts any type of parameter, except complex numeric expressions. DESCEND() converts all types into a character type in
descending order. For example, the following expression would produce a reverse order sort on the field ORD_DATE followed by normal sub-sort on
COMPANY.
Example: DESCEND(ORD_DATE) + COMPANY
See also ASCEND().

Returns .TRUE. if the current record is marked for deletion.

DTOC( Date_Value )

DTOC( Date_Value, 1 )

The function converts a Date value into a Character value. The format of the resulting Character value is specified by the Code4::dateFormat member variable which is by default "MM/DD/YY".

Example:. " DTOC( DATE() ) "
Returns the Character value "05/30/87" if the date is May 30, 1987.

If the optional second argument is used, the result will be identical to the database expression function DTOS. For example, DTOC( DATE(), 1 ) will return "19940731" if the date is July 31, 1994.

DTOS( Date_Value )

The function converts a Date value into a Character value. The format of the resulting Character value is "CCYYMMDD".  

Example: " DTOS( DATE() ) "
Returns the Character value "19870530" if the date is May 30, 1987.  

IIF( Log_Value, True_Result, False_Result )

If Log_Value is .TRUE. then IIF returns the True_Result value. Otherwise, IIF returns the False_Result value. Both True_Result and False_Result must be the same length and type. Otherwise, an error results.

Example: "IIF( VALUE < 0, "Less than zero ", "Greater than zero" )"  Example: "IIF( NAME = "John", "The name is John", "Not John " )"

LEFT( Char_Value, Num_Chars )

This function returns a specified number of characters from a Character expression, beginning at the first character on the left. The parameter NUM_CHARS must be constant.

Example: "LEFT( 'SEQUITER', 3)" returns "SEQ". The same result could be achieved with "SUBSTR ('SEQUITER', 1, 3)".

LTRIM( Char_Value )  

This function trims any blanks from the beginning of the expression.  

MONTH( Date_Value )

Returns the month of the Date parameter as a Numeric.

Example: " MONTH( DT_FIELD ) "  
Returns 12 if the Date field's month is December.  

PAGENO()

When using the report module or CodeReporter, this function returns the current report page number.

RECCOUNT()

The record count function returns the total number of records in the database: Example: "RECCOUNT()"

RECNO()

The record number function returns the record number of the current record.

RIGHT( Char_Value, Num_Chars )

This function returns a specified number of characters from the end of a character expression. The parameter 'NUM_CHARS' must be constant.
Example: "RIGHT( 'SEQUITER', 3)" returns "TER".

STOD( Char_Value )

The function converts a Character value into a Date value: Example: " STOD( '19881130' ) " The character representation is in the format "CCYYMMDD".

STR( Number, Length, Decimals )

The function converts a Numeric value into a Character value. Length is the number of characters in the new string, including the decimal point. Decimals is the number of decimal places desired. The parameters LENGTH and DECIMALS must be constant. If the number is too big for the allotted space, *'s will be returned.
Example: " STR( 5.7, 4, 2) " returns " '5.70' "  
The number 5.7 is converted to a string of length 4. In addition, there will be 2 decimal places.

Example: " STR( 5.7, 3, 2) " returns " '***' "  
The number 5.7 cannot fit into a string of length 3 if it is to have 2 decimal places. Consequently, *'s are filled in.

SUBSTR( Char_Value, Start_Position, Num_Chars)

A substring of the Character value is returned. The substring will be NUM_CHARS long, and will start at the START_POSITION character of CHAR_VALUE. The parameters START_POSITION and NUM_CHARS must be constant.

Example: " SUBSTR( "ABCDE", 2, 3 )" returns " 'BCD' "  
Example: "SUBSTR( "Mr. Smith", 5, 1 )" returns " 'S' "

TIME()

The function returns the system time as a character representation. It uses the following format: HH:MM:SS.

Example: " TIME() " returns " 12:00:00 " if it is noon.  
Example: " TIME() " returns " 13:30:00 " if it is one thirty PM.

TRIM(cHAR_VALUE)

This function trims any blanks off the end of the expression.

UPPER( Char_Value )

A Character string is converted to uppercase and the result is returned.  

VAL( Char_Value )

The function converts a Character value to a Numeric value.

Example: VAL( '10' ) returns "10".
Example: VAL( '-8.7' ) returns "-8.7".

YEAR( Date_Value )

Returns the year of the date parameter as a Numeric: Example: "YEAR( STOD( '19920830' ) ) " returns " 1992"