DBSQLFILTER(database, table, filter, field_name, rec_num)

The DBSQLFILTER function returns the value of a database SQL query that applies a filter.

database is the name of a database and must include the full path if not in the same directory as the open document.

table is the name of the table within the database. table could also be the name of a relation.

filter is a text string containing an SQL filter statement and takes the general form of fieldname <operator> comparison. The following could all be used in the examples below:

filter

meaning

Price = 50

Price exactly equal to 50

Price >= 50

Price greater than or equal to 50

Price > 25 AND Price < 50

Price less than 50 but greater than 25

Classification = 'red'

Classification exactly equal to 'red'

Title LIKE 'Château*'

Title begins with ' Château' where * is a wildcard

field_name is optional and can be either the name of the field within a table or relation or the column number. If omitted, every row and column of the filtered table is returned.

rec_num is optional (and cannot be specified if field_name is omitted) and determines the record or row number of the returned value. If omitted, the entire column is returned (or table if field_name is also omitted).

The function arguments are case insensitive with the exception of SQL operators within the filter statement, which must be upper case.

For example, a database called wineshop, located in a folder c:\ability\samples, contains a Product table of wines. Products contains three fields as follows:

Title

Price

Classification

Château Haut du Puy

17.00

red

Volnay-Santenots

27.00

red

Meursault-Charmes

54.00

white

Le Chambertin

53.00

red

Chambolle-Musigny

28.50

red

DBSQLFILTER can be used to obtain the following results:

DBSQLFILTER("c:\ability\samples\wineshop.adb", "products", "Classification = 'white' ")

returns "Meursault-Charmes", as this is the contents of the first column from the first (and only in this case) record that matches the filter condition. Note that the filter parameter is surrounded by double quotes and the text that Classification is compared to - 'white' - is in single quotes. This is necessary to distinguish the end of each text string respectively.

DBSQLFILTER("c:\ability\samples\wineshop.adb", "products", "Classification = 'red' ", 2, 3)

returns 53.00, the second column from the third record that matches the filter.

ROWS(DBSQLFILTER("c:\ability\samples\wineshop.adb", "products", "Price > 50", "Price"))

returns 2, since there are two wines over 50.

Note that

DBSQLFILTER(database, table, filter, column, record)

is equivalent to DBSQL (see DBSQL) as follows:

DBSQL(database, "SELECT * FROM <table> WHERE <filter>", column, record).

For more details on how to build valid filter conditions, see the SQL primer.

See also:

Other remote functions