19. Intrinsic Function Reference

Intrinsic Functions for DBSeek and DBQuery
Intrinsic Functions for DBQuery Only
Logical Operators (DBQuery Only)
String Constants in DBQuery Expressions

FileFlex defines 12 functions that you can use in DBSeek and DBQuery logical expressions.

Intrinsic Functions for DBSeek and DBQuery

The following functions can be used in either DBSeek or DBQuery expressions:

CTOD, which takes a character string as an argument and converts it into an internal database date format (e.g., CTOD(19901225), which converts the date Dec. 25, 1990, to internal database date format)
DEL, which takes no argument and returns "*" if the current record is marked for deletion, a blank otherwise
DELETED, which returns .TRUE. if the current record is marked for deletion, .FALSE. otherwise
RECNO, which takes no argument and returns the current record number
STR, which takes a numeric value, a number indicating length and a number indicating number of decimal places as arguments and converts the numeric value into a character string (e.g., STR(5.7, 4,2) will return "5.70")
SUBSTR, which extracts a substring of the string supplied as its first argument, beginning at the position specified in the second argument and continuing for the number of characters specified by the third argument (e.g., SUBSTR('ABCDEF',2,3) returns "BCD")
UPPER, which takes a character value as an argument and converts it to uppercase

Intrinsic Functions for DBQuery Only

The following functions can be used in DBQuery expressions only:

DATE, which takes no argument and returns the system date from the computer's internal clock
DTOC, which takes a database date format value as an argument and converts it into the equivalent string (e.g., CTOD(DATE()), which converts today's date into a character string so that if today were Dec. 25, 1990, the result would be "19901225")
IFF, which takes another logical expression as its first argument, followed by an indication of what should happen if that logical comparison is true, and another indication of what should happen if it is false (see discussion below)
RECCOUNT, which takes no argument and returns the total number of records in the database (not to be confused with the FileFlex DBCount API function, which permits you to retrieve the record count and do something with it other than use it in a logical expression)
VAL, which takes a character string and returns its numeric value (e.g., VAL("233") returns 233).

The IFF function described above allows you to nest logical conditions with one DBQuery call. For example, you might design a credit collection and management system with the requirement that each customer file has not only a credit limit, but also a description of when you wish to take some collection action. The credit limit might be stored in a field called LIMIT and the action description in a field called COLLECT. This would enable you to establish different collection policies for each customer. You might use a line something like this:

  put DBQuery("COLLECT = IFF(LIMIT>10000,'Casual',' '")
      into dbResult

This would confine your database review to records where the field COLLECT had a value of "Casual" and the LIMIT field is greater than $10,000. In other words, it would allow you to examine all casual customers with high credit limits.

Logical Operators (DBQuery Only)

Within the DBQuery expression, you can join functions, constants, and field names with logical operators. There are two types of such operators: comparative and connective.

The comparative operators recognized by FileFlex are shown below. Except for the "$" operator, they are the "standard" comparative operators recognized by many major database systems and programming languages. The "$" operator tests for the presence in one string of another (e.g., "ABC" $ "ABCDEF" returns .TRUE.).

Comparative Operators

SYMBOL             INTERPRETATION
  =                equal to
  <>               not equal to
  <                less than
  <=               less than or equal to
  >                greater than
  >=               greater than or equal to
  $                is contained in

The connective operators recognized by FileFlex are the same as those understood by dBASE: .NOT., .OR., and .AND. Note that, like the .TRUE. and .FALSE. constants mentioned earlier, these operators must be surrounded by periods to avoid their being confused with strings of characters.

These connective operators can be combined with the logical operators above to create some fairly complex search criteria. For example, if you wanted to find all your customers in California or Arizona with low account balances and who had received your catalog, you might end up with an expression like this:

  DBQuery("(State='CA' .OR. State='AZ') .AND. 
           Balance<=1000 .AND. Cat=.T.)")

String Constants in DBQuery Expressions

FileFlex defines a special string constant operator used within the DBQuery expressions. The string operator is the single quote (') -- make sure you don't use "smart quotes". When you call DBQuery, you always need to pass it a string containing the search expression. This is string according to the rules of your host development environment. But when you're comparing the state to 'CA', as in the above example, you're passing a DBQuery string to the function. In this case, the string you're asking DBQuery to compare within the expression must be surrounded by single quotes.

  [Previous Chapter]    [Table of Contents]    [Next Chapter]